db_select with subquery including conditions from 2 tables

Question

I spent much time without success to search how to convert with db_select the following query, running perfectly with db_query :

    SELECT *
    FROM job_orders j
    WHERE j.statut = 'To do'
    AND j.job
    IN ( SELECT r.job
    FROM referential r
    WHERE r.category = j.category
    AND r.label = :label
    AND r.activity = :activity ),
    array(':label' => $label, ':activity' => $activity);

Here is the corresponding (?) db_select query I wrote :

$subquery = db_select('referential', 'r')
    ->fields('r', array('job'))
    ->condition('r.category', 'j.category')
    ->condition('r.label', $label)
    ->condition('r.activity', $activity);

$query = db_select('job_orders', 'j')
    ->fields('j')
    ->condition('j.statut', 'To do')
    ->condition('j.job', $subquery, 'IN');
$results = $query->execute();

I got no result with this db_select query, while I got answers with the db_query one... could somebody tell me what is wrong in my db_select query ?

Thank you.

Answer 1

To achieve your desired behavior you have two options.

If you really like to filter by using a subquery, you should execute it first, parse its result set to an array and set this array as parameter to the condition method of your second queries db_select:

// Run your subquery first.
$subquery = db_select('referential', 'r')
  ->fields('r', array('job'))
  ->condition('r.category', 'j.category')
  ->condition('r.label', $label)
  ->condition('r.activity', $activity);
$result = $query->execute()->fetchAllAssoc('job');

// Make sure $results is initialized for further evaluation.
$results = array();

// If we have referential results, execute the query on job orders.
if (!empty($result)) {
  $query = db_select('job_orders', 'j')
    ->fields('j')
    ->condition('j.statut', 'To do')
    ->condition('j.job', array_keys($result), 'IN');
  $results = $query->execute();
}

The far more elegant and therefore suggested solution is to using an INNER JOIN on your query. The target query would look like:

SELECT j.*
  FROM job_orders j
  INNER JOIN referential r ON (r.job = j.job)
  WHERE j.statut = 'To do' AND r.label = :label AND r.activity = :activity

You can achieve this solution using db_select by adding a join to your $query:

  // Run the query using an INNER JOIN.
  $query = db_select('job_orders', 'j')
    ->join('referential', 'r', 'r.job = j.job');
  $query->fields('j')
    ->condition('j.statut', 'To do')
    ->condition('r.label', $label)
    ->condition('r.activity', $activity);
  $results = $query->execute();

Edit: As I don't see any special reason for using db_select in your use case, I'd also suggest on sticking to db_query here for better performance of your application.

Edit 2: In the above 'solutions' I have overseen you are using two conditions between the tables. In this case, you have to add both conditions to your JOIN:

SELECT j.*
  FROM job_orders j
  INNER JOIN referential r ON (r.job = j.job AND r.category = j.category)
  WHERE j.statut = 'To do' AND r.label = :label AND r.activity = :activity

Which would need the following db_select:

  $query = db_select('job_orders', 'j')
    ->join('referential', 'r', 'r.job = j.job AND r.category = j.category');
  $query->fields('j')
    ->condition('j.statut', 'To do')
    ->condition('r.label', $label)
    ->condition('r.activity', $activity);
  $results = $query->execute();