2

I've got myself in a little pickle with an SQL query that is a bit out of my experience.

Here's the two tables I'm using:

                                          Table "public.Vote"
  Column   |              Type              | Collation | Nullable |              Default               
-----------+--------------------------------+-----------+----------+------------------------------------
 id        | integer                        |           | not null | nextval('"Vote_id_seq"'::regclass)
 createdAt | timestamp(3) without time zone |           | not null | CURRENT_TIMESTAMP
 serverId  | integer                        |           | not null | 
Indexes:
    "Vote_pkey" PRIMARY KEY, btree ("createdAt")
    "Vote.id_unique" UNIQUE, btree (id)
Foreign-key constraints:
    "Vote_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"(id) ON UPDATE CASCADE ON DELETE CASCADE
    "Vote_serverId_fkey" FOREIGN KEY ("serverId") REFERENCES "Server"(id) ON UPDATE CASCADE ON DELETE CASCADE
                                          Table "public.Server"
  Column   |              Type              | Collation | Nullable |               Default                
-----------+--------------------------------+-----------+----------+--------------------------------------
 id        | integer                        |           | not null | nextval('"Server_id_seq"'::regclass)
 title     | text                           |           | not null | 
 content   | text                           |           |          | 
 createdAt | timestamp(3) without time zone |           | not null | CURRENT_TIMESTAMP
Indexes:
    "Server_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "Server_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"(id) ON UPDATE CASCADE ON DELETE CASCADE
    "Server_versionId_fkey" FOREIGN KEY ("versionId") REFERENCES "Version"(id) ON UPDATE CASCADE ON DELETE CASCADE
Referenced by:
    TABLE ""Vote"" CONSTRAINT "Vote_serverId_fkey" FOREIGN KEY ("serverId") REFERENCES "Server"(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE ""_ServerToTag"" CONSTRAINT "_ServerToTag_A_fkey" FOREIGN KEY ("A") REFERENCES "Server"(id) ON UPDATE CASCADE ON DELETE CASCADE

Here's how my current query looks:

SELECT s.id, s.title, s.content, count("serverId" WHERE "createdAt" >= '2020-01-01 00:00:00') AS "voteCount" FROM "Server" AS s LEFT JOIN "Vote" AS v ON (s.id = "serverId") GROUP BY s.id ORDER BY "voteCount" DESC;

At the moment the above query does this:

  • Selects all entries in "Server"
  • Counts how many entries in "Vote" each "Server" has
  • Orders the output data by "voteCount"

It's working pretty well, but now I would like to add an extra layer of complexity to it and only count votes made after a certain date.

So I came up with this query:

SELECT s.id, s.title, s.content, count("serverId") AS "voteCount" FROM "Server" AS s LEFT JOIN "Vote" AS v ON (s.id = "serverId") WHERE v."createdAt" >= '2020-01-01 00:00:00' GROUP BY s.id ORDER BY "voteCount" DESC;

While the above technically does work, it's also not outputting rows in "Server" that have no rows in "Vote". Is there any way to get those rows as well?

2

So this is happening because even though you used a LEFT JOIN, you're filtering on a field in the right-side table of that LEFT JOIN in the part of your query that does WHERE v."createdAt" >= '2020-01-01 00:00:00' so this removes all the records in your left-side table "Server" where there are no rows it could join to in the "Vote" table (i.e. where createdAt is null).

You can add this additional condition in your WHERE predicate to fix that:

SELECT s.id, s.title, s.content, count("serverId") AS "voteCount" 
FROM "Server" AS s 
LEFT JOIN "Vote" AS v ON (s.id = "serverId") 
WHERE v."createdAt" >= '2020-01-01 00:00:00' 
    OR v."createdAt" IS NULL
GROUP BY s.id ORDER BY "voteCount" DESC;
4
  • 1
    Oh that's a really good solution actually, hadn't thought of it at all! I also figured out I could change count to sum(case WHEN v."createdAt" > '2020-12-01T00:00:00' THEN 1 ELSE 0 END ), but yours is definitely better. Thank you! – Eight Nov 23 '20 at 23:50
  • Yea funny enough I just suggested the SUM(CASE...) syntax as answer yesterday on another similar question as a logical replacement for COUNT. – J.D. Nov 24 '20 at 0:13
  • @LaurenzAlbe That would be slightly different logically because then you'll always end up with all records from "Server" (even one's where they have a "Vote" prior to the predicate for the createdAt field, e.g. 2019-09-01) but it seems like he only wants one's that have a "Vote" created after 2020-01-01 or don't have a "Vote" at all. – J.D. Nov 24 '20 at 3:52
  • @J.D. You are right, comment withdrawn. – Laurenz Albe Nov 24 '20 at 6:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.