Web design, programming, graphics, and pretty much anything else I care about.

Joins can't be chained using Drupal 7 dynamic queries

I had a problem with a query I was building for a site migration from Drupal 6 to 7. I'm building the query dynamically as an object in Drupal 7. (The details aren't important.)

<?php
$query
= db_select('users_roles', 'r')
             ->
fields('r', array('uid', 'rid'))
             ->
condition('r.uid', $source_id, '=');
$results = $query->execute();
?>

I wanted to add a join, because what I really wanted are role names, so I did the following:

<?php
$query
= db_select('users_roles', 'r')
             ->
join('role', 'ur', 'r.rid=ur.rid')
             ->
fields('r', array('uid', 'rid'))
             ->
condition('r.uid', $source_id, '=');
$results = $query->execute();
?>

This threw errors relating the ->fields line and non-objects. As with most things in PHP, the problem is the line before. It turns out, you can't chain a join when building a dynamic query. I didn't realize this until I checked the documentation - http://drupal.org/node/1060924 .

The code just needed to be changed to the following:

<?php
$query
= db_select('users_roles', 'r')
             ->
select('users_roles', 'r')
             ->
fields('ur', array('name'))
             ->
condition('r.uid', $source_id, '=');
$query->join('role', 'ur', 'r.rid=ur.rid');
?>