MySQL get UNION, order by intersection?

I'm having a bit of a conundrum here. I'm baking a recipe application and let's say I have a query of "mayonnaise", "bacon" and "cheese".

I'd like to return all the recipes containing "mayonnaise" or "bacon" or "cheese" ordered by if they have "mayonnaise", "bacon" AND "cheese"开发者_JS百科 followed by those that just have "mayonnaise" AND "bacon", etc.

So far I have something a bit cloogey like:

SELECT * FROM recipes 
  WHERE LOWER(ingredients) LIKE '%mayonnaise%' 
     OR LOWER(ingredients) LIKE '%bacon%'

But that's just one half of what I want. How do I get across the finish line?

Thanks!

You could try prioritising the presence of ingredients and ordering by whether or not the ingredient is included:

SELECT * FROM recipes 
WHERE LOWER(ingredients) LIKE '%mayonnaise%' 
OR    LOWER(ingredients) LIKE '%bacon%'
OR    LOWER(ingredients) LIKE '%cheese%'
ORDER BY LOWER(ingredients) LIKE '%mayonnaise%' DESC,
         LOWER(ingredients) LIKE '%bacon%' DESC,
         LOWER(ingredients) LIKE '%cheese%' DESC

I don't know if recipes has a primary key, but I'll assume it does. Let's give 1 point for each word.

SELECT r.* FROM recipes
LEFT JOIN recipes m ON r.id = m.id AND LOWER(m.ingredients) LIKE '%mayonnaise%'
LEFT JOIN recipes b ON r.id = b.id AND LOWER(b.ingredients) LIKE '%bacon%'
LEFT JOIN recipes c ON r.id = c.id AND LOWER(c.ingredients) LIKE '%cheese%'
ORDER BY
(CASE WHEN m.id IS NULL THEN 0 ELSE 1 END)+
(CASE WHEN b.id IS NULL THEN 0 ELSE 1 END)+
(CASE WHEN c.id IS NULL THEN 0 ELSE 1 END) DESC

If possible (and as you seem to be building something new), maybe you should rethink your table layout. It appears you have the ingredients in a single column, probably comma separated or something. That really does not scale well and makes even simple queries like the one you have in mind complicated and slow (using all these wildcards in front of the actual search term effectively disables any index use).

Maybe a separate table for INGREDIENTS would be advisable, with a relationship of n:m cardinality to the RECIPES table.

In Pseudo-SQL:

CREATE TABLE RECIPES (id int not null, [...more columns...]) PRIMARY KEY id;
CREATE TABLE INGREDIENTS (id int not null, name varchar(30), [...more columns...]) PRIMARY KEY id;
CREATE TABLE RECIPE_INGREDIENTS (recp_id int not null, ingred_id not null);

That way you could join RECIPES and INGREDIENTS:

SELECT RECIPES.id AS recipe, COUNT(INGREDIENTS.id) AS matching_ingredients 
     FROM RECIPES r JOIN RECIPE_INGREDIENTS ri ON r.ID=ri.recp_id
                    JOIN INGREDIENTS i ON ri.ingred_id = i.id
     WHERE INGREDIENTS.name in ('mayonnaise', 'bacon', 'cheese')
GROUP BY COUNT(INGREDIENTS.id)

Leading to a table of recipe number and the number of matching ingredients. This would benefit from indexes, too.

Please note, that I don't have a MySQL server at hand right now, so the above is written off the top of my head. Maybe I got the syntax wrong a little. Moreover you would probably want to add Foreign Key information, but that's not strictly necessary to get it running.

See the CREATE TABLE syntax in the MySQL manual for that.