Accessing associated models of HABTM relationships via bindModel() w/o recursion

Question

The Problem In A Nutshell

I want to retrieve data from Model A that HABTM Model B via a find() operation in Model B's controller without relying on extensive recursion.

$this->ModelB->bindModel('hasMany' => array('ModelAsModelBs'));
$this->ModelB->find('all', array('fields' => array('ModelA.*'))); //cond'ts below

I'm aware that bindModel() is required to do this, but I can't seem to get access to the associated model fields (ie. not just the HABTM table's fields, but the actual associated model) without multiple recursion.

It occurs to me that I may also be fundamentally misunderstanding something about how model relationships are supposed to interact, or be designed, or retrieved, etc.—in short, I recognize that the reason I may not be succeeding is that this may not be something I should be doing, henh. If this is so, I'd be equally happy learning how to do this better, because I frequently deal with very elaborate model relationships (I mostly do web development for academia and on-line course material/remote research).

Concrete Example / Actual Circumstances

The database has these tables, with id's as you'd expect:

• Users
• Courses
• Modules
• UsersCourses
• UsersModules
• CoursesModules

The model query I am trying execute is happening within the Users controller, and looks like this:

class Users extends AppController {
    public function doSomething() {
        $hasOne = array( 'hasOne' => array('CoursesModules','UsersModules'));
        $this->User->Course->Module->bindModel($hasOne);
        $conditions = array('`CoursesModules`.`course_id`' => $courseIds, //this is a known constraint within the app
                    'NOT' => array('`UsersModules`.`module_id` = `CoursesModules`.`module_id`' ));
        $options = array( 'fields' => array('Module', 'Course.*'), // Note the attempt to get Course model information
                  'conditions' => $conditions,
                  'recursive' => 0);
        $modules = $this->User->Course->Module->find('all', $options);
        $this->set(compact('modules'));
    }
}

This query results in:

Error: SQLSTATE[42S02]: Base table or view not found: 1051 Unknown table 'Course'

But neither can I use bindModel() to connect Courses to Modules. This strikes me as strange since the association path is Users->Courses->Modules. I can bring recursion up a notch, but it causes all sorts of hell that requires a lot of unbind() and also pulls a completely absurd amount of data.

A second oddity is that if I remove the Course.* from the fields list, the above query executes but doesn't work as I'd expect; I think this is correctly asking for all Modules listed in CoursesModules that are not also in UsersModules. Such data does exist in my records, yet isn't retrieved by this.

I realize I can get course_ids from the CoursesModules and then just do another find to get the Course model data, but that's a) not very Cake-like and b) a pain because I'd really appreciate having access to $modules['Module']['Course'] in the rendered view file.

Can anyone point me in the right direction here? Or, haha, god forbid, help me just build this MySQL query (I am all thumbs with MySQL joins)? Truly appreciated, thanks!

UPDATE

@Kai: To set up the relationships I set up my tables and baked'em. Worth noting, perhaps, is that I have a fairly basic grasp of MySQL and generally do everything through PhpMyAdmin. As for generating the initial Cake files, I use cake bake all and then modified things as I went. The SQL for the tables and the $hasAndBelongsToMany arrays from the respective models are posted at the end.

As to why I chose hasOne... I also assumed hasMany; using this relationship consistly generated 'column not found' errors from the tables I was binding (didn't matter which column). Meanwhile, the obviously wrong choice of hasOne worked, to some extent.

And finally, I have had a lurking suspicion that this containable behavior business might be what I was after, but I don't really understand it. As briefly as I can, this is the context for these models and the sorts of queries I'm trying to execute:

I'm building a program for a university faculty that will basically let profs have some online coursework. But the coursework (ie. modules) might be shared between different classes (ie. courses), and students might be in any or all classes. An additional constraint is that a student may have a choice of which modules she'll do in a given course—the prof may offer five of which they'll have to complete any three. So, when a student logs in, I need to be able to retrieve the modules they haven't completed yet, in the context of the courses they're in.

There are a plethora of similar queries I've got to make that are more or less of this nature. As it stands, I can achieve all this (and since this is on a deadline, have done so) through various uses of loadModel(), executing a simpler $this->Model->find(), sorting the result through some foreach logic, rinse repeat. Aside from being irritating, I'm also worried it's not scalable because of undue processing on my part, and finally... I hate doing things wrong, haha. I know that CakePHP can handle the questions I want to ask of my data, I just don't know how to ask them (and/or set up the data so such questions can be asked).

CREATE TABLE IF NOT EXISTS `modules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `domain_id` int(11) NOT NULL,
  `subject_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `passing_score` int(11) NOT NULL,
  `max_attempts` int(11) NOT NULL,
  `allow_retry` int(11) NOT NULL,
  `running_score` tinyint(1) NOT NULL,
  `score_privacy` int(11) NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `domain_id` (`domain_id`),
  KEY `subject_id` (`subject_id`)
)  ENGINE=InnoDB  DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group_id` int(11) NOT NULL,
  `institution_id` int(11) NOT NULL,
  `password` varchar(255) NOT NULL,
  `firstname` varchar(63) NOT NULL,
  `lastname` varchar(63) NOT NULL,
  `email` varchar(255) NOT NULL,
  `studentno` varchar(31) NOT NULL,
  `claimed` tinyint(1) NOT NULL,
  `verified` tinyint(1) NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `group_id` (`group_id`),
  KEY `institution_id` (`institution_id`)
)  ENGINE=InnoDB  DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `courses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `institution_id` int(11) NOT NULL,
  `coursecode` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `semester` varchar(7) NOT NULL,
  `educator_id` int(11) NOT NULL,
  `year` year(4) NOT NULL,
  `description` text NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`educator_id`), /* educator is an alias of user in some cases */
  KEY `institution_id` (`institution_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `users_courses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`,`course_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1; 

CREATE TABLE IF NOT EXISTS `users_modules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `module_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`,`module_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 

CREATE TABLE IF NOT EXISTS `courses_modules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `course_id` int(11) NOT NULL,
  `module_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `course_id` (`course_id`,`module_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

Model Associations

Note: this is not comprehensive—hasOne,hasMany,belongsTo,etc. have been omitted so as to save on space; if need be I can post the entire model.

// from User Model
public $hasAndBelongsToMany = array(
        'Course' => array(
            'className' => 'Course',
            'joinTable' => 'users_courses',
            'foreignKey' => 'user_id',
            'associationForeignKey' => 'course_id',
            'unique' => 'keepExisting',
        ),
        'Module' => array(
            'className' => 'Module',
            'joinTable' => 'users_modules',
            'foreignKey' => 'user_id',
            'associationForeignKey' => 'module_id',
            'unique' => 'keepExisting',
        )
    );

// from Course Model
    public $hasAndBelongsToMany = array(
        'Module' => array(
            'className' => 'Module',
            'joinTable' => 'courses_modules',
            'foreignKey' => 'course_id',
            'associationForeignKey' => 'module_id',
            'unique' => 'keepExisting',
            'conditions' => '',
            'fields' => '',
            'order' => '',
            'limit' => '',
            'offset' => '',
            'finderQuery' => '',
        ),
        'User' => array(
            'className' => 'User',
            'joinTable' => 'users_courses',
            'foreignKey' => 'course_id',
            'associationForeignKey' => 'user_id',
            'unique' => 'keepExisting',
            'conditions' => '',
            'fields' => '',
            'order' => '',
            'limit' => '',
            'offset' => '',
            'finderQuery' => '',
        )
    );

// from Module Model
    public $hasAndBelongsToMany = array(
        'Excerpt' => array(
            'className' => 'Excerpt',
            'joinTable' => 'excerpts_modules',
            'foreignKey' => 'module_id',
            'associationForeignKey' => 'excerpt_id',
            'unique' => 'keepExisting',
            'conditions' => '',
            'fields' => '',
            'order' => '',
            'limit' => '',
            'offset' => '',
            'finderQuery' => '',
        ),
        'Course' => array(
            'className' => 'Course',
            'joinTable' => 'courses_modules',
            'foreignKey' => 'module_id',
            'associationForeignKey' => 'course_id',
            'unique' => 'keepExisting',
            'conditions' => '',
            'fields' => '',
            'order' => '',
            'limit' => '',
            'offset' => '',
            'finderQuery' => '',
        )
    );

Answer 1

As I understand it, this is the problem; A user has a list of available modules through their courses. We want to find available modules that the user has not completed. In other words, we have to find a user's course's modules which are not in that user's modules.

Here's the best way I can think of doing that:

$modules = $this->User->Module->find('all', array(
    'joins' => array(
        array(
            'table' => 'courses_modules',
            'alias' => 'CoursesModule',
            'type' => 'LEFT',
            'conditions' => array('CoursesModule.module_id = Module.id'),
        ),
        array(
            'table' => 'courses_users',
            'alias' => 'CoursesUser',
            'type' => 'LEFT',
            'conditions' => array('CoursesUser.course_id = CoursesModule.course_id'),
        ),
        array(
            'table' => 'modules_users',
            'alias' => 'ModulesUser',
            'type' => 'LEFT',
            'conditions' => array(
                'ModulesUser.module_id = Module.id',
                'ModulesUser.user_id' => $userId,
            ),
        ),
    ),
    'conditions' => array(
        'CoursesUser.user_id' => $userId,
        'ModulesUser.id' => null,
    ),
    'recursive' => -1,
));

Which should build a query like this:

SELECT Module.* FROM modules AS Module
LEFT JOIN courses_modules AS CoursesModule
    ON (CoursesModule.module_id = Module.id)
LEFT JOIN courses_users AS CoursesUser
    ON (CoursesUser.course_id = CoursesModule.course_id)
LEFT JOIN modules_users AS ModulesUser
    ON (ModulesUser.module_id = Module.id
        AND ModulesUser.user_id = $userId)
WHERE CoursesUser.user_id = $userId
AND ModulesUser.id IS NULL

The first two joins along with the user_id condition will get all the available modules. Rather than getting the user, their courses and then that course's modules we work backwards (kind of); we get the modules and join the courses to which we join the users. Once we add the user_id condition the association will be filtered and only modules that can be joined to the user will be found. Since we are starting with the modules there won't be any duplicates, we don't know (or care) which course was used to make the link.

With those available modules we then join the modules a user has completed and limit the records to those that couldn't be joined.

The CakePHP documentation has more info on joining tables.

Edit :

If you want to set the course IDs manually, you can do this:

$modules = $this->User->Module->find('all', array(
    'joins' => array(
        array(
            'table' => 'courses_modules',
            'alias' => 'CoursesModule',
            'type' => 'LEFT',
            'conditions' => array('CoursesModule.module_id = Module.id'),
        ),
        array(
            'table' => 'modules_users',
            'alias' => 'ModulesUser',
            'type' => 'LEFT',
            'conditions' => array(
                'ModulesUser.module_id = Module.id',
                'ModulesUser.user_id' => $userId,
            ),
        ),
    ),
    'conditions' => array(
        'CoursesModule.course_id' => $courseIds,
        'ModulesUser.id' => null,
    ),
    'recursive' => -1,
));

Answer 2

There's something wrong with the way you set up your relationships. Could you post to show what you've done to set up the relationships, plus what the SQL query is that's giving the error? I'm also confused about what exactly your query is supposed to do.

I can make a few observations already though:

• Course does not seem to be defined in your query, which is why you're getting a sql error. Probably there is something wrong with your relationships regarding Course.

• If you're trying to do a HABTM relationship between Users and Modules and Courses and Modules, why are you doing creating a hasOne relationship between Module and CoursesModules and Module and UsersModules?

The short version of what I'm wondering is, shouldn't that be a hasMany instead of a hasOne?

The long version is, occasionally, when you have a join table like that, you'll need to for some reason create a relationship with the join table itself rather than just using HABTM (where when you get the data, the data in the join table is not included). Generally this is because there is data besides the two foreign keys that must also be stored in the join table. But in that case, you should be using what is known as a has many through relationship. If it's not necessary to use a has many through, you should just be using HABTM with users and courses, and not be creating relationships with directly with the join tables at all. http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html#hasmany-through-the-join-model

• Conditions for related models need to be in the contain options. Keep in mind that the normal behavior of Cake is to get related model data in additional queries, rather than doing a join and getting it all in one query. http://book.cakephp.org/2.0/en/core-libraries/behaviors/containable.html