On Dec 7, 1:54 pm, brian <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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?
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
