Hello,
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.
Here is an example of the problem:
-- SQL BEGIN
BEGIN;
CREATE TABLE links (
id INTEGER PRIMARY KEY,
name TEXT,
ahref TEXT
);
CREATE TABLE categories (
id INTEGER PRIMARY KEY,
name TEXT
);
CREATE TABLE links_categories (
lid integer REFERENCES links,
cid integer REFERENCES categories,
PRIMARY KEY (lid, cid)
);
INSERT INTO links (id,name,ahref) VALUES (1,'postgresql',
'http://www.postgresql.org');
INSERT INTO links (id,name,ahref) VALUES (2,'bbc', 'http://www.bcc.com');
INSERT INTO categories (id,name) VALUES (3,'informative');
INSERT INTO categories (id,name) VALUES (4,'news');
INSERT INTO categories (id,name) VALUES (5,'technology');
INSERT INTO categories (id,name) VALUES (6,'database');
INSERT INTO links_categories (lid,cid) VALUES (1,3);
INSERT INTO links_categories (lid,cid) VALUES (1,5);
INSERT INTO links_categories (lid,cid) VALUES (1,6);
INSERT INTO links_categories (lid,cid) VALUES (2,3);
INSERT INTO links_categories (lid,cid) VALUES (2,4);
SELECT id,name,ahref,
array_to_string(array(SELECT links_categories.cid FROM
links_categories WHERE links_categories.lid = id), ':') AS categories
FROM links;
-- SQL END
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 could do a JOIN/SORT approach and have the result processed by PHP
into a single XML entry per link row, but that would be more messy and
maybe not even any faster.
Thank you for your time,
- Adrien
