Re: Querying many to many relations

At 02:28 PM 2/10/07, A Gilmore wrote:
>I need to run a query that retrieves all rows in a table and passes off
>the information to another app, it involves a many-to-many relation, that
>I'm handling with a subselect.  Obviously this is costly, I was wondering
>if anyone has better way of approaching this problem that would allow me
>to avoid the subselect.
>
>SELECT id,name,ahref,array_to_string(array(SELECT links_categories.cid
>FROM links_categories WHERE links_categories.lid = id), ':') AS categories
>FROM links;


This does not appear to be a many-to-many relation.  For one link there are
many categories; that makes a one-to-many relation.


>The problem is my app needs to create an XML entry for each link row that
>contains all applicable category IDs.  Can I create a similar query while
>avoiding the subselect?


I don't see the subselect as a problem.  Try using the array_accum function
from this page:
http://www.postgresql.org/docs/8.2/interactive/xaggr.html

Then you can rewrite your query as

select id,name,ahref,cids from links left join (select lid,array_accum(cid)
as cids from links_categories group by lid) as categories on id=lid;

If you tinker with the aggregate function, perhaps you can include your XML
in there?