Hi Mike,
here is an untested "weird nested query" for your
problem:
SELECT * FROM Recipes r where lower(RecipeTitle) like
lower('%pasta%')
and not exists
(select 1 from ingredients inner join blacklist using
(IngredientId) where RecipeId = r.RecipeId and blacklist.UserId = 123 limit 1);
Should be quite fast with the right indexes.
Timo
Von: pgsql-general-owner@xxxxxxxxxxxxxx
[mailto:pgsql-general-owner@xxxxxxxxxxxxxx] Im Auftrag von Mike
Christensen
Gesendet: Mittwoch, 16. Dezember 2009 09:48
An: pgsql-general@xxxxxxxxxxxxxx
Betreff: Need some advice on a difficult query
Hi all - I'd like some advice on
how to write a rather complicated (for me, anyway) query and if there's any
nifty Postgres features I can take advantage of in this situation.
Imagine a database, if you will, used to store recipes. I have a recipes
table:
RecipeId
RecipeTitle
RecipeRating
And an ingredients table:
RecipeId
IngredientId
Amount
When the user searches for a new pasta dish, the UI would generate a query
something like this:
SELECT * FROM Recipes where RecipeTitle ilike '%pasta%';
I only need the data from the recipes table since I display a summary of the
search results and don't load the full recipe until the user clicks on the
link. This works great. However, I'm now in the process of
implementing an ingredient blacklist. This means NEVER show me any
recipes which have one of my blacklisted ingredients, as if I ingest any I will
die a painful death. Or maybe my god will smite me or something.
This table would look like:
UserId
IngredientId
Ok, now the question. What's the best way to query for my pasta dish
above while excluding any recipe that contains one or more of my blacklisted
ingredients? I don't want to join in Ingredients because I'd then have a
row for each ingredient of each recipe (which would cause me massive headache
and redesign).. I thought about some weird nested query, but I'm
concerned it might be slow (searching for recipes is the central feature of my
site and must be fast, fast and fast). This basically boils down to some
weird Cartesian product thing.
I am, however, willing to redesign the mechanism I store blacklisted
ingredients with. I could store this as an array on the Users table, or
anything else that might make things easier. Any advice on this?
Mike
