计算用户帖子

Question

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.

Answer 1

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