[development] Convert query to COUNT query

[nan wich]

Thanks, Earl. I didn't think about a subquery.
 
Nancy



________________________________
From: Earl Miles <merlin at logrus.com>
To: development at drupal.org
Sent: Tue, August 17, 2010 1:09:51 PM
Subject: Re: [development] Convert query to COUNT query

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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.drupal.org/pipermail/development/attachments/20100817/89e20c71/attachment-0001.html