CODEWITHSUNDEEP
phpcakephpcakephp-3.0
aniga
aniga

Reputation: 117

CakePHP how to set conditions on contain table

I have 2 tables, Drivers and Cars. The Drivers table hasMany association with cars. I created a controller called SearchController. In search controller I load the Driver model using $this->loadModel('Drivers');

When I enter a search, I want it to only return cars that are automatic and the drivers details but it is returning all values including cars which are not automatic.

$drivers = $this->paginate($this->Drivers, [
        'contain' => ['Cars']], [
        'conditions' => ['Cars.transmission' => 'Automatic'] ] );  

 $this->set(compact('drivers')); 
 $this->set('_serialize', ['drivers']);

Your help would be highly appreciated.

---EDIT----

The statement I want to achieve is :

SELECT * FROM drivers, cars 
         WHERE drivers.id = cars.driver_id 
         AND cars.transmission = 'Automatic'

Upvotes: 2

Views: 5964

Answers (2)

Szymon
Szymon

Reputation: 1415

You should write your contain like this:

'contain' => [
    'Cars' => function($query){
        return $query->where(['transmission' => 'Automatic']);
    }
]

For more options, please refer to CakePHP cookbook: Passing Conditions to Contain

EDIT:

To select only Drivers that have Cars with "Automatic" transmission, and to select list of those cars for each driver, you need to join these two tables together. Result code should look like this:

$query = $this->Drivers->find()
            ->matching("Cars", function($q){
                return $q->where(["Cars.transmission" => "Automatic"]);
            })
            ->contain([
                "Cars" => function ($q) {
                    return $q->where(["transmission" => "Automatic"]);
                }
            ]);

$result = $this->paginate($query);

matching() used here will join tables drivers and cars, and set condition accordingly. contain() part of query is still the same.

For more options, please visit these docs:

Pagination

Retrieving Data & Results Sets

Upvotes: 4

Rayann Nayran
Rayann Nayran

Reputation: 1135

Additional associations can be loaded to the paginated table by using the contain parameter:

$this->paginate['contain'] = [
    'Cars' => function (\Cake\ORM\Query $query) {
        return $query->where(['Cars.transmission' => 'Automatic']);
    }
];

$drivers = $this->paginate($this->Drivers);  

$this->set(compact('drivers')); 
$this->set('_serialize', ['drivers']);

See also

Upvotes: 3

Related Questions