How To Make Dynamic Programmatically Generated Nested Statement Using the ZF3 SQL Builder

Questions Components & MVC
1

Is there an example anywhere of a a dynamic programmatically generated nested statement using the ZF3 SQL builder?

here is what I tried:

if ($true) {
            $where = new Where();
            $firstone = false;
            foreach ($users as $user) {
                if ($firstone) {
                    $where->OR->equalTo('BUYER', $user);
                }
                else {
                    $where->NEST->equalTo('BUYER', $user);
                }
                $firstone  = true;
            }
            $where->UNNEST;
          array_push($data, $where);
        }
$resultSetPrototype = new ResultSet ();
        $resultSetPrototype->setArrayObjectPrototype(new SUPLIER ());
        // create a new pagination adapter object
        $paginatorAdapter = new DbSelect (
        // our configured select object
            $select->where($data)->order("NAME ASC"),
            // the adapter to run it against
            $this->tableGateway->getAdapter(),
            // the result set to hydrate
            $resultSetPrototype);
        $paginator = new Paginator ($paginatorAdapter);
        return $paginator;

That did not work. It kept saying UNNESTED.

2

Please format your code example for better reading:

```php
var_dump('Example');
```

Thanks!

3

OK, I submitted an edit to make more readable. Thank you for telling me how (new to discourse).

Let me know if there are more pieces I need to add.

4

In your TableGateway class:

$users = [
    'foo',
    'bar',
    'baz',
];

$select = $this->getSql()->select();
$predicateSet = $select->where->nest();
foreach ($users as $user) {
    $predicateSet->or->equalTo('BUYER', $user);
}
$predicateSet->unnest();

var_dump($this->getSql()->buildSqlString($select));
// SELECT `example`.* FROM `example` WHERE (`BUYER` = 'foo' OR `BUYER` = 'bar' OR `BUYER` = 'baz')'
1 Like
5

You need to use fluent interface. Nest returns new predicate, by using unnest on that predicate, you will get parent predicate.
This:

$where->nest
           ->equalTo('table2.column2', 2)
           ->or
           ->equalTo('table2.column3', 3)
       ->unnest
       ->and
       ->equalTo('table1.column1', 1);

Is equal to this:

$where->nest
           ->equalTo('table2.column2', 2)
           ->or
           ->equalTo('table2.column3', 3);
$where->and
       ->equalTo('table1.column1', 1);

or this:

$nested = $where->nest;
$nested->equalTo('table2.column2', 2)
$where->and
       ->equalTo('table1.column1', 1);
$nested->or
       ->equalTo('table2.column3', 3);
6

The same example can be found in the documentation: https://docs.zendframework.com/zend-db/sql/#where-having

7

@kingharrison, I think you’re looking for something like this:

$users = ['foo', 'bar', 'baz'];
$where = new Where();
$where->nest()->addPredicates(array_map(function ($user) {
    return (new Where())->equalTo('BUYER', $user);
}, $users), Where::OP_OR)->unnest();

Generates:
WHERE ((BUYER = ?) OR (BUYER = ?) OR (BUYER = ?))

or

$where->nest()->addPredicates(function ($where) use ($users) {
    foreach ($users as $user) {
        $where->or->equalTo('BUYER', $user);
    }
})->unnest();

Generates:
WHERE (BUYER = ? OR BUYER = ? OR BUYER = ?)

2 Likes
8

This was exactly it! Thank you!