10 Mar, ’21

The worst query I've written so far

by Cal

Look at this magnificent disaster. The goal: to select one collectable material that is sufficiently uncommon which is an ingredient in a crafting recipe which the character is high enough level to make, and which the character already has most - but not all - of the other ingredients to make.

SELECT items.* FROM (
    SELECT ing_t.recipe_id, (ingr_ct - inv_ct) AS needed FROM 
        (SELECT recipe_id,count(item_id) AS ingr_ct FROM ingredients GROUP BY recipe_id) ing_t
    JOIN (
        SELECT recipe_id, count(inv.item_id) AS inv_ct FROM ingredients LEFT JOIN
            (SELECT item_id FROM  inventory where quantity > 0 and uuid = ?) inv
        ON ingredients.item_id = inv.item_id GROUP BY recipe_id
    ) inv_t 
    ON ing_t.recipe_id = inv_t.recipe_id
    WHERE ingr_ct > inv_ct ORDER BY needed ASC
) filtered 
JOIN ingredients ON filtered.recipe_id = ingredients.recipe_id
JOIN recipes on filtered.recipe_id = recipes.id
JOIN items on ingredients.item_id = items.id
WHERE difficulty <= (SELECT craft FROM characters WHERE uuid = ?)
AND item_id NOT IN (SELECT item_id FROM inventory WHERE uuid = ? AND quantity > 0)
AND rarity > 0 LIMIT 1

I feel like there should be a more efficient way of doing this but honestly I'm just delighted I came up with something that works at all.