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 [email protected] 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 -~----------~----~----~----~------~----~------~--~---
