Cakephp 3: how to restrict a query by its contained model

Question

I have three associated models, Clients has many Preinvoices and belongs to one Group. I would like to limit my query on Clients to only those who have associated Preinvoices (after applying conditions, see below).

Here is the outline of what I am doing (I think you can ignore the Groups part).

$clients = $this->Clients->find()
    ->order(['Groups.name' => 'ASC'])
    ->contain('Groups');
  $clientPreinvoiceConditions = [
    'Preinvoices.last_processed <' => $cutOffDateTime,
  ];
  $clients->contain('Preinvoices', function (\Cake\ORM\Query $q) use ($clientPreinvoiceConditions) {
    return $q
      ->where($clientPreinvoiceConditions);
    });

So far so good – I am getting a full client list with Preinvoices matching the conditions. However it would be much better only getting Clients that in fact have any Preinvoices.

I would like to do something like:

$clients->having(['COUNT(Preinvoices.id) >' => 0]);

But this doesn't seem quite right. I get the error:

Unknown column 'Preinvoices.id' in 'having clause'

This, by the way, is despite the fact that I do get a Preinvoices.id in the result.

I also tried to first select the count as its own column so that I could reference this in having(), but no success:

$clients->select([
        'preinvoiceCount' => 'COUNT(Preinvoices.id)'
      ]);

This gives me the error:

Unknown column 'Preinvoices.id' in 'field list'

Complex queries in Cake always seem to stump me. Ideas?

Check **https://stackoverflow.com/questions/26799094/how-to-filter-by-conditions-for-associated-models**. — ndm, Mar 30 '21 at 21:25

score 0 · Answer 1 · answered Mar 30 '21 at 22:10

As pointed out by ndm's comment, the problem is very similar to the one solved here: How to filter by conditions for associated models?

In my particular case it means changing my 'contain' to a 'matching', AND also adding a normal 'contain', like this:

  $clients->matching('Preinvoices', function (\Cake\ORM\Query $q) use ($clientPreinvoiceConditions) {
    return $q
      ->where($clientPreinvoiceConditions);
    });
  $clients->contain('Preinvoices');

Cakephp 3: how to restrict a query by its contained model

1 Answers1