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