Databases
StackOverflow2013
Postsdbo
POAccessing associated models of HABTM relationships via bindModel() w/o recursion
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAccessing associated models of HABTM relationships via bindModel() w/o recursion
    primarykey
    data
    text
    <h2>The Problem In A Nutshell</h2> <p>I want to retrieve data from <em>Model A</em> that HABTM <em>Model B</em> via a find() operation in Model B's controller <em>without</em> relying on extensive recursion.</p> <pre><code>$this-&gt;ModelB-&gt;bindModel('hasMany' =&gt; array('ModelAsModelBs')); $this-&gt;ModelB-&gt;find('all', array('fields' =&gt; array('ModelA.*'))); //cond'ts below </code></pre> <p>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 <em>actual associated model</em>) without multiple recursion. </p> <p>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).</p> <h2>Concrete Example / Actual Circumstances</h2> <p>The database has these tables, with id's as you'd expect:</p> <ul> <li>Users </li> <li>Courses</li> <li>Modules</li> <li>UsersCourses</li> <li>UsersModules</li> <li>CoursesModules</li> </ul> <p>The model query I am trying execute is happening within the <code>Users</code> controller, and looks like this:</p> <pre><code>class Users extends AppController { public function doSomething() { $hasOne = array( 'hasOne' =&gt; array('CoursesModules','UsersModules')); $this-&gt;User-&gt;Course-&gt;Module-&gt;bindModel($hasOne); $conditions = array('`CoursesModules`.`course_id`' =&gt; $courseIds, //this is a known constraint within the app 'NOT' =&gt; array('`UsersModules`.`module_id` = `CoursesModules`.`module_id`' )); $options = array( 'fields' =&gt; array('Module', 'Course.*'), // Note the attempt to get Course model information 'conditions' =&gt; $conditions, 'recursive' =&gt; 0); $modules = $this-&gt;User-&gt;Course-&gt;Module-&gt;find('all', $options); $this-&gt;set(compact('modules')); } } </code></pre> <p>This query results in:<br /></p> <p><strong>Error:</strong> SQLSTATE[42S02]: Base table or view not found: 1051 Unknown table 'Course'</p> <p>But neither can I use <code>bindModel()</code> to connect <code>Courses</code> to <code>Modules</code>. This strikes me as strange since the association path is <code>Users-&gt;Courses-&gt;Modules</code>. I can bring recursion up a notch, but it causes all sorts of hell that requires a lot of <code>unbind()</code> and also pulls a completely absurd amount of data. </p> <p>A second oddity is that if I remove the <code>Course.*</code> from the fields list, the above query executes but doesn't work as I'd expect; I think this is correctly asking for all <em>Modules</em> listed in <em>CoursesModules</em> that are not also in <em>UsersModules</em>. Such data <strong>does</strong> exist in my records, yet isn't retrieved by this.</p> <p>I realize I can get <code>course_ids</code> from the <code>CoursesModules</code> 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 <code>$modules['Module']['Course']</code> in the rendered view file. </p> <p>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!</p> <h2>UPDATE</h2> <p>@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 <code>cake bake all</code> and then modified things as I went. The SQL for the tables and the <code>$hasAndBelongsToMany</code> arrays from the respective models are posted at the end.</p> <p>As to why I chose <code>hasOne</code>... I also assumed <code>hasMany</code>; 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 <code>hasOne</code> worked, to some extent. </p> <p>And finally, I have had a lurking suspicion that this <code>containable behavior</code> 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:</p> <p>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. </p> <p>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 <code>loadModel()</code>, executing a simpler <code>$this-&gt;Model-&gt;find()</code>, sorting the result through some <code>foreach</code> 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).</p> <pre><code>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; </code></pre> <h2>Model Associations</h2> <p><em>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.</em></p> <pre><code>// from User Model public $hasAndBelongsToMany = array( 'Course' =&gt; array( 'className' =&gt; 'Course', 'joinTable' =&gt; 'users_courses', 'foreignKey' =&gt; 'user_id', 'associationForeignKey' =&gt; 'course_id', 'unique' =&gt; 'keepExisting', ), 'Module' =&gt; array( 'className' =&gt; 'Module', 'joinTable' =&gt; 'users_modules', 'foreignKey' =&gt; 'user_id', 'associationForeignKey' =&gt; 'module_id', 'unique' =&gt; 'keepExisting', ) ); // from Course Model public $hasAndBelongsToMany = array( 'Module' =&gt; array( 'className' =&gt; 'Module', 'joinTable' =&gt; 'courses_modules', 'foreignKey' =&gt; 'course_id', 'associationForeignKey' =&gt; 'module_id', 'unique' =&gt; 'keepExisting', 'conditions' =&gt; '', 'fields' =&gt; '', 'order' =&gt; '', 'limit' =&gt; '', 'offset' =&gt; '', 'finderQuery' =&gt; '', ), 'User' =&gt; array( 'className' =&gt; 'User', 'joinTable' =&gt; 'users_courses', 'foreignKey' =&gt; 'course_id', 'associationForeignKey' =&gt; 'user_id', 'unique' =&gt; 'keepExisting', 'conditions' =&gt; '', 'fields' =&gt; '', 'order' =&gt; '', 'limit' =&gt; '', 'offset' =&gt; '', 'finderQuery' =&gt; '', ) ); // from Module Model public $hasAndBelongsToMany = array( 'Excerpt' =&gt; array( 'className' =&gt; 'Excerpt', 'joinTable' =&gt; 'excerpts_modules', 'foreignKey' =&gt; 'module_id', 'associationForeignKey' =&gt; 'excerpt_id', 'unique' =&gt; 'keepExisting', 'conditions' =&gt; '', 'fields' =&gt; '', 'order' =&gt; '', 'limit' =&gt; '', 'offset' =&gt; '', 'finderQuery' =&gt; '', ), 'Course' =&gt; array( 'className' =&gt; 'Course', 'joinTable' =&gt; 'courses_modules', 'foreignKey' =&gt; 'module_id', 'associationForeignKey' =&gt; 'course_id', 'unique' =&gt; 'keepExisting', 'conditions' =&gt; '', 'fields' =&gt; '', 'order' =&gt; '', 'limit' =&gt; '', 'offset' =&gt; '', 'finderQuery' =&gt; '', ) ); </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USNunser
    1. USJonline
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POAccessing associated models of HABTM relationships via bindModel() w/o recursion
      1. USJonline
      1. VTBountyStart
    2. VO
      singulars
      1. POAccessing associated models of HABTM relationships via bindModel() w/o recursion
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POAccessing associated models of HABTM relationships via bindModel() w/o recursion
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO"all *Modules* listed in *CoursesModules* that are not also in *UsersModules*" - Could you explain what you mean in plain English please? Am I mistaken in understanding it as "find all Modules that are part of a Course but have no Users"?
      singulars
      1. POAccessing associated models of HABTM relationships via bindModel() w/o recursion
      1. USPadraig Galvin
    2. COBy this I'd meant, "Find all modules where Module.id exists in CoursesModules.module_id but does not exist in UsersModules.module_id".
      singulars
      1. POAccessing associated models of HABTM relationships via bindModel() w/o recursion
      1. USJonline
    3. CO"I need to be able to retrieve the modules [a student hasn't] completed yet, in the context of the courses they're in." - That's what I was asking for!
      singulars
      1. POAccessing associated models of HABTM relationships via bindModel() w/o recursion
      1. USPadraig Galvin
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload