You need to add in your own custom count query into the pager_query
(it's the 4th arguement). The standard regex that pager_query uses to
come up with it's own count query is what's breaking.
Jamie Holly
http://www.intoxination.net
http://www.hollyit.net
On 4/8/2010 2:13 PM, nan wich wrote:
I have other queries with subqueries that work just fine.
/*Nancy E. Wichmann, PMP*/
------------------------------------------------------------------------
*From:* T L <[email protected]>
*To:* development <[email protected]>
*Sent:* Thu, April 8, 2010 2:06:36 PM
*Subject:* Re: [development] Strange query problem
Hey Nancy,
I think Randy is right, I just ran a quick test with and without
db_rewrite_sql
<http://api.drupal.org/api/function/db_rewrite_sql/6>--it works
without db_rewrite_sql. This makes sense as db_rewrite_sql is among
other things a regex looking for SQL keywords ('from', 'where', etc)
and looks pretty much incompatible w/ subqueries.
Sorry I don't have any ideas for a fix.
Cheers,
Tim
On Thu, Apr 8, 2010 at 1:39 PM, Randy Fay <[email protected]
<mailto:[email protected]>> wrote:
There may certainly be something going on with db_rewrite_sql(),
so you'll probably have to find out what the actual query is when
it gets sent to mysql. Stepping into db_query() is a great way to
do this.
-Randy
On Thu, Apr 8, 2010 at 11:20 AM, Aaron Winborn
<[email protected] <mailto:[email protected]>> wrote:
Can you post your Drupal query? Hard to troubleshoot w/o the
original code.
nan wich wrote:
I have the following query, which works fine in PhpMyAdmin,
but Drupal upchucks on it. Why?
SELECT nc.nid, n.title, n.created, nc.daycount AS
views_today, nc.totalcount AS total_views,
(SELECT COUNT(*) FROM comments c WHERE c.timestamp >=
1270699200 AND c.nid=nc.nid) AS comments_today,
cs.comment_count
FROM node_counter nc
INNER JOIN node n ON n.nid=nc.nid
LEFT JOIN node_comment_statistics cs ON cs.nid=nc.nid
WHERE nc.daycount > 0
ORDER BY nc.daycount DESC
Yes, in Drupal, I do put the brackets around the table names.
The error message says there's a problem at " AS
comments_today, cs.comment_count FROM...".
/*Nancy E. Wichmann, PMP*/
Sometimes the questions are complicated and the answers are
simple. - Dr. Suess
--
Aaron Winborn
Advomatic, LLC
http://advomatic.com/
Drupal Multimedia available in September!
http://www.packtpub.com/create-multimedia-website-with-drupal/book
My blog:
http://aaronwinborn.com/
--
Randy Fay
Drupal Development, troubleshooting, and debugging
[email protected] <mailto:[email protected]>
+1 970.462.7450
--
Tim Loudon
VP of Technology, Abroad101
(781) 686-6096
www.abroad101.com <http://www.abroad101.com/>
