How to get a result from ORM where contain != empty?

Need Help
1

I did this to get results from my table Receiveables

$receivables = TableRegistry::get('Receivables')->find('all')->contain([
    'SaleDetails' // didnt work ->where(['nota_fiscal !=' => '']),
    'SaleDetails.Sales',
    'SaleDetails.Sales.Purchases',
    'SaleDetails.Sales.Purchases.Brokers',
    'SaleDetails.Sales.Purchases.Plants',
    'SaleDetails.Presales',
    'SaleDetails.Presales.Clients',
    'SaleDetails.Presales.Clients.Sellers'
])->orderAsc('Receivables.data_vencimento')->limit(15);

but I want get results where nota_fiscal not empty from table SaleDetails

How I can Achieve that?

3

Did you want to get all the Receivables, and for each one only the SaleDetails where that condition is true, or do you want only the Receivables that have SaleDetails where that condition is true?

1 Like
4

@Zuluru well Inside sale Details I have a field named “nota_fiscal”. I want to get only the Receivables where SaleDetails.nota_fiscal is not empty.
Well tbh, there’s another problem… one, the database have too many results and exceeds the array max size.

How I also can deal with queries that exceeds array size limit?

and …

“do you want only the Receivables that have SaleDetails where that condition is true?” So basically I want this.

But also, for Knowledge, how you also would do “for each one only the SaleDetails where that condition is true”

5

What you want is to look at matching. The other option, which you don’t want here, is to put conditions on your contains.

As far as dealing with large data sets, check out the details on buffering result sets; by default, it’s doing this, and you want to turn that off.

1 Like
6

Your first solution with the matching worked pretty well…

But about buffering result set It didnt work, I guess its because I have TooMany Relations as I read on the docs and said I couldnt.
If I dont use ->limit(15) it gives that error…
Error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 8192 bytes)
File C:\wamp64\www\sistemarbl\vendor\cakephp\cakephp\src\Database\FieldTypeConverter.php
Line: 78

I did $receivables->enableBufferedResults(false);

it didnt work, it give me the same memory size error

7

Are you using ->toArray() somewhere, or are you just doing foreach ($receivables as $receivable)?

1 Like
8

I’m using `foreach ($receivables as $receivable) in the ctb view file

9

Probably two options left for you, then. You can process things in smaller batches, or you might be able to eliminate all of the containment in the original query (except for what’s required to do your conditions), turn off result buffering, and use loadInto to add the extra details on a record-by-record basis as you process them.

1 Like