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.
Options

Getting the number of people participating in a discussion

edited May 2007 in Vanilla 1.0 Help
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:

$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.

Comments

  • Options
    Just thinking off the top of my head:$s->AddSelect('distinct AuthUserID', 'c', 'participators', count);... on second thought, maybe that won't work quite right.
  • Options
    Oh, yeah. I tried that one, and it turns into 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.
  • Options
    edited May 2007
    I think I got it now: $s->AddSelect('distinct c.AuthUserID', '', 'participators', 'count');
    Heck, even this works:$s->AddSelect('count(distinct c.AuthUserID) AS participators', '');
  • Options
    That works perfectly. Thanks for the help, WallPhone!
This discussion has been closed.