[eluser]OverZealous[/eluser]
@Naren:
First, Distinct works on the object you are querying. In this case, you are querying Knowledge:
Code:
$userObject->authored_knowledge->distinct()->where_related('group', 'id >', 0)->count();
// is the same as
$k = $userObject->authored_knowledge;
$k->distinct()
$k->where_related('group', 'id >', 0)
$k->count();
However, it won't work, as you determined. I did some tests. The correct solution looks like this in SQL:
Code:
SELECT COUNT(DISTINCT knowledges.id)
FROM knowledges
LEFT OUTER JOIN users as authors ON knowledges.author_id = authors.id
LEFT OUTER JOIN groups_knowledges ON groups_knowledges.knowledge_id = knowledges.id
LEFT OUTER JOIN groups ON groups_knowledges.group_id = groups.id
WHERE authors.id = 1
AND groups.id > 0
Currently there is no way to call COUNT(DISTINCT) (which is only supported on certain databases anyway).
Which works pretty well, because you only query the ids. It's not nearly as efficient, admittedly. I'll look at implementing a count_distinct($column) in the future.