So I have three tables,
news :
id|title |body |publised_at |deleted_at|created_at | --+-----------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+----------+-------------------+ 1|Tax are Increasing Again, But Why? |Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor i| | |2021-06-06 14:11:24| 2|AwanKinton are Making Brand New University Named DragonBall|Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor i| | |2021-06-06 14:11:24| 3|AwanKinton Released New Car that Can Fly |Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor i| | |2021-06-06 14:11:24| 4|Indonesia National Team Are Not Gonna Make it to World Cup |Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor i|2021-06-06 14:11:24| |2021-06-06 14:11:24| 10|10 Penyebab Istri Sebel Sama Suami |Sebel aslkdjas askldjlsajd alskdjlkas alskdjsalk asldkjaslk asdlkjasld asdljasl asldjsalk alsdjals lajsdlkaj | | |2021-06-06 14:34:08|
topics :
id|name |created_at | --+-------------+-------------------+ 1|politics |2021-06-06 18:43:13| 2|economy |2021-06-06 18:43:13| 3|auto |2021-06-06 18:43:13| 4|education |2021-06-06 18:43:13| 5|tech |2021-06-06 18:43:13| 6|culture |2021-06-06 18:43:13| 7|money |2021-06-06 18:43:13| 8|sports |2021-06-06 18:43:13| 9|uncategorized|2021-06-06 18:43:13|
and news_topics :
id|news_id|topics_id| --+-------+---------+ 1| 1| 7| 2| 1| 4| 3| 2| 3| 4| 2| 4| 5| 2| 5| 6| 3| 3| 7| 3| 4| 8| 4| 8|
And I want to return it like these :
id|title |body |published_at|deleted_at|created_at |t | --+-----------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+----------+-------------------+----+ 1|Tax are Increasing Again, But Why? |Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor i| | |2021-06-06 18:43:13| | 2|AwanKinton are Making Brand New University Named DragonBall|Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor i| | |2021-06-06 18:43:13|auto| 3|AwanKinton Released New Car that Can Fly |Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor i| | |2021-06-06 18:43:13|auto|
I succeed on returning that value using this query
select "news".*,
(
SELECT STRING_AGG("topics"."name", ',') as "topics"
FROM "topics"
INNER JOIN "news_topics" ON "topics"."id" = "news_topics"."topics_id"
WHERE "news_topics"."news_id" = "news"."id"
AND "topics"."name" IN ('auto')
) AS "topics"
from "news"
where published_at IS NULL AND deleted_at IS null
order by "created_at"
asc limit 10
But there is one flaw to that query, on the above result there are one row that contains null topics. When I add AND topics IS NOT NULL after it gave me error SQL Error [42703]: ERROR: column "topics" does not exist it is makes sense because the order of the query SQL executed.
I want rows that had null value on topics aren’t included on the query result. How do I get that?
Advertisement
Answer
Instead of using a correlated subquery to get the column topics, aggregate on the joined tables topics and news_topics and then join to news:
select n.*, t.topics
from news n
inner join (
SELECT nt.news_id, STRING_AGG(t.name, ',') AS topics
FROM topics t INNER JOIN news_topics nt
ON t.id = nt.topics_id
WHERE t.name IN ('auto')
GROUP BY nt.news_id
) t ON t.news_id = n.id
where n.published_at IS NULL AND n.deleted_at IS null
order by n.created_at asc limit 10
See the demo.