I’m trying to create a dynamic query, the query logic conditions needed are: AND, and OR.

The main query will include two sub queries.

The ConditionInterface::condition documentation states that:

If called with 1 parameter, it should be a ConditionInterface that in itself forms a valid where clause. Use e.g. to build clauses with nested AND's and OR's.

I cannot find the example to build clauses with nested AND’s and OR’s so I’ve done some digging through the ConditionInterface API Reference (https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Query%21ConditionInterface.php/interface/ConditionInterface/8.6.x)

I found a function conditionGroupFactory (https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Query%21ConditionInterface.php/function/ConditionInterface%3A%3AconditionGroupFactory/8.6.x)

The conditionGroupFactory function appears to be what I need, however I cannot find any implementation examples.

Could someone please explain the implementation of this function.

$result = $connection->select('inventory', 'inv');
$result->fields('inv', ['part_number']);
$result->condition('inv.visable', 1);
$result->conditionGroupFactory('AND')->condition('inv.part_number', $queryOne, 'IN');
$result->conditionGroupFactory('OR')->condition('inv.part_number', $queryTwo, 'IN');

Note: $queryOne, $queryTwo; Are of type SelectInterface.

My Objective SQL Statement:

SELECT inv.part_number
 FROM inventory AS inv
 WHERE inv.visable = 1
 AND (inv.part_number IN (SUBQUERY_1)
 OR  inv.part_number IN (SUBQUERY_2));
up vote 0 down vote accepted

I don't have the same structure of your table but i test it with the following statement it works:

SELECT *
 FROM `node__body` AS n_b
 WHERE n_b.`deleted` = 0
 AND (
 n_b.`revision_id` IN (32,6,17)
 OR  n_b.`langcode` IN ('fr')
 );

here is the code snippet for your case:

  $query = $connection->select('inventory', 'inv')
    ->fields('inv', ['part_number'])
    ->condition('inv.visable', '1', '=');
  //- Set Or Query group
  $group = $query
    ->orConditionGroup()
    ->condition('inv.part_number', [SUBQUERY_1], 'IN')
    ->condition('inv.part_number', [SUBQUERY_2], 'IN');
  //- Add Or group 
  $query->condition($group);
  $result = $query->execute()->fetchAll(PDO::FETCH_ASSOC);

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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