Query with 'contain' does not include the associated objects in the result

Need Help
1

Hi,

I have a simple table with earnings and outstanding payments and the customer_id of the client who owes that payment.

For managing the outstanding payments, I want a View that lists all outstanding payments and the fullname of the customer.

In the controller I created a query that selects the earnings records with outstanding payments:

$query = $this->Earnings->find();
$query->select([‘id’, ‘date’, ‘customer_id’, ‘service’, ‘outstanding’, ‘payment_id’]);
$query->where([‘outstanding IS NOT’ => 0]);

BTW: I was wondering if there is any better way to filter the records just by finding all where outstanding > 0?

Anyway, I then store the result in a variable which I pass to the View:

$outstandings = $this->paginate($query, [‘contain’ => [‘Customers’, ‘Payments’]]);
$this->set(‘outstandings’, $outstandings);

Although I included ‘Customers’ in ‘contain’, I don’t get any associated customer records. The resultset just holds the customer_id, but no further Customer object. I am therefore not able to access the Customer fields like its fullname.

what’s wrong? or what would be the proper way?

Thanks,
Marcus.

2

I believe that IS NOT is only for use with NULL, you should be using 'outstanding >' => 0.

Your contained records are not showing up, because you have used select to tell it the fields you want. I personally never bother with this, I think that any performance benefit gained by limiting the fields pulled over from the database is going to be minimal, compared to the time I’ll waste when some calculation or display somewhere changes to include a field that is included in most queries but not all of them and I have to track down what’s going wrong.

But if you insist on using it (there are, after all, times when it’s required), then you can add ->enableAutoFields(true) to your query, and that will pull in the associated records in their entirety, or I think you can use queryBuilder arguments in your containment if you want to further limit what fields are selected from those records.

3

Hi,

Thanks for the hint with the ->enableAutoFields(true) - this really did the trick. I also removed the select statement from the query. Honestly, there was no real intention to limit the columns, I was just trying to find out how to create a proper query at all and followed the documentation.

Another obstacle was, that the associated objects are spelled lowercase in the View - which took me some time to find out.

But now the View works as intended!
Thanks a lot,
Marcus.

4

If you don’t have a select clause any more, you don’t need enableAutoFields. And yes, associated objects are always spelled with lowercase. You can use debug($whatver) to look at objects whenever you run into situations where you’re not sure exactly what structure the data has.