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

**URL:** <https://discourse.laminas.dev/t/how-to-make-dynamic-programmatically-generated-nested-statement-using-the-zf3-sql-builder/639>\
**Category:** Components & MVC\
**Tags:** zend-db\
**Created:** [July 10, 2018, 12:18pm UTC](https://discourse.laminas.dev/t/how-to-make-dynamic-programmatically-generated-nested-statement-using-the-zf3-sql-builder/639 "2018-07-10T12:18:17Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![kingharrison](https://yyz2.discourse-cdn.com/flex032/user_avatar/discourse.laminas.dev/kingharrison/32/230_2.png) [@kingharrison](https://discourse.laminas.dev/u/kingharrison)\
**Post date:** [July 10, 2018, 12:18pm UTC](https://discourse.laminas.dev/t/how-to-make-dynamic-programmatically-generated-nested-statement-using-the-zf3-sql-builder/639/1 "2018-07-10T12:18:17Z")

</div>

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

here is what I tried:

```auto
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.

---

<div class="post-metadata">

**Author:** ![froschdesign](https://yyz2.discourse-cdn.com/flex032/user_avatar/discourse.laminas.dev/froschdesign/32/38_2.png) [@froschdesign](https://discourse.laminas.dev/u/froschdesign)\
**Post date:** [July 10, 2018, 12:31pm UTC](https://discourse.laminas.dev/t/how-to-make-dynamic-programmatically-generated-nested-statement-using-the-zf3-sql-builder/639/2 "2018-07-10T12:31:27Z")

</div>

Please format your code example for better reading:

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

````

Thanks!

---

<div class="post-metadata">

**Author:** ![kingharrison](https://yyz2.discourse-cdn.com/flex032/user_avatar/discourse.laminas.dev/kingharrison/32/230_2.png) [@kingharrison](https://discourse.laminas.dev/u/kingharrison)\
**Post date:** [July 10, 2018, 12:35pm UTC](https://discourse.laminas.dev/t/how-to-make-dynamic-programmatically-generated-nested-statement-using-the-zf3-sql-builder/639/3 "2018-07-10T12:35:05Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![froschdesign](https://yyz2.discourse-cdn.com/flex032/user_avatar/discourse.laminas.dev/froschdesign/32/38_2.png) [@froschdesign](https://discourse.laminas.dev/u/froschdesign)\
**Post date:** [July 10, 2018, 12:51pm UTC](https://discourse.laminas.dev/t/how-to-make-dynamic-programmatically-generated-nested-statement-using-the-zf3-sql-builder/639/4 "2018-07-10T12:51:39Z")

</div>

In your `TableGateway` class:

```php
$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')'

```

---

<div class="post-metadata">

**Author:** ![xerkus](https://yyz2.discourse-cdn.com/flex032/user_avatar/discourse.laminas.dev/xerkus/32/22_2.png) [@xerkus](https://discourse.laminas.dev/u/xerkus)\
**Post date:** [July 11, 2018, 2:21am UTC](https://discourse.laminas.dev/t/how-to-make-dynamic-programmatically-generated-nested-statement-using-the-zf3-sql-builder/639/5 "2018-07-11T02:21:42Z")

</div>

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

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

```

Is equal to this:

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

```

or this:

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

```

---

<div class="post-metadata">

**Author:** ![froschdesign](https://yyz2.discourse-cdn.com/flex032/user_avatar/discourse.laminas.dev/froschdesign/32/38_2.png) [@froschdesign](https://discourse.laminas.dev/u/froschdesign)\
**Post date:** [July 11, 2018, 3:23am UTC](https://discourse.laminas.dev/t/how-to-make-dynamic-programmatically-generated-nested-statement-using-the-zf3-sql-builder/639/6 "2018-07-11T03:23:04Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![cadu](https://yyz2.discourse-cdn.com/flex032/user_avatar/discourse.laminas.dev/cadu/32/279_2.png) [@cadu](https://discourse.laminas.dev/u/cadu)\
**Post date:** [July 12, 2018, 4:39pm UTC](https://discourse.laminas.dev/t/how-to-make-dynamic-programmatically-generated-nested-statement-using-the-zf3-sql-builder/639/7 "2018-07-12T16:39:52Z")

</div>

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

```php
$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

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

```

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

---

<div class="post-metadata">

**Author:** ![kingharrison](https://yyz2.discourse-cdn.com/flex032/user_avatar/discourse.laminas.dev/kingharrison/32/230_2.png) [@kingharrison](https://discourse.laminas.dev/u/kingharrison)\
**Post date:** [July 12, 2018, 6:19pm UTC](https://discourse.laminas.dev/t/how-to-make-dynamic-programmatically-generated-nested-statement-using-the-zf3-sql-builder/639/8 "2018-07-12T18:19:13Z")

</div>

This was exactly it! Thank you!