I am trying to find the highest node ID in a range of the node table. So far, the only query that works requires a sub-query: $nid = db_result(db_query(db_rewrite_sql(" SELECT MAX(r.nid) FROM ( SELECT n.nid FROM {node} n ORDER BY n.nid LIMIT %d ) r "), $count)); I would prefer for my module to continue working for users stuck with MySQL versions lower than 4.1. Is there any way to rewrite this query so it doesn't use a sub-query?
I would prefer for my module to continue working for users stuck with MySQL versions lower than 4.1. Is there any way to rewrite this query so it doesn't use a sub-query?
In Content Blocker (http://drupal.org/project/contentblocker, a newly posted module that enables users to filter the content they see, e.g., block content by user X) I used a subquery and a MySQL pre 4.1 workaround, see below. You might consider something similar. But in your case I don't see the need for the subquery. // MySQL didn't support subqueries before 4.1. if ($GLOBALS['db_type'] == 'mysql' && version_compare(mysql_get_server_info($GLOBALS['active_db']), '4.1.0', '<')) { $ids = array(); $result = db_query("SELECT id FROM {contentblocker} WHERE type = '%s' AND uid = %d", $type, $uid); while ($row = db_fetch_array($result)) { $ids[] = $row['id']; } $return['where'] = "(". $data['block_table_alias'] ."." .$data['block_field'] ." NOT IN(". implode(',', $ids) .") OR ". $data['block_table_alias'] ."." .$data['block_field'] ." IS NULL)"; } else { // $type is potentially user input, so escape it. $return['where'] = "(". $data['block_table_alias'] ."." .$data['block_field'] ." NOT IN(SELECT id FROM {contentblocker} WHERE type = '". db_escape_string($type) ."' AND uid = ". $uid .") OR ". $data['block_table_alias'] ."." .$data['block_field'] ." IS NULL)"; }
But in your case I don't see the need for the subquery.
(Sorry, managed to send that before it was done.) Is this what you're trying to do? db_result(db_query(db_rewrite_sql('SELECT max(n.nid) FROM {node} n ORDER BY n.nid LIMIT %d'), $count));
On Apr 23, 2007, at 12:18 AM, Nedjo Rogers wrote:
But in your case I don't see the need for the subquery.
Is this what you're trying to do?
db_result(db_query(db_rewrite_sql('SELECT max(n.nid) FROM {node} n ORDER BY n.nid LIMIT %d'), $count));
That's exactly what I'm trying to do. But I always get the highest node ID in the table, rather than the highest in the range. That's why I used the sub-query to force the max ID to be selected from within the range.
Darren Oh wrote:
On Apr 23, 2007, at 12:18 AM, Nedjo Rogers wrote:
But in your case I don't see the need for the subquery.
Is this what you're trying to do?
db_result(db_query(db_rewrite_sql('SELECT max(n.nid) FROM {node} n ORDER BY n.nid LIMIT %d'), $count));
That's exactly what I'm trying to do. But I always get the highest node ID in the table, rather than the highest in the range. That's why I used the sub-query to force the max ID to be selected from within the range.
So if $count = 5: 1 2 4 8 9 <-- you want this nid? ------- limit 11 12 14 ...
On Apr 22, 2007, at 10:34 PM, Darren Oh wrote:
I am trying to find the highest node ID in a range of the node table. So far, the only query that works requires a sub-query:
$nid = db_result(db_query(db_rewrite_sql(" SELECT MAX(r.nid) FROM ( SELECT n.nid FROM {node} n ORDER BY n.nid LIMIT %d ) r "), $count));
I would prefer for my module to continue working for users stuck with MySQL versions lower than 4.1. Is there any way to rewrite this query so it doesn't use a sub-query?
Instead of db_query try db_query_range so your LIMIT isn't forced to mysql. http://api.drupal.org/api/5/function/db_query_range
I think this should work: db_query_range(db_rewrite_sql("SELECT n.nid FROM {node} n ORDER BY n.nid"), $count-1, 1); Erdem Köse Darren Oh wrote:
I am trying to find the highest node ID in a range of the node table. So far, the only query that works requires a sub-query:
$nid = db_result(db_query(db_rewrite_sql(" SELECT MAX(r.nid) FROM ( SELECT n.nid FROM {node} n ORDER BY n.nid LIMIT %d ) r "), $count));
I would prefer for my module to continue working for users stuck with MySQL versions lower than 4.1. Is there any way to rewrite this query so it doesn't use a sub-query?
On Apr 23, 2007, at 9:58 AM, Erdem Köse wrote:
I think this should work: db_query_range(db_rewrite_sql("SELECT n.nid FROM {node} n ORDER BY n.nid"), $count-1, 1);
Perfect! Now that you point it out, I have no idea why I was using the MAX() function in the first place.
participants (5)
-
Darren Oh -
Erdem Köse -
Matthew Farina -
Nedjo Rogers -
sime