How to specify AND, OR with dynamic query

Question

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));

berramou · Accepted Answer · 2018-10-14 13:52:05Z

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);

Stack Exchange Network

How to specify AND, OR with dynamic query

1 Answer 1

Hot Network Questions

How to specify AND, OR with dynamic query

1 Answer 1

Related

Hot Network Questions