I am listing the tags/categories on the user's page. I would like to show the number of posts the user made for each tag. The tags, the posts, and the post-tags are in different tables. The difficulty is, that there are two kind of posts. The posts, and the comments. They are in the same table, but different type. "question" and "answer". the related_id at the answers are the id of the posts they are related to.
I tried to solve in pretty lot of way but couldn't get it to work.
My db structures: For tags:
tagid tag_name
For posts
id type(enum:"question","answer") related_id user_id
For post-tags:
post_id tag_id
The code what I tried is the following:
$user_active_query = mysql_query("select p.id,
p.user_id,
pt.post_id,
count(pt.post_id),
pt.tag_id,
t.tagid,
t.tag_name
from posts p
inner join post_tags pt
inner join tags t
on p.id = pt.post_id
and pt.tag_id = t.tagid
where p.user_id = '$uid'
group by t.tagid");
while($useractive = mysql_fetch_array($user_active_query)) {
$user_active_counter = $useractive['count(pt.post_id)'];
echo "<a href='' class='btn btn-mini' style='margin:3px;'>".$useractive['tag_name']." (".$user_active_counter.")</a>";
}
User id is given on the page. "$uid". I am just tired of the lot of try and asking for correction. First it seemed to be the best way to store the post-tags but now this is a nightmare. I mean, for me, its seems impossible to do this with this structure.
You can get both counts i.e the no of answers and no of questions posted by a user ,here is the trick also use proper join syntax you are missing the on clause for join
SELECT
p.id,
p.user_id,
pt.post_id,
COUNT(pt.post_id) all_posts,
COALESCE(SUM(`type` = 'question')) questions,
COALESCE(SUM(`type` = 'answer')) answers,
pt.tag_id,
t.tagid,
t.tag_name
FROM tags t
LEFT JOIN post_tags pt ON(pt.tag_id = t.tagid)
LEFT JOIN posts p ON p.id = pt.post_id
WHERE p.user_id = '$uid'
GROUP BY t.tagid
Note in mysql sum with some expression will result in a boolean
Edit from comments add another condition using OR in your last join so first condition will join the posts that are associated with tags ,and as your explanation tags are not directly linked with answers but answer are linked to their question with related id so can join the related id of each answer to tag id so this way can get the tags for answers too
SELECT
p.id,
p.user_id,
pt.post_id,
COUNT(pt.post_id) all_posts,
COALESCE(SUM(`type` = 'question')) questions,
COALESCE(SUM(`type` = 'answer')) answers,
pt.tag_id,
t.tagid,
t.tag_name
FROM tags t
LEFT JOIN post_tags pt ON(pt.tag_id = t.tagid)
LEFT JOIN posts p ON (p.id = pt.post_id OR p.related_id = pt.post_id)
WHERE p.user_id = '$uid'
GROUP BY t.tagid
I think you only need to include p.type in your group by clause
$user_active_query = mysql_query("
select
p.id,
p.user_id,
pt.post_id,
count(pt.post_id),
pt.tag_id,
t.tagid,
t.tag_name
from posts p
inner join post_tags pt
inner join tags t
on p.id = pt.post_id
and pt.tag_id = t.tagid
where p.user_id = '$uid'
group by t.tagid, p.type"
);
so, we will group per type too.
I'm not sure I fully understand your schema design. But it sounds like you have two join "paths", one to get to the question type posts, and another to get to child answer type posts.
To get the count of the question-type posts (by a specific user) related to each tag, looks like what you have so far, basically:
SELECT t.tagid
, t.tag_name
, COUNT(p.id) AS count_question
FROM tags t
JOIN post_tags pt
ON pt.tag_id = t.tagid
JOIN posts p
ON p.id = pt.post_id
AND p.type = 'question'
WHERE p.user_id = '$uid'
GROUP BY t.tagid
To get the count of the answer-type posts (by a specific user) related to a question related to each tag, we first need to join to the 'question' (to get the tags), and then join to the related answer. This is nearly identical to the first query, except that we add another join to posts table to get the "child" answer-type posts (so we can get a count of the answer-type posts), and we are not restricting the question-type posts to those from the specific user... we are going to count the answer-type posts from a user posted against any user's question.
SELECT t.tagid
, t.tag_name
, COUNT(a.id) AS count_answer
FROM tags t
JOIN post_tags pt
ON pt.tag_id = t.tagid
JOIN posts p
ON p.id = pt.post_id
AND p.type = 'question'
JOIN posts a
ON a.related_id = p.id
AND a.type = 'answer'
WHERE a.user_id = '$uid'
GROUP BY t.tagid
If each of those queries returns a portion of the total count you want to return, those can be combined, but it gets a little bit messy.
The most straightforward approach is to combine those two results with a UNION ALL set operator, and then use that as an inline view, i.e. run another query against the combined resultset.
For example:
SELECT r.tagid
, r.tagname
, SUM(r.count_post) AS count_total
, SUM(IF(r.type='q',r.count_post,0)) AS count_question
, SUM(IF(r.type='a',r.count_post,0)) AS count_answer
FROM (
SELECT 'q' AS type
, t.tagid
, t.tag_name
, COUNT(p.id) AS count_post
FROM tags t
JOIN post_tags pt
ON pt.tag_id = t.tagid
JOIN posts p
ON p.id = pt.post_id
AND p.type = 'question'
WHERE p.user_id = '$uid'
GROUP BY t.tagid
UNION ALL
SELECT 'a' AS type
, t.tagid
, t.tag_name
, COUNT(a.id) AS count_post
FROM tags t
JOIN post_tags pt
ON pt.tag_id = t.tagid
JOIN posts p
ON p.id = pt.post_id
AND p.type = 'question'
JOIN posts a
ON a.related_id = p.id
AND a.type = 'answer'
WHERE a.user_id = '$uid'
GROUP BY t.tagid
) r
GROUP BY r.tag_id
If you aren't interested in the individual counts (of question and answer type posts), then just remove those two expressions from the SELECT list of the outer query, and you can also remove the type='q', type='a' discriminator column from the inline view query.
This isn't the only way to combine the results, but I think it's the easiest way to verify we're getting a "correct" result (we can run just the inline view query and verify that the results from that are correct.
Another approach to combining them is messier, and more difficult to decipher.
We basically need to join to question-type posts from all users, and then do an outer join operation to the answer-type posts from the specific user.
We can use predicates in the WHERE clause to filter out the rows, so that return only rows that have a matching answer-type row -OR- are a question-type row poseted by the specified user.
In the SELECT list, we need to do some additional filtering, so that we filter out posts from other users.
Something like this:
SELECT t.tagid
, t.tag_name
, COUNT(DISTINCT IF(p.user_id='$uid',p.id,NULL))
+ COUNT(DISTINCT a.id) AS count_total
, COUNT(DISTINCT IF(p.user_id='$uid',p.id,NULL)) AS count_question
, COUNT(DISTINCT a.id) AS count_question
FROM tags t
JOIN post_tags pt
ON pt.tag_id = t.tagid
JOIN posts p
ON p.id = pt.post_id
AND p.type = 'question'
LEFT
JOIN posts a
ON a.related_id = p.id
AND a.type = 'answer'
AND a.user_id = '$uid'
WHERE p.user_id = '$uid'
OR a.id IS NOT NULL
GROUP BY t.tagid
But, I'd don't really like this query, it's too hard to figure out what's going on. I'd opt for the (previous) query, with the UNION ALL inline view. That's easier to decipher.