SQL query to match Lego design with pieces and quantity of pieces

mysql, sql

Solution

Try this query... This will give you the LegoDesigns in which the user has all of the blocks and quantity of blocks for:

SELECT
    c.id, c.name
FROM
    Collection a
INNER JOIN
    LegoBlockForDesign b ON 
        a.LegoBlock.id = b.LegoBlock.id AND
        a.quantityAvailable >= b.numberOfPiecesNeeded
INNER JOIN
    LegoDesign c ON b.LegoDesign.id = c.id
INNER JOIN
    (
        SELECT LegoDesign.id, COUNT(1) AS totalneeded
        FROM LegoBlockForDesign
        GROUP BY LegoDesign.id
    ) d ON c.id = d.LegoDesign.id
WHERE
    a.User.id = <userid here>
GROUP BY
    c.id, c.name, d.totalneeded
HAVING 
    COUNT(1) = d.totalneeded

Edit 2: This query will retrieve all designs the user can build given their current collection plus additional lego blocks that are selected by the user looking to purchase them:

SELECT 
    a.id, a.name
FROM
    (
        SELECT
            c.id, c.name, NULL AS notInCollection
        FROM
            Collection a
        INNER JOIN
            LegoBlockForDesign b ON a.LegoBlock.id = b.LegoBlock.id
        INNER JOIN
            LegoDesign c ON b.LegoDesign.id = c.id
        WHERE
            a.quantityAvailable >= b.numberOfPiecesNeeded AND
            a.User.id = <userid here>
        UNION ALL
        SELECT
            d.id, d.name, 1 AS notInCollection
        FROM
            LegoDesign d
        INNER JOIN
            LegoBlockForDesign e ON d.id = e.LegoDesign.id
        WHERE
            e.LegoBlock.id IN (<comma sepd list of legoblockids here>)
    ) a
INNER JOIN
    (
        SELECT LegoDesign.id, COUNT(1) AS totalneeded
        FROM LegoBlockForDesign
        GROUP BY LegoDesign.id
    ) b ON a.id = b.LegoDesign.id
GROUP BY 
    a.id, a.name, b.totalneeded
HAVING 
    COUNT(1) = b.totalneeded AND 
    COUNT(a.notInCollection) > 0

The `UNION ALL` basically tacks on rows representing designs that contain those particular selected blocks so that the `HAVING COUNT(*)` on the outside can take them into account. The `notInCollection` field in the FROM subselect is a flag indicating if the part is already in the user's collection or not... so `HAVING COUNT(f.notInCollection) > 0` excludes designs which the user already has all the parts for in his/her collection. This is much more efficient than doing a `NOT IN (<1st query as subquery>)` which you could also do.

The query assumes that the user is previously presented and can only select from a list of legoblocks which he/she doesn't already have, or else there will be duplication in the UNION which will throw off the results.

You can also put in a list of legoblockids so users can select more than one legoblock to see the increasing number of designs they can build as they select more blocks.

Problem

I am trying to find the fastest/less complicated way to get a result for the following problem. I have a DB of (for example) Lego Kits, where each Kit has a description and a list of Lego pieces needed and how many of them. An user can insert his collection of Lego pieces and then ask what Kit he can build with his pieces and what else he can build if he buys other pieces (maybe a first limit is that he can buy only 1 type of piece). What I have is roughly this: ``` LegoDesign - id - name LegoBlock - id - type - weight - description LegoBlockForDesign - LegoDesign.id - LegoBlock.id - numberOfPiecesNeeded Collection (- User.id) - LegoBlock.id - quantityAvailable ``` For example, the DB contains the LegoDesign for StarWar's Death Star. LegoBlock contains a long list of pieces like "2x2 black square" or "small wheel" etc. LegoBlockForDesign assign the LegoBlocks to the LegoDesign of he Death Star (for example 1000 pieces of "2x2 black square"). The Table collection instead contains what pieces the user has. Now the problem here is that I have to query for the Design with the pieces the user has which means 1st checking for the LegoBlock.id and then checking for the numberOfPiecesNeeded since I could have some 2x2 black square pieces but not enough to build the Death Star. This is the first query. The second one should check for Design that contains the block I have plus some blocks I don't have in my collection. This means that I should check for LegoBlock in my possession but with less than the right amount and for Design with blocks not in my possession. The latter needs a limit that could be set manually. I was thinking to let the user pick between a limit on the number of pieces to buy (ie max 30 pieces) or a limit on difficulty of pieces (ie. no "special block" to buy, like some special wheel or character present only in specific Design (the characters of Star Wars, for example)). I'm not entirely sure it can be done in SQL, especially because I have to check for quantities and not only for the existence of the block. EDIT: I've added LegoBlock.type and LegoBlock.weight. This way I can define type = common,rare,unique to define normal pieces or specific ones (like the Star Wars characters that can be defined rare. I don't want to buy those pieces since they can be used only on the StarWars' Design). Weight instead can be used to give a priority (I like blue, so I would prefer to see the Design where I have to buy blue pieces).

Original source