clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 635678 fiddles created (13311 in the last week).

-- schema CREATE TABLE article ( id bigint PRIMARY KEY, title text NOT NULL, score int NOT NULL ); CREATE TABLE tag ( id bigint PRIMARY KEY, name text NOT NULL ); CREATE TABLE article_tag ( article_id bigint NOT NULL REFERENCES article (id), tag_id bigint NOT NULL REFERENCES tag (id), PRIMARY KEY (article_id, tag_id) ); CREATE INDEX ON article (score); -- sample data INSERT INTO article (id, title, score) VALUES (1, 'Sample Article A', 200), (2, 'Sample Article B', 350); INSERT INTO tag (id, name) VALUES (1, 'Technology'), (2, 'News'), (3, 'Startups'), (4, 'Programming'), (5, 'Investing'); INSERT INTO article_tag (article_id, tag_id) VALUES (1, 1), (1, 2), (1, 3), (1, 4), (2, 1), (2, 2), (2, 5);
2 rows affected
5 rows affected
7 rows affected
 hidden batch(es)


-- Faster LATERAL SELECT a.id, a.title, a.score, tags.names FROM article a LEFT JOIN LATERAL ( SELECT ARRAY ( SELECT t.name FROM article_tag a_t JOIN tag t ON t.id = a_t.tag_id WHERE a_t.article_id = a.id ) ) AS tags(names) ON true ORDER BY a.score DESC LIMIT 10;
id title score names
2 Sample Article B 350 {Technology,News,Investing}
1 Sample Article A 200 {Technology,News,Startups,Programming}
 hidden batch(es)


-- Even faster correlated subquery SELECT a.id, a.title, a.score , ARRAY ( SELECT t.name FROM article_tag a_t JOIN tag t ON t.id = a_t.tag_id WHERE a_t.article_id = a.id ) AS names FROM article a ORDER BY a.score DESC LIMIT 10;
id title score names
2 Sample Article B 350 {Technology,News,Investing}
1 Sample Article A 200 {Technology,News,Startups,Programming}
 hidden batch(es)