jump to navigation

Putting find conditions on a HABM relationship with cakephp March 11, 2009

Posted by maxmil in : cakephp , trackback

Have you ever had to add conditions to a find call where the conditions apply to a model object that has a Has And Belongs To Many relationship with your principal object.

Example:
Model object A has a HABTM relationship with object B.
You want to generate a sql statement that looks like
SELECT .... FROM A INNER JOIN B ON ... WHERE B.id = ?

I found the solution here

1) Since you have a HABTM relationship you must already have a database table called As_Bs. Create a dummy model that represents this table as if it were an actual object in your model.

If your table is As_Bs your model object would be called AsB. You don’t need to create the model file.

If your database follows standards, then the table As_Bs has fields a_id and b_id and the default model values should be ok. Otherwise you may need to customize the asossiation.

Note that if you are using the paginate component you will need to add false as a second parameter to bindModel so that the bind lasts.
$this->A->bindModel(array('hasOne'=>array('AsB'=>array())));
2) Add your constraint. This asumes that you want only A’s who have B’s where B’s id is 3 or 5
$conditions = array('AsB.b_id' => array(3,5))
3) Remove results. Any A that contains both the B with id 3 and the B with id 5 will be returned twice. To avoid this you need to add a DISTINCT to the id field in a field list
$fields = array('DISTINCT A.id, .... other fields go here)
4) Execute the find as normal
$this->A->find('all', array('conditions' => $conditions, $fields => fields))

Hope that this helps someone somewhere someday…

Comments»

no comments yet - be the first?