Answer the question
In order to leave comments, you need to log in
Postgres: how to build such a query?
Postgres 9.5
4 tables:
Articles
Topics
Relations (columns: fk article_id, fk topic_id[, id etc...])
Favorite (columns: fk 'article_id', fk 'article_name', fk owner etc...)
Each article (Article ) can be linked to multiple topics (Topic) via the Relations table.
The user adds, say, 1000 articles to Favorites. How can I get only those articles from the table that have been added by this user to Favorites (Favorite) and are related to one specific topic (Topic)?
I'm sorry for the title of the question - I have no idea what to call it. If you know - write a comment - I will rename.
Answer the question
In order to leave comments, you need to log in
Let's say we know owner.id, and we know topic.id,
then the query will look like this (there is not enough knowledge of sql to organize this more harmoniously, but I would do something like this):
SELECT * FROM ARTICLES
WHERE ARTICLES.ID = (
SELECT ID FROM FAVORITE
WHERE
FAVORITE.OWNER.ID = "YOUR_OWNER_ID"
AND EXIST (
SELECT * FROM RELATIONS
WHERE FAVORITE.ID = RELATIONS.ID
AND RELATIONS.TOPIC_ID = "YOUR_TOPIC_ID
)
)
SELECT a.*
FROM articles a
INNER JOIN favourite f ON f.article_id = a.id
INNER JOIN relations r ON r.article_id = a.id
WHERE f.owner = :owner_id
AND r.topic_id = :topic_id
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question