MySql: Select by Count with Union or Joins

Question

I need a way for page moderators to view the members of their page ordered by the total number of posts the user has on that particular page. The posts are stored in different tables such as polls, downloads, wiki, etc (like 15 tables). Each of those have the structure of id, user_id, page_id, title, content, etc etc. The table that lists all the users of a page is called 'favorites'. The structure of that is simply id, user_id, page_id, date.

I currently have a query that unions all of the "content tables" together for a specific user to display ALL of their posts across the site regard of what page it is on, I could easily make it specific to each page using WHERE page_id = $page_id). I tried using that code to do what I'm trying to do in the moderator area. As I mentioned, in the moderator area I need to sort the list of users by their total posts count DESC.

I have this code but it is not working.

$getfans = mysql_query("SELECT DISTINCT user_id, ((SELECT id FROM wiki WHERE wiki.page_id = $page_id AND wiki.user_id = favorites.user_id)
UNION ALL
(SELECT id FROM downloads WHERE downloads.page_id = $page_id AND downloads.user_id = favorites.user_id)
UNION ALL
(SELECT id FROM polls WHERE polls.page_id = $page_id AND polls.user_id = favorites.user_id)) AS posts 
FROM favorites WHERE favorites.page_id = $page_id AND favorites.status = 0 ORDER BY posts DESC", $conn);

I tried other methods that also did not work but this one made the most logical sense to me so it's the only one I'm going to display

Please help. Thanks.

Additionally...

This was perhaps a step in the right direction

$getfans = mysql_query("SELECT DISTINCT user_id, (SELECT COUNT(id) FROM polls WHERE page_id = $page_id AND user_id = favorites.user_id) AS posts FROM favorites WHERE favorites.page_id = $page_id AND favorites.status = 0 ORDER BY posts DESC", $conn);

When I echo'ed $posts it gave me the correct count for the user's polls for that page.

HOWEVER when I tried to do more than one table it only returned one user.

Example:

$getfans = mysql_query("SELECT DISTINCT user_id, SUM((SELECT COUNT(id) FROM polls WHERE page_id = $page_id AND user_id = favorites.user_id)+(SELECT COUNT(id) FROM wiki WHERE page_id = $page_id AND user_id = favorites.user_id)) AS posts FROM favorites WHERE favorites.page_id = $page_id AND favorites.status = 0 ORDER BY posts DESC", $conn);

I FOUND THE SOLUTION
if curious, just read my post below.

Answer 1

Use left joins!

SELECT 
    f.user_id, 
    COUNT(w.user_id) + COUNT(d.user_id) + COUNT(p.user_id) as PageCount
FROM 
    favorites f
    left join wiki w on
        f.page_id = w.page_id
        and f.user_id = w.user_id
    left join downloads d on
        f.page_id = d.page_id
        and f.user_id = d.user_id
    left join polls p on
        f.page_id = p.page_id
        and f.user_id = p.user_id
WHERE 
    f.page_id = $page_id 
    AND f.status = 0
GROUP BY
    f.user_id
ORDER BY PageCount DESC

If you'd like to stick with the union approach:

select
    f.user_id,
    count(x.user_id) as PageCount
from
    favorites f
    inner join (
        select user_id, page_id from wiki
        union all
        select user_id, page_id from downloads
        union all
        select user_id, page_id from polls
        union all
        select user_id, page_id from videos
    ) x on
       f.user_id = x.user_id
       and f.page_id = x.page_id
WHERE 
    f.page_id = $page_id 
    AND f.status = 0
group by
    f.user_id
ORDER BY PageCount DESC

You want to use a group by for aggregates, and the count function will ignore any nulls that you run into, so it will only count positive matches.

Answer 2

I FOUND THE SOLUTION. I thought I had this earlier but I guess I was off by the slightest bit. Thanks everyone who participated.

$getfans = mysql_query("SELECT DISTINCT user_id,
(SELECT COUNT(id) FROM wiki WHERE page_id = $page_id AND user_id = favorites.user_id) + 
(SELECT COUNT(id) FROM downloads WHERE page_id = $page_id AND user_id = favorites.user_id) + 
(SELECT COUNT(id) FROM polls WHERE page_id = $page_id AND user_id = favorites.user_id)  
AS posts FROM favorites
WHERE favorites.page_id = $page_id
AND favorites.status = 0
ORDER BY posts DESC", $conn);

just keep doing the subquery with the + at the end for each new table. Perhaps not the most concise code but it works.

This works. I can't accept it as an answer for two days :(

Answer 3

This is my favorite SQL query, for one table,

Select Distinct user_id, Count(user_id) as C from yourtable group by user_id 

will show you each person's user_id and the number of rows they have in that table. Combining a bunch of these results should be straightforward.

BTW, if all the tables have the same structure, why not have them all be in the same table? You could add a column indicating what type of item the record applies to.