The worst query I've written so far
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.