# Cake php 3.8 - ORM Complex query?

**URL:** <https://discourse.cakephp.org/t/cake-php-3-8-orm-complex-query/6864>\
**Category:** Need Help\
**Created:** [November 8, 2019, 5:17pm UTC](https://discourse.cakephp.org/t/cake-php-3-8-orm-complex-query/6864 "2019-11-08T17:17:42Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![samuk190](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/samuk190/32/1593_2.png) [@samuk190](https://discourse.cakephp.org/u/samuk190)\
**Post date:** [November 8, 2019, 5:17pm UTC](https://discourse.cakephp.org/t/cake-php-3-8-orm-complex-query/6864/1 "2019-11-08T17:17:42Z")

</div>

I have table  
Purchases  
Sales\_Purchases  
Purchase\_Transfers  
Purchase\_Devolutions  
–\>  
" ‘volume\_comprado + volume\_transferido - volume\_devolvido - volume\_vendido \> 0’"  
= is getting data from a field of my table **Purchases** which is calculated to actual date

```
  $purchases = TableRegistry::get('Purchases')->find()
            ->where([
                'data_compra <=' => $date,
                'volume_comprado + volume_transferido - volume_devolvido - volume_vendido > 0'
            ])
            //certo
            ->matching('Plants')
            ->orderAsc('Plants.razao_social')
            ->orderAsc('Purchases.created')
            ->contain([
                'Distributors',
                'Plants'
            ]);

```

But Instead of getting the actual saved value from my table Purchase, I need get the data from the tables from a certain date.  
Sales Purchases to get volume\_vendido -\> created date and filter it to the $date(which is YYYY-MM-DD already)  
I also need the same for Purchases\_Transfers (which is volume\_transferido) and Purchase\_Devolutions (which is volume\_devolvido)

So I need a where condition which will filter the associations too, I do not see any example on documentation how i can do that with the cake ORM  
Complete function

```
  public function estoqueAbertura()
    {
        // 
        $data = $this->request->is('get') ? $this->request->query : $this->request->getData();
        // dump(date('Y-m-d'));
        // $date = new Date($data['data_referencia_ate']);
        $date = date("Y-m-d", strtotime($data['data_referencia_ate']));
        $dates = date("d/m/Y", strtotime($data['data_referencia_ate']));
        $datef = date("m/d/Y", strtotime($data['data_referencia_ate']));
    $purchases = TableRegistry::get('Purchases')->find()
                ->where([
                    'data_compra <=' => $date,
                    'volume_comprado + volume_transferido - volume_devolvido - volume_vendido > 0'
                ])
                //certo
                ->matching('Plants')
                ->orderAsc('Plants.razao_social')
                ->orderAsc('Purchases.created')
                ->contain([
                    'Distributors',
                    'Plants'
                ]);

            
        $rows = [];
        $contratos = [];
        foreach ($purchases as $purchase) {
            $contratos[] = $purchase->id;
            $distributor_id = $purchase->distributor_id;
            if (array_key_exists($distributor_id, $rows)) {
                $row = $rows[$distributor_id];
                $row['volume'] += $purchase->volume_comprado + $purchase->volume_transferido - $purchase->volume_devolvido - $purchase->volume_vendido;
            } else {
                $row['distribuidora'] = $purchase->distributor->razao_social;
                $row['volume'] = $purchase->volume_comprado + $purchase->volume_transferido - $purchase->volume_devolvido - $purchase->volume_vendido;
            }
            $rows[$distributor_id] = $row;
        }

        $this->set(compact('rows', 'purchases', 'contratos'));
    }
```

---

<div class="post-metadata">

**Author:** ![jimgwhit](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/jimgwhit/32/378_2.png) [@jimgwhit](https://discourse.cakephp.org/u/jimgwhit)\
**Post date:** [November 9, 2019, 3:25am UTC](https://discourse.cakephp.org/t/cake-php-3-8-orm-complex-query/6864/2 "2019-11-09T03:25:41Z")

</div>

Have a look here: [https://book.cakephp.org/3/en/orm/retrieving-data-and-resultsets.html#eager-loading-associations](https://book.cakephp.org/3/en/orm/retrieving-data-and-resultsets.html#eager-loading-associations)

---

<div class="post-metadata">

**Author:** ![samuk190](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/samuk190/32/1593_2.png) [@samuk190](https://discourse.cakephp.org/u/samuk190)\
**Post date:** [November 11, 2019, 12:20pm UTC](https://discourse.cakephp.org/t/cake-php-3-8-orm-complex-query/6864/3 "2019-11-11T12:20:06Z")

</div>

the problem is not loading associations but I need group them and join with the table so I can get correct values

---

<div class="post-metadata">

**Author:** ![Zuluru](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/zuluru/32/1230_2.png) [@Zuluru](https://discourse.cakephp.org/u/Zuluru)\
**Post date:** [November 11, 2019, 2:47pm UTC](https://discourse.cakephp.org/t/cake-php-3-8-orm-complex-query/6864/4 "2019-11-11T14:47:18Z")

</div>

That’s on that very same page that @jimgwhit linked to. If you’re not finding examples of what you want to do, it’s because you’re not looking hard enough. Try [this section](https://book.cakephp.org/3/en/orm/retrieving-data-and-resultsets.html#passing-conditions-to-contain).