From: | Mike Christensen <mike(at)kitchenpc(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Need some advice on a difficult query |
Date: | 2009-12-16 08:47:36 |
Message-ID: | 7aa638e00912160047l2f2d924s971fab08598793ed@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Willy-Bas Loos | 2009-12-16 09:06:14 | Re: Column privileges and Hibernate |
Previous Message | Florian Weimer | 2009-12-16 07:54:39 | Re: Possible causes for database corruption and solutions |