Conditions on associated model on CakePHP 3.x query

Need Help
1

My codes works perfectly and retrieves data but When I add more conditions on associated models it shows error.

  if ($property == 'apartment') {
            $result = $this->Buildings->find('all')
                ->contain([
                    'Dohss','BuildingPlotInfo.Plots','Apartments.Owners','Owners'
                ])
                ->where(['Dohss.id' => $dohs_id,'Buildings.is_apartment' => 1,'Buildings.status' => 1,'Apartments.Owners.status'=>1]);

            $data_ap = 1;
            $this->set('data_ap', $data_ap);
            $this->set('result', $this->paginate($result));
            $this->set('_serialize', ['result']);

        }

In my code last condition on where clause is ‘Apartments.Owners.status’ and Cake Shows Error for this condition

2

Hey! What kind of error do you get?

3

checkout this http://book.cakephp.org/3.0/en/orm/retrieving-data-and-resultsets.html#filtering-by-associated-data

4

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘Apartments.Owners.status’ in ‘where clause’

Cause In query this cloumn is not present but It has fetched all the data associated with that model and I’ve mentioned them inside contain.

SELECT Buildings.id AS Buildings__id, Buildings.dohs_id AS Buildings__dohs_id, Buildings.road_number AS Buildings__road_number, Buildings.build_type AS Buildings__build_type, Buildings.title_en AS Buildings__title_en, Buildings.title_bn AS Buildings__title_bn, Buildings.build_site_area AS Buildings__build_site_area, Buildings.build_site_north AS Buildings__build_site_north, Buildings.build_site_south AS Buildings__build_site_south, Buildings.build_site_east AS Buildings__build_site_east, Buildings.build_site_west AS Buildings__build_site_west, Buildings.build_site_road_details AS Buildings__build_site_road_details, Buildings.build_site_soil_type AS Buildings__build_site_soil_type, Buildings.build_purpose AS Buildings__build_purpose, Buildings.roof_type AS Buildings__roof_type, Buildings.estimate_cost AS Buildings__estimate_cost, Buildings.actual_cost AS Buildings__actual_cost, Buildings.plan_approve_date AS Buildings__plan_approve_date, Buildings.work_start_date AS Buildings__work_start_date, Buildings.work_finish_date AS Buildings__work_finish_date, Buildings.floor_number AS Buildings__floor_number, Buildings.building_details AS Buildings__building_details, Buildings.is_apartment AS Buildings__is_apartment, Buildings.is_house AS Buildings__is_house, Buildings.is_garage_available AS Buildings__is_garage_available, Buildings.is_legal_info AS Buildings__is_legal_info, Buildings.apartment_number AS Buildings__apartment_number, Buildings.septic_tank_info AS Buildings__septic_tank_info, Buildings.waste_cleaning_details AS Buildings__waste_cleaning_details, Buildings.build_status AS Buildings__build_status, Buildings.developer_id AS Buildings__developer_id, Buildings.status AS Buildings__status, Buildings.create_time AS Buildings__create_time, Buildings.update_time AS Buildings__update_time, Dohss.id AS Dohss__id, Dohss.title_en AS Dohss__title_en, Dohss.title_bn AS Dohss__title_bn, Dohss.total_area AS Dohss__total_area, Dohss.total_plot_number AS Dohss__total_plot_number, Dohss.total_building_number AS Dohss__total_building_number, Dohss.total_house_number AS Dohss__total_house_number, Dohss.total_apartment_number AS Dohss__total_apartment_number, Dohss.total_market_number AS Dohss__total_market_number, Dohss.total_shop_number AS Dohss__total_shop_number, Dohss.status AS Dohss__status, Dohss.map_file AS Dohss__map_file, Dohss.create_time AS Dohss__create_time, Dohss.update_time AS Dohss__update_time FROM buildings Buildings INNER JOIN dohss Dohss ON Dohss.id = (Buildings.dohs_id) WHERE (Dohss.id = :c0 AND Buildings.is_apartment = :c1 AND Buildings.status = :c2 AND Apartments.Owners.status = :c3) LIMIT 100 OFFSET 0

5

Try this:

 if ($property == 'apartment') {
    $result = $this->Buildings->find('all')
        ->contain([
            'Dohss' => function ($q) use ($dohs_id) {
                return $q->where(['Dohss.id' => $dohs_id]);
            },
            'BuildingPlotInfo' => function ($q) {
                return $q->contain(['Plots']);
            },
            'Apartments' => function ($q) {
                return $q->contain([
                    'Owners' => function ($q) {
                        return $q->where(['Owners.status' => 1]);
                    },
                ]);
            },
            'Owners'
        ])
        ->where(['Buildings.is_apartment' => 1, 'Buildings.status' => 1]);

    $data_ap = 1;
    $this->set('data_ap', $data_ap);
    $this->set('result', $this->paginate($result));
    $this->set('_serialize', ['result']);

}
6

Thanks mate ! It worked.I wasn’t that much familiar with closure of CakePHP 3.x.

I appreciate your help!

7 
   $this->MainEntities->find()
   	->contain(['AssocEntities'=>['conditions'=>['AssocEntities.field'=>'some value']]])
   	->where(['MainEntities'=>'some other value']);
1 Like
8

Thanks for this solution. It also works on cakephp 4.