[development] Convert query to COUNT query

nan wich nan_wich at bellsouth.net
Tue Aug 17 17:13:34 UTC 2010

In actuality the td.vid and pv.fid come from the page settings and are not 
actually hard-coded. This site will never go to Postgres; Oracle maybe some day. 
No, there is no chance for Views on this one. Remember, this is the full query; 
what I need is the count query equivalent.

From: Ivan Sergio Borgonovo

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

More information about the development mailing list