filter conditions from an association

Question

I have a search function, which works great for staff, so I can search by name. Now, I want filter staffs by staffgroup.groupname, but unfortunatelly i get this error:

Column not found: 1054 Unknown column 'staffgroups.groupname' in 'where clause'

I'm having the following tablelayout

• staffs (a person can belong to many groups)
• staff_staffgroups (HABTM-linking table)
• staffgroups (has a groupname)

---

i used the conditions as follows:

$tmpConditions['AND'][] = array('Staff.isActive =' => "1");
$tmpConditions['OR'][] =  array('Staff.lastname LIKE' => "%$name%");
$tmpConditions['OR'][] = array('staffgroups.groupname LIKE' => "%$group%");
[...]

$this->Staff->recursive = 1;
$this->paginate = array('conditions' =>  $tmpConditions );
$this->set('staffs', $this->paginate());

I cant manage to get it working, allthough i think the Condition is set right.

cheers endo

Blackamoor

Answer 1

$tmpConditions['OR'][] = array('staffgroups.groupname LIKE' => "%$group%");

Is not following the CakePHP conventions. It should be:

$tmpConditions['OR'][] = array('StaffGroup.group_name LIKE' => "%$group%");

Model and Database conventions:

Models:

Model classnames are singular and CamelCased. Person, BigPerson, and ReallyBigPerson are all examples of conventional model names.

Database tables:

Table names corresponding to CakePHP models are plural and underscored. The underlying tables for the above mentioned models would be people, big_people, and really_big_people, respectively.

Database fields:

Field names with two or more words are underscored like, first_name.

Following these conventions, you should have a table called staff_groups which contains a field called group_name. The associated model would be named StaffGroup.

Lorenzen

Can you update your question? Please add it your question instead of editing the original lines. Could you also clarify how you database structure looks like? Can you access other data? What happens when you simply var_dump($this->StaffGroup->find('all'); ? – Lorenzen

ill redo mvc, and get back to you – Blackamoor

Answer 2

You cannot do that "out of the box", due to the HABTM association. You have to do it by hand.

First you need to get the StaffGroup.id(s) you are looking for

$group_ids = $this->Staff->StaffGroup->field('id', array('groupname LIKE' => '%$group%') );

Then unbind the HABTM association, and then bind the join table as hasMany association

$this->Staff->UnbindModel( array('hasAndBelongsToMany' => array('StaffGroup')) );
$this->Staff->bindModel(array('hasMany' => array('StaffStaffGroup')));

You can now perform your search

$this->Staff->StaffStaffGroup->find(
    'all', 
    array(
        'conditions' => array(
            'StaffStaffGroup.staff_group_id' => $group_ids,
            'Staff.isActive =' => "1",
            'Staff.lastname LIKE' => "%$name%",
        )
    )
);

Bissonnette

too bad thats not outa box, i'll try., at the end it was still not working although i got all conventions – Blackamoor

i put in a followup question: #14691952 – Blackamoor

Answer 3

In your $tmpConditions array the you should have the following format:

$tmpConditions = array(
       "AND" => array('Staff.isActive =' => "1"),
       "OR"  => array(
                'Staff.lastname LIKE' => "%$name%",
                'Staffgroup.groupname LIKE' => "%$group%"
       )
);

I assume that you have a table named staffgroups and that it actually contains the field groupname.

Also the line

$this->Staff->recursive = 1;

should be

$this->paginate = array(
      ...
      'recursive' => 1
);

I believe that this should do the job...

Scarab

I can't see why recursive needs to be inside paginate and not set by itself, both should work. – Embree

This is straight forward from the documentation [book.cakephp.org/2.0/en/core-libraries/components/… – Scarab

and where in the documentation does it say that $this->Staff->recursive = 1; is wrong? – Embree

At the Query Setup section it says explicitly that recursive as well as other Model->find('all') related parameters are passed to the paginated model through the paginate settings array. So I believe that this implies your answer – Scarab

Yes, you can pass it through the paginate array, but still, you don't have to. It is the same with the conditional array for find queries in CakePHP. You can still call the model with $this->Staff->recursive = 1;. It is correct and will work. Try it yourself and you will see that it works! – Embree

Answer 4

Just change staffgroups to Staffgroup. Naming conventions for models vs. tables means db table my_tables will be named model MyTable. See http://book.cakephp.org/2.0/en/getting-started/cakephp-conventions.html#model-and-database-conventions.

Embree