Query to find where associated model (has many) is empty in cakephp

Question

I have a model named Application. And Application is associated to has_many model named Location.

Application has many Location

In my Application query:

$this->Application->find('all', array('conditions' => 'Application.status' => 'accepted'));

I'm finding applications where status is accepted.

Next thing that I would like to achieve is to find Application records where associated Location is empty/null or in other words where count of Location records is 0.

I tried to make join query like this:

 $join_query = array(
            'table' => 'locations',
            'alias' => 'Location',
            'type' => 'INNER',
            'conditions' => array(
                'Location.application_id = Application.id',
                'OR' => array(
                    array('Location.id' => NULL)
                )
            )   
        );

But seems like it's just querying Application records that do have associated Location records.

Thanks in advanced if you guys have any idea(s).

Can you explain your question in a bit more precise way please? — kidz, Jun 27 '14 at 15:43
@kidz The question is pretty clear if you ask me, she want's to find all applications that don't have any associated locations. — ndm, Jun 27 '14 at 15:47

score 2 · Accepted Answer · answered Jun 27 '14 at 15:53

You need to use a left join, not an inner join. Inner join will get only those results that have a row in both of the tables you are joining, where you want only results where there is only a row in the left table. Left joins will get all the results in the left table, regardless if there's a row associated with it in the right table. Then add a condition after the join is complete, to only select those joined results where Location.id is null.

$this->Application->find('all',
    array(
        'conditions' => array('Location.id' => null),
        'joins' => array(
             array(
                'table' => 'locations',
                'alias' => 'Location',
                'type' => 'LEFT',
                'conditions' => array('Location.application_id = Application.id')
             ),
         ),
    )
);

or you could do `$this->Application->find()->notMatching('Locations')->all();` — waspinator, Dec 30 '18 at 03:48
@waspinator brilliant, and the actual way to go when using defined associations — Antoniossss, Jan 06 '23 at 08:58

score 0 · Answer 2 · edited May 23 '17 at 12:30

Your query says "find any application and its location with application_id = id, AND (1 OR where location.id = null)", so that will match any application that has location.

What I'd do is to leave joins and just use containable and counts. With plain sql I'd use a left join and count the Locations, like in this example. But cake doesn't behave well with not named columns, like "COUNT(*) AS num_locations", so I tend to avoid that.

I'd transform your query to a containtable one

$apps = this->Application->find('all', array('contains'=>'Location'));
foreach($apps as $app) {
    if (count($app['Location']) <= 0)
        //delete record
}

You could also implement a counterCache, and keep in a BD column the number of locations per application, so the query can be a simple find like

$this->Application->find('all', array('conditions'=>array('location_count'=>0)));

Ooooor, you could add a virtual field with "SUM(*) as num_locations" and then use your join with "left outter join" and compare "num_locations = 0" on the conditions.

Those are the options that comes to mind. Personally I'd use the first one if the query will be a one time/not very used one. Probably put it in the Application model like

 public function findAppsWithNoLocations() {
    $apps = this->Application->find('all', array('contains'=>'Location'));
    foreach($apps as $app) {
        if (count($app['Location']) <= 0)
            //delete record
    }
 }

But the other two options would be better if the sum of locations per app is going to be a recurrent query you'll search for.

EDIT

And of course Kai's answer options that does what you want xD. This tendency to complicate things will be the end of me... Well, will leave the answer here to show a reference to other convoluted options (specifically counterCache if you'll need to count the relations a lot of times).

I would generally discourage the usage of the "_select all and reduce with PHP_" solution, no matter if the query isn't expensive, this is simply something that should be up to the DBMS! If you ask me, using a properly indexed counter cache is the best option, speaking of performance, and other than that a proper join like shown by @Kai or a [**subquery**](http://stackoverflow.com/a/544156/1392379) for a `NOT IN/EXISTS` condition should be the way to go. — ndm, Jun 27 '14 at 16:13
@ndm: agreed. I noticed Kai's answer after I posted mine, but decided to leave it up to explain other solutions (agreeing some may not be the best for most cases, but is still an option). I consider countercache the best option, but some people may not want to add a counter column just for one single use in a single query, for that single query I'd go with Kai's, as mention in my answer. — Nunser, Jun 27 '14 at 16:24

score 0 · Answer 3 · answered May 12 '15 at 13:06

i know this is already some time ago. i could manage it this way:

public function getEmpty($assoc) {

    foreach($this->find('all') as $c){

        if(empty($c[$assoc])) $return[] = $c;

    }
    return $return;
}

now i got all entries that have an empty associated data.

in my controller i call the function like this:

$ce = $this->Company->getEmpty('CompaniesUsers');

companies Users is the Empty Associated model i want to check.

Query to find where associated model (has many) is empty in cakephp

3 Answers3