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
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 <winborn@advomatic.com>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, LLChttp://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 randy@randyfay.com +1 970.462.7450
You can look at the global $query variable or use devel to see if the query is being written correctly. ----- Adam A. Gregory Drupal Developer & Consultant Web: AdamAGregory.com Twitter: twitter.com/adamgregory Phone: 910.808.1717 Cell: 919.306.6138 On Thu, Apr 8, 2010 at 1:39 PM, Randy Fay <randy@randyfay.com> 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 <winborn@advomatic.com>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, LLChttp://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 randy@randyfay.com +1 970.462.7450
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 <randy@randyfay.com> 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 <winborn@advomatic.com>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, LLChttp://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 randy@randyfay.com +1 970.462.7450
-- Tim Loudon VP of Technology, Abroad101 (781) 686-6096 www.abroad101.com
I have other queries with subqueries that work just fine. Nancy E. Wichmann, PMP ________________________________ From: T L <tloud365@gmail.com> To: development <development@drupal.org> 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--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 <randy@randyfay.com> 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 <winborn@advomatic.com> 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 randy@randyfay.com +1 970.462.7450
-- Tim Loudon VP of Technology, Abroad101 (781) 686-6096 www.abroad101.com
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 <tloud365@gmail.com> *To:* development <development@drupal.org> *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 <randy@randyfay.com <mailto:randy@randyfay.com>> 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 <winborn@advomatic.com <mailto:winborn@advomatic.com>> 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 randy@randyfay.com <mailto:randy@randyfay.com> +1 970.462.7450
-- Tim Loudon VP of Technology, Abroad101 (781) 686-6096 www.abroad101.com <http://www.abroad101.com/>
$query = "SELECT nc.nid, n.title, nc.daycount AS views, nc.totalcount, " . "(SELECT COUNT(*) FROM {comments} c WHERE c.timestamp >= %d 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"; $result = pager_query($query, $page_limit, 0, NULL, $today); This comes out looking like: SELECT nc.nid, n.title, nc.daycount AS views, nc.totalcount, (SELECT COUNT(*) FROM {comments} c WHERE c.timestamp >= %d 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 user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AS comments_today, cs.comment_count FROM node_counter nc INNER JOIN node n ON ' at line 1 query: 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 in C:\www\webapps\drupal6\sites\all\modules\mymodule\mymodule.pages.inc on line 672. Nancy E. Wichmann, PMP ________________________________ From: Aaron Winborn <winborn@advomatic.com> To: development@drupal.org Sent: Thu, April 8, 2010 1:20:38 PM Subject: Re: [development] Strange query problem 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/
On 4/8/2010 11:07 AM, nan wich wrote:
$query = "SELECT nc.nid, n.title, nc.daycount AS views, nc.totalcount, " . "(SELECT COUNT(*) FROM {comments} c WHERE c.timestamp >= %d 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"; $result = pager_query($query, $page_limit, 0, NULL, $today);
pager_query() is failing you because it can't rewrite that properly. You need to write your own count query for pager_query().
How did I know that Earl would be the one to show me the error of my ways... Thanks, Earl that did the trick. Nancy E. Wichmann, PMP ________________________________ From: Earl Miles <merlin@logrus.com> To: development@drupal.org Sent: Thu, April 8, 2010 2:21:28 PM Subject: Re: [development] Strange query problem On 4/8/2010 11:07 AM, nan wich wrote:
$query = "SELECT nc.nid, n.title, nc.daycount AS views, nc.totalcount, " . "(SELECT COUNT(*) FROM {comments} c WHERE c.timestamp >= %d 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"; $result = pager_query($query, $page_limit, 0, NULL, $today);
pager_query() is failing you because it can't rewrite that properly. You need to write your own count query for pager_query().
And Devel says this: SELECT nc.nid, n.title, nc.daycount AS views, nc.totalcount, (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 LIMIT 0, 20 And pasting that into PhpMyAdmin gives me the results I would expect. Nancy E. Wichmann, PMP ________________________________ From: Aaron Winborn <winborn@advomatic.com> To: development@drupal.org Sent: Thu, April 8, 2010 1:20:38 PM Subject: Re: [development] Strange query problem 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/
participants (7)
-
Aaron Winborn -
Adam Gregory -
Earl Miles -
Jamie Holly -
nan wich -
Randy Fay -
T L