[development] Convert query to COUNT query

[nan wich]

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.
 
Nancy




________________________________
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.


SELECT COUNT(*) AS num
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
http://www.webthatworks.it
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.drupal.org/pipermail/development/attachments/20100817/8f8ee870/attachment.html