MySQL - Count Query With Table Join Filtering

Question

I have is a users table, a posts table, a private table, and a user_stats table:

users                     
user_id |  user_name
--------------------
   1    |    tony
   2    |    steph
   3    |    lizzy
   4    |    adam

posts
post_id   user_id     sugg_by   private (0 is public, 1 is private to authorized users)
-----------------------------------------------------
   1         1           2         0
   2         2           2         1
   3         2           2         1            
   4         2           4         1
   5         2           2         1
   6         2           3         0

private
private_id   post_id   authorized_user_id
-----------------------------------------------
    1           2               4
    2           2               3
    3           4               4
    4           5               1
    5           5               3

user_stats                     
user_id  orig_posts_count(user_id=sugg_by in posts)  sugg_posts_count(user_id<>sugg_by in posts) 
-----------------------------------------------------------------------------------------------------
   1             0                                          1
   2             3                                          2
   3             0                                          0
   4             0                                          0

The problem I'm having is in this query. In this example '4' is the $logged_in_id adam, and '2' is the user_id of the posts we are counting for steph. If steph is logged_in, the COUNT will not run and we will give 'posterview', this works. Back to any other logged in user or anyone logged out and the issue begins. So for adam '4':

SELECT u.user_id, us.orig_posts_count, us.sugg_posts_count,
IF(us.user_id='4', 'posterview',
COUNT(case when ISNULL(pv.post_id) AND p.private='1' AND p.user_id='2' AND p.sugg_by='2' then null else 1 end)
) as display_orig_posts_count,
IF(us.user_id='4', 'posterview',
COUNT(case when ISNULL(pv.post_id) AND p.private='1' AND p.user_id='2' AND p.sugg_by<>'2' then null else 1 end)
) as display_sugg_posts_count
FROM users u 
JOIN user_stats us ON u.user_id=us.user_id 
JOIN posts p ON p.user_id=us.user_id
LEFT JOIN private pv on pv.post_id = p.post_id AND pv.authorized_user_id='4' 
WHERE u.user_id='2' LIMIT 1

The output should be:

user_id  orig_posts_count  sugg_posts_count  display_orig_posts_count  display_sugg_posts_count
   2            3                  2                    1                          2

It however outputs as:

user_id  orig_posts_count  sugg_posts_count  display_orig_posts_count  display_sugg_posts_count
   2            3                  2                    3                          5

I believe the reason for this is in the JOIN posts p ON p.user_id=us.user_id

If I turn it into JOIN posts p ON p.user_id=us.user_id AND p.sugg_by='2' (which matches the first COUNT for display_orig_posts_count), then the display_orig_posts_count is 1 which is correct, but display_sugg_posts_count is incorrect at 3. I get the correct display_orig_posts_count and incorrect display_sugg_posts_count. Outputs as:

user_id  orig_posts_count  sugg_posts_count  display_orig_posts_count  display_sugg_posts_count
   2            3                  2                    1                          3

If I turn it into JOIN posts p ON p.user_id=us.user_id AND p.sugg_by<>'2' (which matches the second COUNT for display_sugg_posts_count), then the display_orig_posts_count is 2 which is incorrect, but display_sugg_posts_count is correct at 2. Outputs as:

user_id  orig_posts_count  sugg_posts_count  display_orig_posts_count  display_sugg_posts_count
   2            3                  2                    2                          2

Basically, a logged in user if steph should return 'posterview', but any other user that uses COUNT should only see posts that are public (private 0), or (private 1) only if they are a part of it, and then count based on the condition in the COUNT clause to get the correct output as stated. I've been stuck on this for hours. Any idea how to get the query to work correctly? Note: Fiddles are included for each example output.

hrunting · Accepted Answer · 2013-02-09 23:02:10Z

Change your query to this:

  SELECT u.user_id, us.orig_posts_count, us.sugg_posts_count,
    IF(us.user_id='4', 'posterview',
      COUNT(CASE
        WHEN p.sugg_by='2' AND (p.private='0' OR pv.post_id IS NOT NULL)
        THEN 1
        ELSE NULL
      END)) AS display_orig_posts_count,
    IF(us.user_id='4', 'posterview',
      COUNT(CASE
        WHEN p.sugg_by<>'2' AND (p.private='0' OR pv.post_id IS NOT NULL)
        THEN 1
        ELSE NULL
      END)) AS display_sugg_posts_count
    FROM users AS u
         JOIN user_stats AS us ON u.user_id=us.user_id
         JOIN posts AS p ON p.user_id=us.user_id
         LEFT JOIN private AS pv ON pv.post_id=p.post_id AND pv.authorized_user_id='4'
   WHERE u.user_id='2'
GROUP BY u.user_id, us.orig_posts_count, us.sugg_posts_count

DETAILED EXPLANATION

In your first query, the following posts are being counted (with reasons):

display_orig_posts_count = 3:
  post_id=2: pv.post_id is not null (fails ISNULL() check)
  post_id=4: pv.post_id is not null (fails ISNULL() check)
  post_id=6: p.private='0'          (fails p.private='1' check)

display_sugg_posts_count = 5:
  post_id=2: pv.post_id is not null (fails ISNULL() check)
  post_id=3: p.sugg_by='2'          (fails p.sugg_by<>'2' check)
  post_id=4: pv.post_id is not null (fails ISNULL() check)
  post_id=5: p.sugg_by='2'          (fails p.sugg_by<>'2' check)
  post_id=6: p.private='0'          (fails p.private='1' check)

In your second query, the following posts are being counted (with reasons):

display_orig_posts_count = 1:
  post_id=2: pv.post_id is not null (fails ISNULL() check)

display_sugg_posts_count = 3:
  post_id=2: pv.post_id is not null (fails ISNULL() check)
  post_id=3: p.sugg_by='2'          (fails p.sugg_by<>'2' check)
  post_id=5: p.sugg_by='2'          (fails p.sugg_by<>'2' check)'

In your third query, the following posts are being counted (with reasons):

display_orig_posts_count = 2:
  post_id=4: pv.post_id is not null (fails ISNULL() check)
  post_id=6: p.private='0'          (fails p.private='1' check)

display_sugg_posts_count = 2:
  post_id=4: pv.post_id is not null (fails ISNULL() check)
  post_id=6: p.private='0'          (fails p.private='1' check)

What do we want to be counted:

display_orig_posts_count = 1:
  post_id=2: because p.sugg_by=2, p.private=1 AND (is_authorized)

display_sugg_posts_count = 3:
  post_id=4: because p.sugg_by<>'2', p.private=1 AND (is_authorized)
  post_id=6: because p.sugg_by<>'2', p.private=0

So let's clean this up:

Strip out p.user_id='2' from `CASE1 statements

This is always true anyway, because you're limiting posts to only those posts with user_id='2' by virtue of your JOIN statements.
Use affirmative checks

In the case of display_orig_posts_count, we want certain things. Rather than trying to filter out the things we don't want, let's explicitly look for the things we do want:
```
 COUNT(CASE
   WHEN p.sugg_by='2' AND (p.private='0' OR pv.post_id IS NOT NULL)
   THEN 1
   ELSE NULL
 )
```
In the case of display_sugg_posts_count, let's again look for those things explicitly:
```
 COUNT(CASE
   WHEN p.sugg_by<>'2' AND (p.private='0' OR pv.post_id IS NOT NULL)
   THEN 1
   ELSE NULL
 )
```
Use GROUP BY when using aggregation functions

You shouldn't need LIMIT 1, either, if you're properly grouping aggregated functions. Only one row should ever be returned for each user.

By far the best detailed explanation. Quick note: Does it matter if I drop the us.orig_posts_count, us.sugg_posts_count from the GROUP BY u.user_id, us.orig_posts_count, us.sugg_posts_count and instead do GROUP BY u.user_id? Thank you hrunting!
It doesn't matter in this case because there's only one orig_posts_count/sugg_posts_count pair per user_id value. In fact, you don't actually need to group by anything in this query because the query should always return only one row per user_id value anyway. Still, I do it to be explicit about what is intended, and some SQL systems won't let you use aggregate functions (like COUNT, MAX, AVG, etc.) with non-aggregated fields unless you group by each non-aggregated field.

Collectives™ on Stack Overflow

MySQL - Count Query With Table Join Filtering

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related