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.
On Sun, Dec 7, 2008 at 5:27 PM, erturne <[EMAIL PROTECTED]> wrote:
>
>
>
> 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
-~----------~----~----~----~------~----~------~--~---
