PostgreSQL Query – Selecting Rows Ordered by Foreign Key Count and Filtering by Date

postgresqlselect

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?

Best Answer

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;
Related Question