SQL Statement JOIN only returns elements with association

Question

I have Acts by Users who are joined to Groups by Memberships in a PostgreSQL db.

I have a query to generate rows for a leaderboard. However, it currently excludes Users where the Acts table does not include a row with the corresponding users_id. I want to include all group members, even those with 0 Acts.

The current query:

SELECT acts.users_id, username, avatar_url, COUNT(acts.id) 
FROM acts 
JOIN users ON acts.users_id = users.id 
JOIN memberships on memberships.users_id = users.id 
WHERE memberships.groups_id = ' + req.params.group_id + ' 
AND acts.created_at >= (CURRENT_DATE - 6) 
GROUP BY acts.users_id, username, avatar_url 
ORDER BY COUNT(acts.id) DESC

I have tried changing JOIN before users to RIGHT JOIN and LEFT JOIN, but I get the same result. At one point, I think RIGHT JOIN was working, but somehow, I have gone awry.

Where does `avatar_url` come from? What exactly are you trying to count? — Erwin Brandstetter, Oct 09 '19 at 00:53

Erwin Brandstetter · Accepted Answer · 2019-10-09T02:04:16.783

I want to include all group members, even those with 0 Acts.

Tripping wires:

If you want include members with 0 Acts, you cannot return acts.users_id. Use memberships.users_id instead.
The condition a.created_at >= (CURRENT_DATE - 6) in the WHERE clause voids all attempts with LEFT JOIN. Move that condition into the JOIN clause. See:
- Postgres Left Join with where condition

SELECT m.users_id  -- !!!
     , u.username, avatar_url
     , COUNT(a.users_id) AS ct_acts
FROM   memberships m
JOIN   users       u ON m.users_id = u.id 
LEFT   JOIN acts   a ON a.users_id = u.id 
                    AND a.created_at >= (CURRENT_DATE - 6)  -- !!!
WHERE  m.groups_id = ' + req.params.group_id + '
GROUP  BY 1, 2, 3
ORDER  BY COUNT(*) DESC;

Assuming referential integrity between memberships and users (FK constraint), so the join to users can remain as [INNER] JOIN.

Also assuming "all group members" is suppose to mean all WHERE m.groups_id = ' + req.params.group_id + ', or we need to do more.

But what exactly are you counting there? Currently, this looks like a multiplication of acts with group memberships. May be a misunderstanding. See:

Two SQL LEFT JOINS produce incorrect result

Depending on exact table definitions and what you want to count, exactly, there might be a faster query ...

thanks Erwin. Everything you said was correct. removing the CURRENT_DATE constraint caused the leaderboard to render, but obviously, the date constraint is important. I had to adjust the query above to substitute m.users_id instead of a.users_id in the GROUP BY clause, but then it worked perfectly — user2799827, Oct 09 '19 at 01:39
Erwin, I am curious now about the possibility of a faster query. To clarify, the leaderboard ranks the users in a group based on the number of Acts they have done in the past 7 days. Is there a more efficient query? Thanks again! — user2799827, Oct 09 '19 at 01:41
@user2799827: Like I said: *depending on exact table definitions*. Please start a *new question* disclosing the relevant information: Postgres version, `CREATE TABLE` statements with relevant columns showing data types and constraints. My educated guess: there is a faster way. You can always link to this question / answer for context. — Erwin Brandstetter, Oct 09 '19 at 01:56

score 1 · Answer 2 · answered Oct 09 '19 at 01:04

I have had issues like this before. What I would do is remove all the where statements and the joins. Start by left joining the users to acts only and see if the query retains the inactive users that you wanted. Likewise try a left join between users and memberships. Once you have a query of two tables with users that do not exist in the ACT table. Join the third table with the output of the first two. And then finally apply your where statement and group by count.

score 0 · Answer 3 · answered Oct 08 '19 at 23:06

0

If you want to return users who don't have acts, you need to start from an other table than acts. You can for exemple try to start from the users table instead of the acts :

SELECT acts.users_id, username, avatar_url, COUNT(acts.id) 
FROM users
LEFT JOIN acts ON acts.users_id = users.id 
LEFT JOIN memberships on memberships.users_id = users.id 
WHERE memberships.groups_id = ' + req.params.group_id + ' 
AND acts.created_at >= (CURRENT_DATE - 6) 
GROUP BY acts.users_id, username, avatar_url 
ORDER BY COUNT(acts.id) DESC

This should return you all the users, even if they don't have an acts or a memberships.

answered Oct 08 '19 at 23:06

Askam Eyra

21
7

I have copied this exactly and I am getting the same result. Maybe it is something in Express or Vue? – user2799827 Oct 08 '19 at 23:44
I have changed from inner to left and back and the resulting array is the same every time, which makes me think something is wrong. I am restarting the api server every time. – user2799827 Oct 08 '19 at 23:56

SQL Statement JOIN only returns elements with association

3 Answers3