Search Postgresql Archives

Re: Need some advice on a difficult query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux