简而言之这个问题
我想通过模型B的控制器中的find（）操作从模型A中检索数据，而不依赖于广泛的递归。

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

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

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;

// 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' => '',
        )
    );

最佳答案

据我所知，这就是问题所在；用户在其课程中有可用模块的列表。我们要查找用户尚未完成的可用模块。换言之，我们必须找到不在用户模块中的用户课程模块。
我能想到的最好的办法是：

$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,
));

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

$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,
));

关于mysql - 通过bindModel()w/o递归访问HABTM关系的关联模型，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/20341136/