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 <s...@samason.me.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
