How do I query data in CakePHP using HABTM relationships?

Question

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

Solution 1

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.

Solution 2

From the Cookbook: http://book.cakephp.org/view/83/hasAndBelongsToMany-HABTM

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')
));

Solution 3

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.

Comments

[Admin]

As best as I am aware this shouldn't matter as I explicitly set the joint table and model names.

[TeckniX]

This was, I feel, the best answer - One thing to consider is that you have to make sure the 'find' uses contain on the model you binded. So 'contain' => 'FilterTag' after your conditions.