0

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.

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();
4
  • Thanks for your quick answer. I'd want to use db_select because I need a pager to display results, and extend PagerDefault doesn't work with db_query. I've tried your 2 solutions, but still no success :( - the first one can't work because of "condition('r.category', 'j.category')" : "j" alias isn't yet associated with the corresponding table - the second one return no results too But it gave me an idea : I tried to run db_select queries without "condition('r.category', 'j.category')", and it return results ! but too much of course, the condition is needed. Here is a summary of wh – phg Jan 30 '16 at 11:37
  • Altered my above answer. (Edit 2) – Mario Steinitz Jan 30 '16 at 13:03
  • "Mais c'est bien sûr !" as we say in France :) Thank you very very much Mario, it's working like a charm ! – phg Jan 30 '16 at 19:48
  • Sorry bummi and kiamlaluno, I'm new on the forum and I didn't know. I'll remember that ! – phg Jan 30 '16 at 19:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.