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?