Vanilla 1 is no longer supported or maintained. If you need a copy, you can get it here.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.
Getting the number of people participating in a discussion
I'm trying to build some SQL with the sql builder to fetch data from the database, and in the query I wanted to do a count(distinct x), but that's not possible as far as I can tell, so I was wondering if anyone had any ideas on how to solve it. What I want to do is get discussion and related data, specifically the discussion name (plus some more), the username of the author, and the number of users who have participated in the discussion, so far I have this:
Now, the problem is that the count in the above build will just return the same as the number of posts. The 'function parameter' parameter that AddSelect accepts adds it at the end of a function, so if I did
Is there some other way to get the number of participating members of a discussion? I'd really like to get everything into one SQL query if possible, rather than splitting it up.
$s->SetMainTable('Discussion', 'd');
$s->AddJoin('User', 'u', 'UserID', 'd', 'AuthUserID', 'left join');
$s->AddJoin('Comment', 'c', 'DiscussionID', 'd', 'DiscussionID', 'left join');
$s->AddSelect(array('DiscussionID', 'AuthUserID', 'Name', 'DateCreated', 'DateLastActive', 'CountComments'), 'd');
$s->AddSelect('Name', 'u', 'UserName');
$s->AddSelect('AuthUserID', 'c', 'participators', 'count');
[some AddWheres]
$s->AddGroupBy('DiscussionID', 'd');Now, the problem is that the count in the above build will just return the same as the number of posts. The 'function parameter' parameter that AddSelect accepts adds it at the end of a function, so if I did
$s->AddSelect('AuthUserID', 'c', 'participators', 'count', 'distinct'); I'd get count(c.AuthUserID, distinct) instead of the count(distinct c.AuthUserID) I want. Is there some other way to get the number of participating members of a discussion? I'd really like to get everything into one SQL query if possible, rather than splitting it up.
0
This discussion has been closed.
Comments
$s->AddSelect('distinct AuthUserID', 'c', 'participators', count);... on second thought, maybe that won't work quite right.count(c.) as participators. I think the sql builder recognises that 'distinct AuthUserID' isn't a valid field name, and just discards it. Thanks for the suggestion though.$s->AddSelect('distinct c.AuthUserID', '', 'participators', 'count');Heck, even this works:
$s->AddSelect('count(distinct c.AuthUserID) AS participators', '');