[development] Convert query to COUNT query

Earl Miles merlin at logrus.com
Tue Aug 17 17:09:51 UTC 2010

On 8/11/2010 12:27 PM, nan wich wrote:
> I'm havng trouble getting my head around converting this to a count
> query for doing a pager_query.
> SELECT c.name, c.mail, COUNT(*) AS num, pv.value AS usertype, td.name AS
> role
>  FROM {comments} c
>  INNER JOIN {node} n ON n.nid=c.nid
>  INNER JOIN {term_node} tn ON tn.nid=n.nid AND tn.vid=n.vid
>  INNER JOIN {term_data} td ON td.tid=tn.tid AND td.vid = 2
>  LEFT JOIN {profile_values} pv ON pv.uid=c.uid AND pv.fid = 13
>  WHERE c.mail <> ''
>  GROUP BY role, c.mail ORDER BY num DESC

You can almost always do:

SELECT COUNT(*) as expression FROM (SELECT ...);

Drop the ORDER BY and any fields that you don't need from the query (you
need the group by fields) and you'll be okay.

More information about the development mailing list