1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

  2. Anuncie Aqui ! Entre em contato fdantas@4each.com.br

Postgres: Many to many joins creates double output

Discussão em 'Outras Linguagens' iniciado por Stack, Dezembro 31, 2020.

  1. Stack

    Stack Membro Participativo

    I've recently added a many to many JOIN to one of my queries to add a "tag" functionality. The many to many works great, however, it's now causing a previously working part of the query to output records twice.

    SELECT v.*
    FROM "Server" AS s
    JOIN "Vote" AS v ON (s.id = v."serverId")
    JOIN "_ServerToTag" st ON (s.id = st."A")
    OFFSET 0 LIMIT 25;
    id | createdAt | authorId | serverId
    -----+-------------------------+----------+----------
    190 | 2020-12-23 15:47:25.476 | 6667 | 3
    190 | 2020-12-23 15:47:25.476 | 6667 | 3
    194 | 2020-12-21 15:47:25.476 | 6667 | 3
    194 | 2020-12-21 15:47:25.476 | 6667 | 3



    In the example above:

    • Server is my main table which contains a bunch of entries. Think of it as Reddit Posts, they have a title, content and use the Vote table to count "upvotes".

    id | title
    ----+-------------------------------
    3 | test server 3

    • Votes is a really simple table, it contains a timestamp of the "upvote", who created it, and the Server.id it is assigned to.
    • _ServerToTag is a table that contains two columns A and B. It connects Server to another table which contains Tags.

    A | B
    ---+---
    3 | 1
    3 | 2


    The above is a much-simplified query, in reality, I am suming the outcome of the query to get a number total of Votes.

    The desired outcome would be that the results are not duplicated:

    id | createdAt | authorId | serverId
    -----+-------------------------+----------+----------
    190 | 2020-12-23 15:47:25.476 | 6667 | 3
    194 | 2020-12-21 15:47:25.476 | 6667 | 3


    I'm really unsure why this is even happening so I have absolutely no idea how to fix it.

    Any help would be greatly appreciated.

    Edit:

    DISTINCT works if I want to query the Vote table. But not in more complex queries. In my case it would look something more like this:

    SELECT s.id, s.title, sum(case WHEN v."createdAt" >= '2020-12-01' AND v."createdAt" < '2021-01-01'
    THEN 1 ELSE 0 END ) AS "voteCount",
    FROM "Server" AS s
    LEFT JOIN "Vote" AS v ON (s.id = "serverId")
    LEFT JOIN "_ServerToTag" st ON (s.id = st."A");
    id | title | voteCount
    ----+-------------------------------+-----------
    3 | test server 3 | 4


    In the above, I only need the voteCount column to be DISTINCT.

    SELECT s.id, s.title, sum(DISTINCT case WHEN v."createdAt" >= '2020-12-01' AND v."createdAt" < '2021-01-01'
    THEN 1 ELSE 0 END ) AS "voteCount",
    FROM "Server" AS s
    LEFT JOIN "Vote" AS v ON (s.id = "serverId")
    LEFT JOIN "_ServerToTag" st ON (s.id = st."A");
    id | title | voteCount
    ----+-------------------------------+-----------
    3 | test server 3 | 1


    The above kind of works, but it seems to only count one vote even if there are multiple.

    Continue reading...

Compartilhe esta Página