How to count best answers of user in mysql?

Question

The user activity plugin uses:

            'SELECT u.userid, count(p.postid) AS qs, count(p.selchildid) AS selected
             FROM ^users u
               LEFT JOIN ^posts p ON u.userid=p.userid AND p.type="Q"
             WHERE u.handle=$';
 

I just need the count of best answers. How would you query it?

 

EDIT: The above gives the count() of how many answers of others the user selected.

To get the user's answers that has been selected the plugin uses:

            'SELECT u.userid, COUNT(a.postid) AS qs, SUM(q.selchildid=a.postid) AS selected
             FROM ^users u
               LEFT JOIN ^posts a ON u.userid=a.userid AND a.type="A"
               LEFT JOIN ^posts q ON a.parentid=q.postid AND q.type="Q"
             WHERE u.handle=$';
 

Comments

If somebody is interested why I need it, I implemented a percentage of how many answers of a user were best answers, that is really motivating! → Example: http://www.klaustukai.lt/user/R%C5%ABta/answers

Answer 1

See the aselecteds column in qa_userpoints

Comments

First I thought, wow, he really thought of everything. But then I saw that I must query the userid because only the handle is available in qa-page-user-answers.php. And then I thought, we talked about the extra core function qa_get_userid_from_handle($handle), I searched for that in entire q2a 1.6.2 code, but it is not there. Didn't you want to add it?

---

qa_handle_to_userid(...)

---

loving you :)

Answer 2

Ah just see that this is getting the best answers selected from user itself!! Not the ones he has received.

SELECT count(p.selchildid) AS selected
             FROM qa_users u
               LEFT JOIN qa_posts p ON u.userid=p.userid AND p.type="Q"
             WHERE u.userid=1

Is there a q2a function available?

Or is there a shorter query?

 

EDIT/UPDATE: That is what seems to work (but maybe there is a shorter query):

            SELECT SUM(q.selchildid=a.postid) AS selected
             FROM qa_users u
               LEFT JOIN qa_posts a ON u.userid=a.userid AND a.type="A"
               LEFT JOIN qa_posts q ON a.parentid=q.postid AND q.type="Q"
             WHERE u.userid=4