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.