Cake php 3.8 - ORM Complex query?

Need Help
1

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'));
    }
2

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

3

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

4

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.

1 Like