How can I determine which user is the top one in the specific tag?

Question

I have a question and answer website like stackoverflow. Here is the structure of some tables:

-- {superfluous} means some other columns which are not related to this question

// q&a
+----+-----------------+--------------------------+------+-----------+-----------+
| id |      title      |            body          | type |  related  | author_id |
+----+-----------------+--------------------------+------+-----------+-----------+
| 1  | How can I ...   | I'm trying to make ...   |  q   | NULL      | 3         |
| 2  |                 | You can do that by ...   |  a   | 1         | 1         |
| 3  | Why should I .. | I'm wonder, why ...      |  q   | NULL      | 1         |
| 4  |                 | First of all you ...     |  a   | 1         | 2         |
| 5  |                 | Because that thing ...   |  a   | 3         | 2         |
+----+-----------------+--------------------------+------+-----------+-----------+

// users
+----+--------+-----------------+
| id |  name  |  {superfluous}  |
+----+--------+-----------------+
| 1  | Jack   |                 |
| 2  | Peter  |                 |
| 3  | John   |                 |
+----+--------+-----------------+

// votes
+----+----------+-----------+-------+-----------------+
| id |  user_id |  post_id  | value |  {superfluous}  |
+----+----------+-----------+-------+-----------------+
| 1  | 3        |  4        |  1    |                 |
| 2  | 1        |  1        | -1    |                 |
| 3  | 2        |  1        |  1    |                 |
| 4  | 3        |  2        | -1    |                 |
| 5  | 1        |  4        |  1    |                 |
| 6  | 3        |  5        | -1    |                 |
+----+--------+-------------+-------+-----------------+

// tags
+----+------------+-----------------+
| id |    name    |  {superfluous}  |
+----+------------+-----------------+
| 1  | PHP        |                 |
| 2  | SQL        |                 |
| 3  | MySQL      |                 |
| 4  | HTML       |                 |
| 5  | CSS        |                 |
| 6  | C#         |                 |
+----+------------+-----------------+

// q&aTag
+-------+--------+
| q&aid | tag_id |
+-------+--------+
| 1     | 1      |
| 1     | 4      |
| 3     | 5      |
| 3     | 4      |
| 4     | 6      |
+-------+--------+

Now I need to find top users in a specific tag. For example, I need to find Peter as top user in PHP tag. Because his answer for question1 (which has PHP tag) has earned 2 upvotes. Is doing that possible?

Answer 1

Try this:

select q1.title, u.id, u.name, sum(v.value) total from `q&a` q1
left join `q&atag` qt ON q1.id = qt.`q&aid`
inner join tags t ON qt.tag_id = t.id
left join `q&a` q2 ON q2.related = q1.id
left join users u ON q2.author_id = u.id
left join votes v ON v.post_id = q2.id

where t.name = 'PHP'
group by q1.id, u.id

and here is a simple divided solution:

Let us divide it into sub queries:

1. get the id of the tag you will search for: select id from tags where name = 'PHP'
2. get the questions with this tag: select 'q&aid' from 'q&aTag' where tag_id = 1.
3. get the ids of answers for that question: select id, author_id fromq&awhere related in (2.)
4. get the final query: select user_id, sum(value) from votes where post_id in (3.) group by user_id

Now combining them all give the result:

select user_id, sum(`value`) total from votes
where post_id in (
    select id from `q&a` where related in (
        select `q&aid` from `q&aTag` where tag_id IN (
            select id from tags where name = 'PHP'
        )
    )
)
group by user_id

you can add this at the end if you want only one record:

order by total desc limit 1