[development] Avoiding sub-queries

[Nedjo Rogers]

> 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)";
  }