I'm not sure that either of these suggestions will help your problem, but ...
1) you should try using the latest CakePHP (revision 7296 last I checked). There have been a number of improvements and bug fixes since 6311. 2) you've got an extra comma at the end of your find query ("'recursive' => 0,"). I don't see any reason why it would cause your error, but it couldn't hurt to remove it. On Mon, Jun 30, 2008 at 12:25 PM, Mike <[EMAIL PROTECTED]> wrote: > > Hello! > > I'm really sorry to post this, but I've looked around, and haven't > been able to figure out how to do a find on a Model (named 'Term"), > using conditions that restrict the results based on fields in another > model (named "Course") that belongsTo the first model . Term hasMany > Course. > > In the manual (http://book.cakephp.org/view/66/models#retrieving-your- > data-73), at the end of 3.7.4.2 ("Complex Find Conditions"), is says > "These conditions are also infinitely nest-able. Let's say you had a > hasMany/belongsTo relationship between Posts and Authors, which would > result in a LEFT JOIN. Let's say you wanted to find all the posts that > contained a certain keyword ("magic") or were created in the past two > weeks, but you want to restrict your search to posts written by Bob", > and has the example code of: > "Author.name" => "Bob" > > > > So in my app, I want to have Terms (school terms - in the US, > semesters, or maybe quarters, depending on the college), and Courses. > I'd like to be able to ask for a list of all Terms, such that there's > at least one course in that term. I'd also like to figure out how to > generally be able to filter my finds based on critieria :). So to > start, I figured that I'd just ask for all terms that have course #1 > in them (which should just be one term), in order to figure out how to > use the find('all') conditions. > > (I've got all the code up at http://bin.cakephp.org/saved/34262, if > it's more readable there) > > The problem is that if I try to do: > > $terms = $this->Term->find('all', array( > 'conditions' => array('Course.id' => '1'), > 'order' => 'year DESC', > 'recursive' => 2, > )) ; > > The resulting SQL query looks like: > > SELECT `Term`.`id`, `Term`.`year`, `Term`.`quartername` FROM `terms` > AS `Term` WHERE `Course`.`id` = 1 ORDER BY `year` DESC > > Which causes problems b/c it's only using the Term table, but my > condition uses the Course field. As far as I can tell, CakePHP will > first query the terms table, then will separately query the Courses > table, but it doesn't seem to be putting the 'Course.id' criteria on > the right query. Based on what I read in the manual, I would expect > this to work, but it isn't. > > What's odd is that if I query the COURSE instead, like so: > > $terms = $this->Course->find('all', array( > 'conditions' => array('Term.id' => 1, 'Course.id' => 1), > 'order' => 'Term.year DESC', > 'recursive' => 0, > )) ; > $terms = Set::extract($terms, "{n}.Term"); // RC2: -> classicExtract > > Cake generates SQL kinda like: > SELECT `Course`.`id`, `Course`.`term_id`, /*SNIP*/ `Term`.`id`, > `Term`.`year`, `Term`.`quartername` FROM `courses` AS `Course` LEFT > JOIN `terms` AS `Term` ON (`Course`.`term_id` = `Term`.`id`) WHERE > `Term`.`id` = 1 AND `Course`.`id` = 1 ORDER BY `Term`.`year` DESC > > Since this query includes both tables, the condition works. Using the > Set::extract, I can then get back to my list of terms (which should > work just fine, if my recursive level is high enough :) ). > > > My first question: If Term hasMany Course, and I want a list of Terms > (filtered, in part, based on Courses), shouldn't I be doing a find on > Term (i.e., the first query)? > > Second question: If I wanted to get a list of Terms that have a non- > empty sublist of Courses, can I do that by querying the Term? The > second query (i.e., the one on Course) does this for my by default), > but it seems a tad bit odd. > > > I'm trying to use the Cake 1.2.0.6311 (beta) distribution on XAMP, and > I've tried it using both FireFox 3 and IE 7.0.5730.13, not that the > client should matter. > > > Quick Aside: I've looked at using the new ContainableBehavior, but > as I understand it, I should be able to filter using the Find method. > I _think_ that Containable will try to (un)bind models to restrict the > query - I"m not sure if it'll actually put the conditions onto the > query, or if it'll pull all the data back, then apply conditions in- > memory. > There's a note at the very end of the manual entry that says > "Additional filtering can be performed by supplying the standard Model- >>find() options" (http://book.cakephp.org/view/474/containable). That > said, if I'm wrong in my understanding of this, and Containable should > be used here, please let me know! > > If anyone could help (or even just offer suggestions/hints), that > would be great! > Thanks! > --Mike > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~----------~----~----~----~------~----~------~--~---