My Query is without the LeftJoin that i needed. Why?

Need Help
1

Hello, I wrote a small function to request City names but cakephp does not do the leftjoin with the correct table…

Function in CountriesController:

public function getAjaxcityList()
{
$this->autoRender = false;

    if ($this->request->is('ajax')) {
        

        $this->Authorization->skipAuthorization();
        
        $name2 = $this->request->getQuery('term2');
        
        $results = $this->Countries->find()
        ->contain(['Cities'])
        ->where(['id' => 19])
        ->where(['Cities.name LIKE' => $name2 . '%'])
        ->limit(10);
        
        debug($results);
         debug($results->toArray());
        debug($results->sql());

        $resultsArr = [];
        
        foreach ($results as $result) {
            $resultsArr[] =['label' => $result->cities.name, 'value' => $result->cities.id];
        }
        
        return $this->response->withType('application/json')->withStringBody(json_encode($resultsArr));
    }
    
}

CountriesTable:
$this->hasMany(‘Cities’, [
‘foreignKey’ => ‘country_id’,
]);

Error Message:

object(Cake\ORM\Query) id:0 {
‘(help)’ => ‘This is a Query object, to get the results execute or iterate it.’
‘sql’ => ‘SELECT Countries.id AS Countries__id, Countries.name AS Countries__name FROM countries Countries WHERE (id = :c0 AND Cities.name like :c1) LIMIT 10’

‘defaultTypes’ => [
‘Countries__id’ => ‘integer’,
‘Countries.id’ => ‘integer’,
‘id’ => ‘integer’,
‘Countries__name’ => ‘char’,
‘Countries.name’ => ‘char’,
‘name’ => ‘char’,
‘Cities__id’ => ‘integer’,
‘Cities.id’ => ‘integer’,
‘Cities__country_id’ => ‘integer’,
‘Cities.country_id’ => ‘integer’,
‘country_id’ => ‘integer’,
‘Cities__name’ => ‘string’,
‘Cities.name’ => ‘string’,
]

{ “message”: “SQLSTATE[42S22]: Column not found: 1054 Unknown column \u0027Cities.name\u0027 in \u0027where clause\u0027”, “url”: “/countries/get-ajaxcity-list?term=hamburg”, “code”: 500, “file”: “/var/www/html//vendor/cakephp/cakephp/src/Database/Statement/MysqlStatement.php”, “line”: 39 }

What am i doing wrong here?

2

By default, a hasMany association will be done with a second query, not a join. Options for you include:

Flip the query so you’re getting cities and their matching countries. Cities should be a belongsTo relation to Countries, which would do a join.

$results = $this->Countries->Cities->find()
    ->contain(['Countries'])
    ->where(['Countries.id' => 19])
    ->where(['Cities.name LIKE' => $name2 . '%'])
    ->limit(10);

Put the condition on the containment:

$results = $this->Countries->find()
    ->contain(['Cities' => [
        'queryBuilder' => function(\Cake\ORM\Query $query) use ($name2) {
            return $query->where(['Cities.name LIKE' => $name2 . '%'])
                ->limit(10);
        }
    ]])
    ->where(['id' => 19]);

Force the join (I don’t generally do this, so the syntax might not be quite right):

$results = $this->Countries->find()
    ->leftJoinWith('Cities')
    ->contain(['Cities'])
    ->where(['Countries.id' => 19])
    ->where(['Cities.name LIKE' => $name2 . '%'])
    ->limit(10);

Note that each of these choices has some impact on what the resulting data structure will look like. I’d probably go with the first one, as that best resembles what you actually want: you want to find matching cities.

3

thank you… it works! but now i am wondering how i can limit the results…

$results = $this->Group->Subgroup->find()
->contain([‘Group’])
->where([‘Subgroup.id’ => 19])
->limit(10);

because this is not working…

4

If this is still about cities and countries, can you keep using those names in your example of what’s not working? And tell us specifically what “not working” means.

5

i talk about the ->limit(10);

6

I understand that. I don’t understand the context of it any more, as you’ve changed the model names. And I don’t know what “not working” means. Are you getting no results? More than 10 results? Something is being limited to 10 results, but it’s not the right thing?