Geek Answers Handbook

SQL query for a combination of products

For an online store, I am looking for a request to select the best (cheapest) combination of products and packages. Very simple, the DB looks like this:

Items

Name  Price
-----------
Item1   10
Item2   20
Item3   30
Item4   40

Itempackage

Name          Price
-------------------
ItemPackage1    25
ItemPackage2    60
ItemPackage3    50

ItemPackageItems

ID                   ItemPackage     Item
------------------------------------------
ItemPackageItems1    ItemPackage1    Item1
ItemPackageItems2    ItemPackage1    Item2
ItemPackageItems3    ItemPackage2    Item3
ItemPackageItems4    ItemPackage2    Item4
ItemPackageItems5    ItemPackage3    Item1
ItemPackageItems6    ItemPackage3    Item2
ItemPackageItems7    ItemPackage3    Item2

How can I get prices for all combinations or the best combination when a customer orders different items.

Examples:

Item1 + Item2 + Item3   should return   ItemPackage1 + Item3
Item1 + Item3 + Item4   should return   Item1 + ItemPackage2
Item1 + Item3           should return   Item1 + Item3

What can SQL or Linq do to achieve this?
Thanks,

+4
source share
3 answers

I wrote a couple of classes to solve this optimization problem, but it is not finished yet (now I'm disconnected from my PC and go back - you can first look at it and see if it is useful) ...

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace TestCSharp.Optimizer
{
public interface IUnit
{
    int Id { get; set; }
    float UnitPrice { get; set; }
}
public interface IOrder
{
    int Quantity { get; set; }
    float GetTotalPrice();
}

public interface IOrder<T> : IOrder where T : IUnit
{
    T Unit { get; }
}



public abstract class AbstractUnit : IUnit
{
    public int Id { get; set; }
    public float UnitPrice { get; set; }
}

public class Item : AbstractUnit
{
    public string Name { get; set; }
}

public class Package : AbstractUnit
{
    private List<Order<Item>> _items = new List<Order<Item>>();
    public IEnumerable<Order<Item>> Items
    {
        get { return this._items; }
    }
}

public class Order<T> : IOrder<T> where T : IUnit
{
    T _unit;

    public Order(T unit)
    {
        _unit = unit;
    }

    public T Unit { get { return _unit; } }

    public int Quantity { get; set; }

    public float GetTotalPrice()
    {
        return _unit.UnitPrice * Quantity;
    }
}

public class Combination
{
    private List<IOrder> _orders = new List<IOrder>();

    private Combination()
    {
        // Private constructor
    }

    public List<IOrder> Orders { get { return _orders; } }

    public float GetTotalPrice()
    {
        if (_orders.Any())
        {
            return _orders.Select(m => m.GetTotalPrice()).Sum();
        }

        return 0;
    }

    public static Combination GetBestCombination(IEnumerable<Order<Item>> intendedItems, IEnumerable<Package> allPacksSetupFromDB)
    {
        var potentialCombinations = new List<Combination>();

        // First comb: treat each item as a standalone IOrder:
        var com = new Combination();
        foreach (var ele in intendedItems)
        {
            com.Orders.Add(ele);
        }
        potentialCombinations.Add(com);

        // check each possible pack (loaded from db) and find all possible combinations
        var possiblePacks = allPacksSetupFromDB.Where(m => m.Items.All(n => (intendedItems.Any(t => t.Unit.Id == n.Unit.Id && t.Quantity >= n.Quantity)))).ToArray();
        //ToDo: in the possible packages to use, find out all possible combinations
        //      This is the tricky part...

        // The one with lowest price is desired!
        return potentialCombinations.OrderBy(m => m.GetTotalPrice()).FirstOrDefault();
    }
}
}

EDIT:

, , , :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace TestCSharp.Optimizer
{
public interface IUnit
{
    int Id { get; set; }
    float UnitPrice { get; set; }
}
public interface IOrder
{
    int Quantity { get; set; }
    float GetTotalPrice();
}

public interface IOrder<T> : IOrder where T : IUnit
{
    T Unit { get; }
}



public abstract class AbstractUnit : IUnit
{
    public int Id { get; set; }
    public float UnitPrice { get; set; }
}

public class Item : AbstractUnit
{
    public string Name { get; set; }
}

public class Package : AbstractUnit
{
    private List<Order<Item>> _items = new List<Order<Item>>();

    // Items here are required by the package - number of items is specified by the quantity.
    public IEnumerable<Order<Item>> Items
    {
        get { return this._items; }
    }
}

public class Order<T> : IOrder<T> where T : IUnit
{
    T _unit;

    public Order(T unit)
    {
        _unit = unit;
    }

    public T Unit { get { return _unit; } }

    public int Quantity { get; set; }

    public float GetTotalPrice()
    {
        return _unit.UnitPrice * Quantity;
    }
}

public class Combination
{
    private List<IOrder> _orders = new List<IOrder>();

    private Combination()
    {
        // Private constructor
    }

    public List<IOrder> Orders { get { return _orders; } }

    /// <summary>
    /// Get the total number of items ordered (combine package as well)
    /// </summary>
    /// <returns></returns>
    public Dictionary<int, int> GetItemIdAndQuantity()
    {
        var output = new Dictionary<int, int>();

        foreach (var order in this._orders)
        {
            if (order is Order<Item>)
            {
                var orderedItem = (Order<Item>)order;
                if (!output.Keys.Contains(orderedItem.Unit.Id))
                {
                    output.Add(orderedItem.Unit.Id, 0);
                }
                output[orderedItem.Unit.Id] = output[orderedItem.Unit.Id] + ((Order<Item>)order).Quantity;
            }
            else
            {
                var orderedPackage = (Order<Package>)order;
                foreach (var item in orderedPackage.Unit.Items)
                {
                    var itemId = item.Unit.Id;
                    if (!output.Keys.Contains(itemId))
                    {
                        output.Add(itemId, 0);
                    }
                    output[itemId] = output[itemId] + item.Quantity * orderedPackage.Quantity;
                }
            }
        }

        return output;
    }

    public float GetTotalPrice()
    {
        if (_orders.Any())
        {
            return _orders.Select(m => m.GetTotalPrice()).Sum();
        }

        return 0;
    }

    public static Combination GetBestCombination(IEnumerable<Order<Item>> intendedItems, IEnumerable<Package> allPacksSetupFromDB)
    {
        var potentialCombinations = new List<Combination>();

        // check each possible pack (loaded from db) and find all possible combinations
        // Step 1: for each package, if it could be used potentially, collect it into a list
        var possibleRanges = new List<PackageUseRange>();
        foreach (var p in allPacksSetupFromDB)
        {
            // for each required item in a package, intended items have to fulfill at least one occurrence (with given quantity)
            if (p.Items.All(n => (intendedItems.Any(t => t.Unit.Id == n.Unit.Id && t.Quantity >= n.Quantity))))
            {
                var rng = new PackageUseRange(p);
                rng.Min = 0;

                // Find the possible max occurrence of the package
                var matchedOrders = intendedItems.Where(x => p.Items.Any(m => m.Unit.Id == x.Unit.Id));
                rng.Max = matchedOrders.Select(m => m.Quantity / p.Items.First(t => t.Unit.Id == m.Unit.Id).Quantity).Min();
                possibleRanges.Add(rng);
            }
        }
        // By now we should have something like: 
        // package 1: min 0, max 2
        // package 2: min 0, max 1
        // package 3: min 0, max 3
        // ...

        // Step 2: find all possible combinations:            
        if (possibleRanges.Any())
        {
            // define a collection to collect combinations 
            // also define a method to clear unwanted combinations. 
            var combinations = new List<Combination>();
            Action invalidOrderRemover = delegate()
            {
                for (int j = combinations.Count - 1; j >= 0; j++)
                {
                    var theCom = combinations[j];
                    var orderedQuantities = theCom.GetItemIdAndQuantity();
                    foreach (var itemId in orderedQuantities.Keys)
                    {
                        var intended = intendedItems.First(m => m.Unit.Id == itemId).Quantity;
                        if (orderedQuantities[itemId] > intended)
                        {
                            combinations.Remove(theCom);
                            break;
                        }
                    }
                }
            };

            // For first package, let create orders with different quantities
            var firstPack = possibleRanges[0];
            for (int i = firstPack.Min; i <= firstPack.Max; i++)
            {
                var order = new Order<Package>(firstPack.Package);
                order.Quantity = i;
                var com = new Combination();
                com.Orders.Add(order);
                combinations.Add(com);
            }                

            // From second onwards:  
            //      1. we expand the orders created earlier and collect current pack (with different quantity)
            //      2. we also take out those impossible combinations so far (total quantity exceeds wanted)
            for (int i = 1; i < possibleRanges.Count - 1; i++)
            {
                invalidOrderRemover.Invoke(); // to avoid the list/array unreasonably large

                // expand orders based on current pack range:
                var currPack = possibleRanges[i];
                var expanded = new List<Combination>();
                foreach (var oldCom in combinations)
                {
                    for (int j = currPack.Min; j <= currPack.Max; j++) 
                    {
                        // Clone from previous and pick up new ones from existing package. 
                        var newCom = new Combination();
                        newCom.Orders.AddRange(oldCom.Orders);
                        var newOrder = new Order<Package>(currPack.Package);
                        newOrder.Quantity = j;
                        newCom.Orders.Add(newOrder);
                    }
                }

                // Clear old and add back the expanded:
                combinations.Clear();
                combinations.AddRange(expanded);
            }

            // Clear unwanted again:
            invalidOrderRemover.Invoke();

            // Add back balanced items:
            foreach (var cb in combinations)
            {
                var fulfilled = cb.GetItemIdAndQuantity();
                foreach (var item in intendedItems)
                {
                    if (!fulfilled.Keys.Contains(item.Unit.Id))
                    {
                        // no such item in any package
                        // thus just add new Item based order
                        var newOrder = new Order<Item>(item.Unit);
                        newOrder.Quantity = item.Quantity;
                        cb.Orders.Add(newOrder);
                    }
                    else
                    {
                        // check balance:
                        if (fulfilled[item.Unit.Id] < item.Quantity)
                        {
                            var newOrder = new Order<Item>(item.Unit);
                            newOrder.Quantity = item.Quantity - fulfilled[item.Unit.Id];
                            cb.Orders.Add(newOrder);
                        }
                    }
                }
            }

            // Add to the final combination collection
            potentialCombinations.AddRange(combinations);
        }


        // Step 3: If there is no package used at all, treat each item as a standalone IOrder:
        if (!potentialCombinations.Any())
        {
            var com = new Combination();
            foreach (var ele in intendedItems)
            {
                com.Orders.Add(ele);
            }
            potentialCombinations.Add(com);
        }

        // The one with lowest price is desired!
        return potentialCombinations.OrderBy(m => m.GetTotalPrice()).FirstOrDefault();
    }

    private class PackageUseRange
    {
        public PackageUseRange(Package p)
        {
            this.Package = p;
            this.Min = 0;
            this.Max = 0;
        }

        public Package Package { get; private set; }
        public int Min { get; set; }
        public int Max { get; set; }
    }
}
}

: , , .

, min ( 0) .

, ( ).

, , - , . ...

0
public string[] getItems(string [] Items)
        {
            List<string> lstReturn = new List<string>();
            using(DBContext context = new DBContext())
            {

            lstReturn = (from c in context.ItemPackageItems
                            where Items.Contains(c.Item)
                            select c.ItemPackage).Distinct().ToList();


            var PackagesItems = (from c in context.ItemPackageItems
                                 where lstReturn.Contains(c.ItemPackage)
                                 select c.Item).Distinct();

            foreach (string strItem in Items)
            {
                if (!PackagesItems.Contains(strItem))
                    lstReturn.Add(strItem);
            }

           }

            return lstReturn.ToArray();
        }

,

0

sql cuse , 3, 3 , .

    declare @ck1 varchar (20),
@ck2 varchar (20),
@ck3 varchar (20),
@out varchar (100),
@flag bit,
@Itemck int
--if 1 then only @ck1 use, if 2 it used @ck1+@ck2 if 3 @ck1+@ck3 if 4 then @ck1+@ck2+@ck3
--usage manu
--1 @ck1
--2 @ck1+@ck2
--3 @ck1+@ck3
--4 @ck1+@ck2+@ck3
--5 @ck2+@ck3

set @ck1='Item1'
set @ck2='Item2'
set @ck3='Item2'
if @ck1='Item1'
    BEGIN
        if @ck2 = 'Item2' and @ck3='Item2'
        begin
            set @out='ItemPackge 3'
            set @flag=1
            set @Itemck=4
        end
            else

                begin
                        if @ck2 = 'Item2'
                            begin
                                set @out='ItemPackge 1'
                                set @flag=1
                                set @Itemck=2
                            end

                        else if @ck3='Item2'
                                    begin

                                        set @out='ItemPackge 1'
                                        set @flag=1
                                        set @Itemck=3
                                    end
                                        else 
                                            begin
                                                set @out='Item1'
                                                set @flag=1
                                                set @Itemck=1
                                            end
                end
    END
else

    if @ck1='Item2'
    BEGIN
        set @out='Item2'
        set @flag=1
    END
                if @ck1='Item3'
            BEGIN
                if @ck2 = 'Item4' or @ck3='Item4'
                    if @flag=0
                        set @out='ItemPackge 2'
                    else 
                        set @out=@out +' + ItemPackge 2'
                else 
                    if @flag=0
                            set @out='Item3'
                    else set @out=@out +' + Item 3'
                set @flag=1
            END
        else
            if @ck1='Item4'
            BEGIN
                if @flag=0 set @out='Item4'
                else set @out=@out +' + Item 4'
                set @flag=1
            END
if @Itemck=0 or @Itemck=1 or @Itemck=3
    BEGIN
        if @ck2 ='Item1' 
            BEGIN
                if @ck3='Item2'
                BEGIN
                    if @flag=0
                        set @out='ItemPackge 2'
                    else 
                        set @out=@out +' + ItemPackge 2'
                    set @Itemck=5
                END

                Else
                    BEGIN
                            if @flag=0
                                set @out='Item1'
                            else 
                                set @out=@out +' + Item1'
                    END
                end

        else if @ck2='Item2'
                    BEGIN
                            if @flag=0
                                set @out='Item2'
                            else 
                                set @out=@out +' + Item2'
                    END


        if @ck2 ='Item3' 
            BEGIN
                if @ck3='Item4'
                BEGIN
                    if @flag=0
                        set @out='ItemPackge 2'
                    else 
                        set @out=@out +' + ItemPackge 2'
                    set @Itemck=5
                END
                else
                BEGIN
                            if @flag=0
                                set @out='Item3'
                            else 
                                set @out=@out +' + Item3'
                    END
            END
                ELSE if @ck2='Item4'
                    BEGIN
                            if @flag=0
                                set @out='Item4'
                            else 
                                set @out=@out +' + Item4'
                    END


    END

            if @Itemck =1 or @Itemck =2
            BEGIN

                    if @flag=0
                        set @out=@ck3
                    else 
                        set @out=@out +' + ' + @ck3
            END
print @out
-1

More articles:


All Articles