<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:arial, helvetica, sans-serif;font-size:12pt"><DIV></DIV>
<DIV>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.<BR> </DIV>
<P><FONT face="bookman old style, new york, times, serif" color=#ff007f size=4><EM><STRONG>Nancy</STRONG></EM></FONT><BR></P>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: arial, helvetica, sans-serif"><BR>
<DIV style="FONT-SIZE: 10pt; FONT-FAMILY: arial, helvetica, sans-serif"><FONT face=Tahoma size=2>
<HR SIZE=1>
<B><SPAN style="FONT-WEIGHT: bold">From:</SPAN></B> Ivan Sergio Borgonovo<BR></FONT><BR>> I'm havng trouble getting my head around converting this to a<BR>> count query for doing a pager_query.<BR>> <BR>> SELECT <A href="http://c.name/" target=_blank>c.name</A>, c.mail, COUNT(*) AS num, pv.value AS usertype,<BR> ^^^^^^^^^^^^^^^ ???<BR>> <A href="http://td.name/" target=_blank>td.name</A> AS role FROM {comments} c <BR>> INNER JOIN {node} n ON n.nid=c.nid<BR>> INNER JOIN {term_node} tn ON tn.nid=n.nid AND tn.vid=n.vid<BR>> INNER JOIN {term_data} td ON td.tid=tn.tid AND td.vid = 2<BR> ^^^^^^^^^^<BR>> LEFT JOIN {profile_values} pv ON pv.uid=c.uid AND
pv.fid = 13<BR> ^^^^^^^^^^^<BR>This is bad, you don't hard-code something that depends on a<BR>configuration you can change through web interface.<BR>No chance this is going to work with views?<BR><BR>> WHERE c.mail <> ''<BR>> GROUP BY role, c.mail ORDER BY num DESC<BR> ^^^^^^^^^^^^^^^^^^^^^<BR>You're grouping on too few columns than the one you're returning in<BR>the result. This is not going to work on Postgres.<BR><BR><BR>SELECT COUNT(*) AS num<BR>FROM {comments} c <BR>INNER JOIN {term_node} tn ON tn.nid=c.nid AND tn.vid=n.vid<BR> INNER JOIN {term_data} td ON td.tid=tn.tid AND td.vid = 2<BR> WHERE c.mail <> ''<BR> GROUP BY role, c.mail<BR><BR>GROUP BY may perform better than DISTINCT and it can have the<BR>same
effect.<BR><BR>I didn't tested it<BR><BR>-- <BR>Ivan Sergio Borgonovo<BR>http://www.webthatworks.it<BR><BR></DIV></DIV></div></body></html>