This might help you: http://www.postgresql.org/docs/8.4/static/queries-with.html On 24/08/2011, at 9:54 AM, Chris Hanks wrote: > I have two tables: > > CREATE TABLE items > ( > root_id integer NOT NULL, > id serial NOT NULL, > -- Other fields... > > CONSTRAINT items_pkey PRIMARY KEY (root_id, id) > ) > > CREATE TABLE votes > ( > root_id integer NOT NULL, > item_id integer NOT NULL, > user_id integer NOT NULL, > type smallint NOT NULL, > direction smallint, > > CONSTRAINT votes_pkey PRIMARY KEY (root_id, item_id, user_id, type), > CONSTRAINT votes_root_id_fkey FOREIGN KEY (root_id, item_id) > REFERENCES items (root_id, id) MATCH SIMPLE > ON UPDATE CASCADE ON DELETE CASCADE, > -- Other constraints... > ) > > I'm trying to, in a single query, pull out all items of a particular > root_id along with a few arrays of user_ids of the users who voted in > particular ways. The following query does what I need: > > SELECT *, > ARRAY(SELECT user_id from votes where root_id = i.root_id AND > item_id = i.id AND type = 0 AND direction = 1) as upvoters, > ARRAY(SELECT user_id from votes where root_id = i.root_id AND > item_id = i.id AND type = 0 AND direction = -1) as downvoters, > ARRAY(SELECT user_id from votes where root_id = i.root_id AND > item_id = i.id AND type = 1) as favoriters > FROM items i > WHERE root_id = 1 > ORDER BY id > > The problem is that I'm using three subqueries to get the information > I need when it seems like I should be able to do the same in one. I > thought that Postgres (I'm using 8.4) might be smart enough to > collapse them all into a single query for me, but looking at the > explain output in pgAdmin it looks like that's not happening - it's > running multiple primary key lookups on the votes table instead. I > feel like I could rework this query to be more efficient, but I'm not > sure how. > > Any pointers? > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
