1

On cakephp 2.1, I have two tables: qca belongs to employee via field emp_number on both tables.

qca model belongsTo : (pleae note foreignKey)

public $actsAs = array('Containable');

var $belongsTo = array('Dir', 
     'Employee' => array(
     'className' => 'Employee',
     'foreignKey' => 'emp_number')
 );

employee model:

public $actsAs = array('Containable');

On my controller's find, i use 'contain' to retrieve employee info based on emp_number from qca table.

$hoursvalues = $this->Qca->find('all', array('conditions' => $conditions, 
            'fields' => array('Qca.emp_number', 'Sum(CASE WHEN Qca.qca_tipcode = 1 THEN 1 END) AS Qca__comps', 'Sum(qca_end - qca_start) as Qca__production', 'Sum(Qca.qca_durend) as Qca__idle'),
            'contain' => array(
                'Employee' => array(
                    'fields' => array('emp_number', 'emp_ape_pat', 'emp_ape_mat', 'emp_ape_mat'))),
            'group' => array('Qca.emp_number'),
                ));

However, the executed sql sentence shows:

LEFT JOIN `devopm`.`employees` AS `Employee` ON (`Qca`.`emp_number` = `Employee`.`id`)

Whereas Employee.id should be Employee.emp_number

This is the full sql sentence:

SELECT `Qca`.`emp_number`, Sum(CASE WHEN Qca.qca_tipcode = 1 THEN 1 END) AS Qca__comps, Sum(qca_end - qca_start) as Qca__production, Sum(`Qca`.`qca_durend`) as Qca__idle, `Employee`.`emp_number`, `Employee`.`emp_ape_pat`, `Employee`.`emp_ape_mat`, `Employee`.`id` FROM `devopm`.`qcas` AS `Qca` LEFT JOIN `devopm`.`employees` AS `Employee` ON (`Qca`.`emp_number` = `Employee`.`id`) WHERE `Qca`.`dir_id` = 63 AND FROM_UNIXTIME(`Qca`.`qca_start`, '%Y-%m-%d') >= '2012-07-18' AND FROM_UNIXTIME(`Qca`.`qca_start`, '%Y-%m-%d') <= '2012-07-18' GROUP BY `Qca`.`emp_number`

This results on null values returned for Employee:

array(
(int) 0 => array(
    'Qca' => array(
        'emp_number' => 'id3108',
        'comps' => '2',
        'production' => '7784',
        'idle' => '529'
    ),
    'Employee' => array(
        'emp_ape_pat' => null,
        'emp_ape_mat' => null,
        'id' => null
    )
),

Note: I have other instance of 'contain' working (one with default id = tableName.id). I'm wondering if the foreignKey on belongsTo ('foreignKey' => 'emp_number') is just not good for 'contain' to work?

Can you help?

Thank you so much.

(I found a workaround but slows down the query a great deal (it duplicates the left join and takes forever)

$joins = array(
 array('table' => 'publication_numerations',
   'alias' => 'PublicationNumeration',
   'type' => 'LEFT',
   'conditions' => array(
      'Publication.id = PublicationNumeration.publication_id',
   )
 )
);

$this->Publication->find('all', array('joins' => $joins));
Carlos Garcia
  • 359
  • 1
  • 5
  • 29
  • try `$this->Qca->Employee->primaryKey = 'emp_number';` before your find – tigrang Jul 21 '12 at 00:08
  • Great! it works, Thanks a lot! How could I have ever thought of that, I wonder. Many thanks. – Carlos Garcia Jul 21 '12 at 04:12
  • Why don't you just have `Qca.employee_id`, though? – tigrang Jul 21 '12 at 04:19
  • Main reason is that employees autoincrement id is not good for identifying employees; all employees have an id assigned already, which is being stored as emp_number. qca records belong to employees via emp_number. Actually I was just about to write back because `$this->Qca->Employee->primaryKey = 'emp_number';` seems to take a lot of time and my server hangs up. Is it maybe that the id is composite? e.g. 'id1520' I'm considering tryng a) remove 'id' letters and use only numbers. b) use employee.id as the employee identifier (change of strategy). Thanks a lot. Carlos – Carlos Garcia Jul 23 '12 at 21:52
  • Shouldn't I somehow "reverse" `$this->Qca->Employee->primaryKey = 'emp_number';' after the find? Because it seems to run fine only the first time. – Carlos Garcia Jul 23 '12 at 22:34

0 Answers0