Conditions in JOINed tables shows error CakePHP

Question

I have two tables employee_personals where all the personal record of the employee is stored and telephone_bills where the telephone bills paid to a particular employee is stored for each month. Now in my employeePersonalsController.php I have a function called api_show_employees() which is similar to below :

function api_show_employees() {
      //$this->autoRender = false;
      //Configure::write("debug",0);

      $office_id = '';
      $cond = '';

      if(isset($_GET['office_id']) && trim($_GET['office_id']) != '') {
        $office_id = $_GET['office_id'];
        $cond['EmployeePersonal.office_id'] = $office_id;
      }


      if(isset($_GET['telephoneBillTo']) && isset($_GET['telephoneBillFrom']) ) {
        if($_GET['telephoneBillTo'] != '' && $_GET['telephoneBillFrom'] != '') {
          $cond['TelephoneBill.bill_from'] = $_GET['telephoneBillFrom'];
          $cond['TelephoneBill.bill_to'] = $_GET['telephoneBillTo'];
        }
      }


      $order = 'EmployeePersonal.name';
     // $employee = $this->EmployeePersonal->find('all');
      $employee = $this->EmployeePersonal->find('all',array('order' => $order,'conditions'=>$cond));
      //return json_encode($employee);
    }

This functions basically finds all the employees who paid bills in the given period. But I am getting an error

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'TelephoneBill.bill_from' in 'where clause'

Models : EmployeePersonal.php:

var $hasMany = array(
  'TelephoneBill' => array(
         'className' => 'TelephoneBill',

     )
);

TelephoneBill.php

public $name = 'TelephoneBill';
var $hasMany = array('EmployeePersonal');

NB: If I skip the bill_from and bill_to conditions, I am getting the results , with TelephoneBill array !

score 1 · Accepted Answer · answered Apr 10 '14 at 13:58

TLDR: use Joins instead.

Details/Notes:

1) it looks like you're using recursive. Don't do that. Use Containable instead.

2) You can't limit the parent model based on conditions against data from a contained/recursive-included table - instead, use Joins.

2b) Or, you could query from the other direction, and query your TelephoneBill with conditions, then contain the EmployeePersonal.

Conditions in JOINed tables shows error CakePHP

1 Answers1

Linked