Selecting rows ordered by foreign key count and filtering by date 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 &quot;public.Vote&quot;
  Column   |              Type              | Collation | Nullable |              Default               
-----------+--------------------------------+-----------+----------+------------------------------------
 id        | integer                        |           | not null | nextval('&quot;Vote_id_seq&quot;'::regclass)
 createdAt | timestamp(3) without time zone |           | not null | CURRENT_TIMESTAMP
 serverId  | integer                        |           | not null | 
Indexes:
    &quot;Vote_pkey&quot; PRIMARY KEY, btree (&quot;createdAt&quot;)
    &quot;Vote.id_unique&quot; UNIQUE, btree (id)
Foreign-key constraints:
    &quot;Vote_authorId_fkey&quot; FOREIGN KEY (&quot;authorId&quot;) REFERENCES &quot;User&quot;(id) ON UPDATE CASCADE ON DELETE CASCADE
    &quot;Vote_serverId_fkey&quot; FOREIGN KEY (&quot;serverId&quot;) REFERENCES &quot;Server&quot;(id) ON UPDATE CASCADE ON DELETE CASCADE

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

Here's how my current query looks:
SELECT s.id, s.title, s.content, count(&quot;serverId&quot; WHERE &quot;createdAt&quot; &gt;= '2020-01-01 00:00:00') AS &quot;voteCount&quot; FROM &quot;Server&quot; AS s LEFT JOIN &quot;Vote&quot; AS v ON (s.id = &quot;serverId&quot;) GROUP BY s.id ORDER BY &quot;voteCount&quot; DESC;

At the moment the above query does this:

Selects all entries in &quot;Server&quot;
Counts how many entries in &quot;Vote&quot; each &quot;Server&quot; has
Orders the output data by &quot;voteCount&quot;

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(&quot;serverId&quot;) AS &quot;voteCount&quot; FROM &quot;Server&quot; AS s LEFT JOIN &quot;Vote&quot; AS v ON (s.id = &quot;serverId&quot;) WHERE v.&quot;createdAt&quot; &gt;= '2020-01-01 00:00:00' GROUP BY s.id ORDER BY &quot;voteCount&quot; DESC;

While the above technically does work, it's also not outputting rows in &quot;Server&quot; that have no rows in &quot;Vote&quot;. Is there any way to get those rows as well?
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.&quot;createdAt&quot; &gt;= '2020-01-01 00:00:00' so this removes all the records in your left-side table &quot;Server&quot; where there are no rows it could join to in the &quot;Vote&quot; 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(&quot;serverId&quot;) AS &quot;voteCount&quot; 
FROM &quot;Server&quot; AS s 
LEFT JOIN &quot;Vote&quot; AS v ON (s.id = &quot;serverId&quot;) 
WHERE v.&quot;createdAt&quot; &gt;= '2020-01-01 00:00:00' 
    OR v.&quot;createdAt&quot; IS NULL
GROUP BY s.id ORDER BY &quot;voteCount&quot; DESC;