http://teknoid.wordpress.com/2008/07/17/forcing-an-sql-join-in-cakephp/ http://teknoid.wordpress.com/2008/09/05/example-of-cakephps-containable-for-deep-model-bindings/ Lapinski erturne wrote: > > > On Dec 7, 5:48 pm, brian <bally.z...@gmail.com> wrote: >> I misinterpreted MenuPostings. I thought it was a table of comments or >> something like that. So, it seems that, though there may be many >> MenuPostings for a particularMenu, only a single one is really >> "active" at a given time. Is that the case? If so, why not just add >> columns post_start & post_end to the menus table and modify them >> whenever the posting dates for a menu need to be changed? That would >> make your query much simpler. >> > > They want to set up their menu schedule ahead of time, and not have to > remember to set the next posting each time one expires. I can also > envision other situations like this in other applications, and as this > is my first Cake app I'd like to learn what the limitations of the > technology are. In this case I COULD attack the problem from a > different angle, starting with MenuPosting rather than Menu so that I > can specify conditions on the post_start and post_end, but I don't > like the idea changing simply because I don't understand how to make > it work the way I want to. I'd really like to find out if it's > possible to do what I want (obtain menus filtered based on the > criteria of an associated model). Or if it's not possible, why. > >> On Sun, Dec 7, 2008 at 5:27 PM, erturne <eric.tur...@turner-wa.com> >> wrote: >> >> > On Dec 7, 1:54 pm, brian <bally.z...@gmail.com> wrote: >> >> You need a LEFT JOIN in your query. MenuPosting can't be in the WHERE >> >> clause. Perhaps if you post the actual SQL query you're aiming for we >> >> can figure out how to have Cake generate it. >> >> > Essentially I want information about menus and the entrees on them. >> > Something like: >> >> > SELECT Menus.title, Entrees.name FROM menus Menus >> > INNER JOIN entrees_menus EntreesMenus ON EntreesMenus.menu_id=Menus.id >> > INNER JOIN entrees Entrees ON EntreesMenus.entree_id=Entrees.id >> > INNER JOIN menu_postings MenuPostings ON MenuPostings.menu_id=Menus.id >> > WHERE MenuPostings.post_start <= NOW() AND MenuPostings.post_end > NOW >> > (); >> >> > Of course I'll eventually want more than just the menu title and >> > entree names, but that's good enough for now. >> >> > I don't think LEFT JOIN is correct for what I need because I'm not >> > interested in a menu without entrees! INNER JOIN is the way to go >> > here. Not sure if we can make CakePHP do that using find() or >> > Containable or unbindModel()/bindModel(). I'm definitely open to >> > suggestions. >> >> >> What are post_start & post_end, anyway? >> >> > Records in the menu_postings table define when each menu is visible on >> > the website. We might have a particular menu visible on the website at >> > various times throughout the year (defined by post_start and >> > post_end). >> >> >> On Sat, Dec 6, 2008 at 10:06 PM, erturne <eric.tur...@turner-wa.com> >> wrote: >> >> >> > I've seen similar questions raised but haven't found a solution that >> >> > works. Perhaps it's because I'm new to CakePHP and don't fully >> >> > understand unbindModel(), bindModel(), container behaviors, etc. >> >> > Anyway, I'll start simple and take suggestions. >> >> >> > I have the following models in which a Menu hasAndBelongsToMany >> Entree >> >> > and hasMany MenuPosting, and a MenuPosting belongsTo Menu: >> >> >> > class Menu extends AppModel { >> >> > var $name = 'Menu'; >> >> > var $hasAndBelongsToMany = 'Entree'; >> >> > var $hasMany = array( >> >> > 'MenuPosting' => array( 'dependent' => true ) >> >> > ); >> >> > } >> >> >> > class MenuPosting extends AppModel { >> >> > var $name = 'MenuPosting'; >> >> > var $belongsTo = 'Menu'; >> >> > } >> >> >> > class Entree extends AppModel { >> >> > var $name = 'Entree'; >> >> > } >> >> >> > In my MenusController I have a display_current() action to find all >> >> > menus that are currently posted: >> >> >> > class MenusController extends AppController { >> >> > var $name = 'Menus'; >> >> > function display_current() { >> >> >> > // Limit the query results to the menu that is >> visible (i.e. is >> >> > // currently posted) >> >> > $conditions = array( >> >> > 'MenuPosting.post_start <=' => date('Y-m-d >> H:i:s', strtotime >> >> > ("now")), >> >> > 'MenuPosting.post_end >' => date('Y-m-d >> H:i:s', strtotime("now")), >> >> > ); >> >> >> > // Find the menus that meets the conditions. >> >> > $this->set('menu', $this->Menu->find('all', >> array('conditions' => >> >> > $conditions))); >> >> > } >> >> > } >> >> >> > The error I get from this is: >> >> > Warning (512): SQL Error: 1054: Unknown column >> >> > 'MenuPosting.post_start' in 'where clause' >> >> >> > $sql = "SELECT `Menu`.`id`, `Menu`.`old_id`, >> `Menu`.`created`, >> >> > `Menu`.`modified`, `Menu`.`title` FROM `menus` AS `Menu` WHERE >> >> > `MenuPosting`.`post_start` <= '2008-12-06 22:02:57' AND >> >> > `MenuPosting`.`post_end` > '2008-12-06 22:02:57' " >> >> >> > If I remove the conditions from the find I am able to retrieve all >> >> > Menu and associated Entree, so I believe I have set up my database >> >> > tables correctly. >> >> >> > Any suggestions? > > > > -- View this message in context: http://www.nabble.com/Model-Find-With-Conditions-On-Associated-Models-Not-Working-tp20880170p24621656.html Sent from the CakePHP mailing list archive at Nabble.com. --~--~---------~--~----~------------~-------~--~----~ 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 cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~----------~----~----~----~------~----~------~--~---
