Trouble with hasMany and find

Question

I'm working around a month with CakePHP 2.0, and today I found some problems with the hasMany relationships on find().

The relationships for model Usuario (user) is as follows:

$this->Usuario->bindModel(array(
'hasMany' => array(
    'CambioCorreo' => array(
        'className' => 'CambioCorreo',
        'foreignKey' => 'id_usuario',
    ),
    'Llave' => array(
        'className' => 'Llave',
        'foreignKey' => 'id_usuario',
    ))));

But when I try to use this find:

$u = $this->Usuario->find('all',array('conditions' => array('Llave.llave' => $llave,'Llave.id_tipo_llave' => 3,'Llave.fecha_creacion = CambioCorreo.fecha')));

, I got this:

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

Oddly enough, before doing the hasMany relationship, I did a hasOne as a mistake, and it worked pretty well. But now, I changed the bindModel from "hasOne" to "hasMany", and the SQL column error arises.

Anyone knows where I'm making the mistake?

Thanks in advance

jeremyharris jeremyharris · Accepted Answer · 2012-04-23T18:02:26

hasMany relationships are not LEFT joined in the same SQL statement, and therefore you cannot use conditions on them in the same statement. The same goes for HABTM.

You can, however, add conditions to what hasMany items are pulled for the Usuario search.

$this->Usuario->find('all', array(
  'contain' => array(
    'Llave' => array(
      'conditions' => array(
        'Llave.llave' => $llave
      )
     )
   )
));

This will find all Usuario records and bring in all Llave records where llave => $llave.

The process is something like this: Cake creates a SQL statement to find all Usuario records, then separate SQL statements for each Usuario records to pull their respective Llave records, with the conditions stated above. It then joins them into a single array for you.

To find Usuario records based on conditions for Llave, you'll need to either bind a hasOne relationship so you can use conditions to filter, or search the Llave records first and contain their Usuario records, something like:

$this->Usuario->Llave->find('all', array(
  'conditions' => array(
    'Llave.llave' => $llave
  ),
  'contain' => array(
    'Usuario'
  )
));

This searches the Llave records, then joins in their respective users. However, you may end up in duplicate Usuario records because they can have more than one Llave.

Trouble with hasMany and find

1 Answers