CakePHP find() based on HABTM relationships?

Turn your debug level up to 2 and look at the SQL output. Find the query that your code is generating and you'll notice there are several. The ORM layer in CakePHP doesn't join HABTM related tables in the first query.

It gets the results from the first select, then separately fetches the HABTM data for each item. Because the join table is not in the first query, your condition, which is intended for use on a joined table, results in the error you are seeing.

Up vote 4 down vote favorite 1 share g+ share fb share tw.

I'm working on a CakePHP 1.2 application. I have a model "User" defined with a few HABTM relationships with other tables through a join table. I'm now tasked with finding User information based on the data stored in one of these HABTM tables.

Unfortunately, when the query executes, my condition is rejected with an error about a missing table. Upon inspection it seems that CakePHP is not including any of the HABTM tables in the select statement. My User HABTM relationship is as follows: var $hasAndBelongsToMany = array( 'Course' => array( 'className' => 'Course', 'joinTable' => 'course_members', 'foreignKey' => 'user_id', 'associationForeignKey' => 'course_id', 'conditions' => '', 'order' => '', 'limit' => '', 'uniq' => false, 'finderQuery' => '', 'deleteQuery' => '', 'insertQuery' => '' ), 'School' => array( 'className' => 'School', 'joinTable' => 'school_members', 'foreignKey' => 'user_id', 'associationForeignKey' => 'school_id', 'conditions' => '', 'order' => '', 'limit' => '', 'uniq' => false, 'finderQuery' => '', 'deleteQuery' => '', 'insertQuery' => '' ), 'Team' => array( 'className' => 'Team', 'joinTable' => 'team_members', 'foreignKey' => 'user_id', 'associationForeignKey' => 'team_id', 'conditions' => '', 'order' => '', 'limit' => '', 'uniq' => false, 'finderQuery' => '', 'deleteQuery' => '', 'insertQuery' => '' ) ); The error is: SQL Error: 1054: Unknown column 'School.name' in 'where clause' And finally, the query it is trying to execute SELECT `User`.

`id`, `User`. `username`, `User`. `password`, `User`.

`firstName`, `User`. `lastName`, `User`. `email `, `User`.

`phone`, `User`. `streetAddress`, `User`. `city`, `User`.

`province`, `User`. `country`, `User `. `postal`, `User`.

`userlevel`, `User`. `modified`, `User`. `created`, `User`.

`deleted`, `User`. `deleted_date ` FROM `users` AS `User` WHERE `User`. `id` = 6 AND `School`.

`name` LIKE '%Test%' LIMIT 1 php cakephp habtm cakephp-1.2 link|improve this question edited May 29 '11 at 9:11Bogs54 asked Nov 29 '08 at 2:18Daniel.

Turn your debug level up to 2 and look at the SQL output. Find the query that your code is generating and you'll notice there are several. The ORM layer in CakePHP doesn't join HABTM related tables in the first query.

It gets the results from the first select, then separately fetches the HABTM data for each item. Because the join table is not in the first query, your condition, which is intended for use on a joined table, results in the error you are seeing. The cook book has a section on HABTM associations and fetching data based on conditions in the HABTM table that will fit your requirements.

Your table should be called "schools_users" and not "school_members" because it's many-to-many, thus using the plural form in the table name on both sides is appropiate. You also set the Model ClassName "School" as Alias for the HABTM. You should change that to something more generic like "Schools" as in "User is in and has many SchoolS" to avoid conflicts.

And another hint: Try to find the user "via" the School Model rather than the User Model. $this->User->Schools->find() Hope this helps.

FWIW, your join tables do appear to be "oddly named" insofar as they don't follow the convention described here: book.cakephp.org/view/83/hasAndBelongsTo... In any case, good luck, I remember HABTM being a butt-pain in CakePHP.

As best as I am aware this shouldn't matter as I explicitly set the joint table and model names. – Mat Nov 29 '08 at 15:11.

From the Cookbook: book.cakephp.org/view/83/hasAndBelongsTo... One option is to search the Tag model (instead of Recipe), which will also give us all of the associated Recipes. $this->Recipe->Tag->find('all', array( 'conditions' => array('Tag.name' => 'Dessert'))); We could also use the join table model (which CakePHP provides for us), to search for a given ID. $this->Recipe->bindModel(array('hasOne' => array('RecipesTag'))); $this->Recipe->find('all', array( 'fields' => array('Recipe.

*'), 'conditions' => array('RecipesTag. Tag_id' => 124) // id of Dessert )); It's also possible to create an exotic association for the purpose of creating as many joins as necessary to allow filtering, for example: $this->Recipe->bindModel(array('hasOne' => array('RecipesTag', 'FilterTag' => array( 'className' => 'Tag', 'foreignKey' => false, 'conditions' => array('FilterTag.id = RecipesTag. Tag_id') )))); $this->Recipe->find('all', array( 'fields' => array('Recipe.

*'), 'conditions' => array('FilterTag.name' => 'Dessert') )).

If it's lower than 1, Cake will not follow the relations defined. You could also try calling $User->contain("School") before the find to try and force Cake to use the relation. If none of these work I think you'll have to show us more code.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.