[development] Convert query to COUNT query

Ivan Sergio Borgonovo mail at webthatworks.it
Tue Aug 17 15:49:36 UTC 2010

On Wed, 11 Aug 2010 12:27:31 -0700 (PDT)
nan wich <nan_wich at bellsouth.net> 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
This is bad, you don't hard-code something that depends on a
configuration you can change through web interface.
No chance this is going to work with views?

>  WHERE c.mail <> ''
>  GROUP BY role, c.mail ORDER BY num DESC
You're grouping on too few columns than the one you're returning in
the result. This is not going to work on Postgres.

 FROM {comments} c 
 INNER JOIN {term_node} tn ON tn.nid=c.nid AND tn.vid=n.vid
 INNER JOIN {term_data} td ON td.tid=tn.tid AND td.vid = 2
 WHERE c.mail <> ''
 GROUP BY role, c.mail

GROUP BY may perform better than DISTINCT and it can have the
same effect.

I didn't tested it

Ivan Sergio Borgonovo

More information about the development mailing list