Search code examples
mysqldrupaldrupal-7

Drupal 7 DB API Query with Condition db_or etc

I'm having a little issue with getting an sql query to work in drupal 7. Basically, I'd like to know if my drupal code for the db_api is correct for the SQL query that I expect it to generate.

Please see code below:

/*
  SQL Query
  Note: The the 'a' in '%a%', is just a sample of some text input by a user. 
*/

SELECT u.uid, fn.field_first_name_value, ln.field_last_name_value 
FROM role r
JOIN users_roles ur ON (r.rid = ur.rid)
JOIN users u ON (u.uid = ur.uid)
JOIN field_data_field_first_name fn ON (fn.entity_id = u.uid)
JOIN field_data_field_last_name ln ON (ln.entity_id = u.uid)
WHERE (fn.field_first_name_value LIKE '%a%' OR ln.field_last_name_value LIKE '%a')
AND r.name = 'custom_role'
LIMIT 0, 5


/*
   DRUPAL 7 DB API CODE
 */

$name = $_POST['name'];
$res = db_select('role', 'r');

$res->join('users_roles', 'ur', 'ur.rid = r.rid');
$res->join('users', 'u', 'u.uid = ur.uid');
$res->join('field_data_field_first_name', 'fn', 'fn.entity_id = u.uid');
$res->join('field_data_field_last_name', 'ln', 'ln.entity_id = u.uid');

$res->fields('u', array('uid'));
$res->fields('fn', array('field_first_name_value'));
$res->fields('ln', array('field_last_name_value'));                        

$or = db_or()->condition('fn.field_first_name_value', '%'.db_like($name).'%', 'LIKE');
$or->condition('ln.field_first_name_value', '%'.db_like($name).'%', 'LIKE');

$res->condition($or)->condition('r.name', 'custom_role', '=');
$res->range(0,5);            

$res->execute();

Also, if there is way that I can see the generated sql by the db api, that would be great for debugging. Thanks.

Solution

  • I cannot execute it because i do not have the same tables like you but your generated query looks good.

    SELECT u.uid AS uid, fn.field_first_name_value AS field_first_name_value, ln.field_last_name_value AS field_last_name_value
FROM
{role} r
INNER JOIN {users_roles} ur ON ur.rid = r.rid
INNER JOIN {users} u ON u.uid = ur.uid
INNER JOIN {field_data_field_first_name} fn ON fn.entity_id = u.uid
INNER JOIN {field_data_field_last_name} ln ON ln.entity_id = u.uid
WHERE ( (fn.field_first_name_value LIKE :db_condition_placeholder_0 ESCAPE '\\') OR (ln.field_first_name_value LIKE :db_condition_placeholder_1 ESCAPE '\\') )AND (r.name = :db_condition_placeholder_2)
LIMIT 5 OFFSET 0

    If you want to print the query yourself you can use

    print $res->__toString() . "\n\n";
var_export($res->getArguments());

    Or 

    watchdog('MY_QUERY_DEBUG', $res->__toString(), $res->getArguments());

    To add it to your watchdog log.