Re: Need some advice on a difficult query

Thanks! The queries I wrote in my email were just an example, my actual
implementation specifies all column names required and also uses full text
search. I just didn't want to paste in that much cruft :)

I'll do some tests with your technique below and see which works better..

Mike

On Wed, Dec 16, 2009 at 7:01 AM, Sam Mason <sam(at)samason(dot)me(dot)uk> wrote:

> On Wed, Dec 16, 2009 at 12:47:36AM -0800, Mike Christensen wrote:
> > 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.
>
> If you expect the number of blacklisted recipes to be low, the
> following may be a good alternative as well:
>
> SELECT r.*
> FROM recipes r LEFT JOIN (
> SELECT i.recipeid FROM ingredients i, blacklist b
> WHERE i.ingredientid = b.ingredientid
> AND b.userid = 123
> GROUP BY i.recipeid) x ON r.recipeid = x.recipeid
> WHERE r.recipetitle ILIKE '%pasta%'
> AND x.recipeid IS NULL;
>
> Note that it's generally considered bad form to include "*" in the
> return of a query when it's code dealing with the response. Humans can
> deal with the columns coming back differently, but code has the habit of
> getting confused.
>
> Also, you may want to consider using full text search when searching the
> titles. That ILIKE requires a full table scan and will slow down as
> more recipes get added.
>
> --
> Sam http://samason.me.uk/
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>