H
H
hbrmdc2016-07-16 20:44:02
PostgreSQL
hbrmdc, 2016-07-16 20:44:02

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

2 answer(s)
D
Denis Knyazev, 2016-07-16
@axaxa_man

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
          )
     )

S
SharuPoNemnogu, 2016-07-17
@SharuPoNemnogu

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 question

Ask a Question

731 491 924 answers to any question