order by dates that match criteria

[jackmn1]

I have the following query that outputs users with the highest number of favorites that they received in the past week in descending order:

 

SELECT COUNT(faves.user_id) AS topFaves, faves.created_on, user_name 
FROM users 
INNER JOIN faves ON faves.user_id= users.id 
WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= created_on 
GROUP BY id ORDER BY topFaves DESC LIMIT 10 

 

I would like to be able to extend this list to contain all users, not just from the past week, but still order them by the same criteria (the number of favorites they got in the past week).

I tried to include a subquery in the select but didnt have any luck with it.

 

Thanks in advance for any help

[silkfire]

What does

 

SELECT COUNT(faves.user_id) AS topFaves, faves.created_on, user_name

FROM users

INNER JOIN faves ON faves.user_id= users.id

GROUP BY id ORDER BY topFaves DESC LIMIT 10

 

give?

 

I removed the WHERE clause.

[requinix]

You mean to include the users but give them a topFaves=0? Try a LEFT [OUTER] JOIN and modify the WHERE so that it doesn't mind if the date is NULL (which is the data you get back if there aren't any matching rows).

[jackmn1]

What does

 

SELECT COUNT(faves.user_id) AS topFaves, faves.created_on, user_name

FROM users

INNER JOIN faves ON faves.user_id= users.id

GROUP BY id ORDER BY topFaves DESC LIMIT 10

 

give?

 

I removed the WHERE clause.

 

It gives a list of the users by the number of faves they got. If you add the WHERE to it, it displays only the users that got top faves the past week.

[jackmn1]

You mean to include the users but give them a topFaves=0? Try a LEFT [OUTER] JOIN and modify the WHERE so that it doesn't mind if the date is NULL (which is the data you get back if there aren't any matching rows).

 

Thanks requinix, you described exactly what I want to display, but how should I write the left join? should I use a subquery such as this?

LEFT OUTER JOIN (SELECT COUNT(faves.user_id) AS topFaves, faves.created_on, user_name, id
FROM users 
INNER JOIN faves ON faves.user_id= users.id
WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= created_on
GROUP BY id ORDER BY topFaves) weekFaves ON weekFaves.id = users.id 

 

And what should I do with the where clause already in the parent query? I'm also not sure how to allow the null...

[requinix]

Change the query. You have an INNER JOIN - change that to a LEFT OUTER JOIN. And remove the created_on from the SELECT list: it's nonsensical there.

SELECT COUNT(faves.user_id) AS topFaves, user_name
FROM users
LEFT OUTER JOIN faves ON faves.user_id = users.id
WHERE created_on IS NULL OR DATE_SUB(CURDATE(), INTERVAL 7 DAY) GROUP BY id ORDER BY topFaves DESC LIMIT 10

Your query might be more efficient (depends on how MySQL optimizes) if you figure out what last week was in your PHP and put the value into the condition. Otherwise there's a risk that MySQL will try to evaluate the DATE_SUB(...) for every single row in the table, which is silly because the value will be the same every time.

[jackmn1]

Got it! I appreciate your help