CakePHP sql-query

Question

I have the following tables:

- restaurants (restaurant.id, restaurant.name)
- menus (menu.id, menu.name, menu.active, menu.restaurant_id)

I want to have a list with all restaurants with the active menus (menu.active = true):

- restaurant2
    - menu1
    - menu4
- restaurant5
    -menu3
- restaurant19
    - menu34
    - menu33

My first idea was something like this:

$options['contain'] = array(
    'Menu' => array(
        'conditions' => $menuParams //array('Menu.active' => '1') //$menuParams
        )
     );

This doen't work becaus all restaurants will be listed. I want to have only restaurants with active menus.

Next idea: using join

$options['joins'] = array(
        array('table' => 'menus',
            'alias' => 'Menu',
            'type' => 'RIGHT',
            'conditions' => array(
                'Menu.restaurant_id = Restaurant.id',
            )
        )
    );

Not good, because, I don't have the ordered list I want. I need the menus grouped by the restaurant. Look above.

Is it the right way to make a join with restaurants and menus(active = true) and then using the contain to get the ordered list? I think that could work but I think also there is an easier way, right?

Any help is welcome! Thank you.

You need to add `'Menu.active' => '1'` to the `JOIN` query you showed, then it will give you the list of restaurant that has active menus, but why do you want to group the results? — Mahmoud Gamal, Mar 23 '13 at 10:32
Yes, that's right, I know that. Ok, group is the wrong word. I want to show restaurants with the menus like in the first list below. Now I get the following: restaurant 1, menu1 restaurant 1, menu2 restaurant 2, menu49 restaurant 2, menu22 I want to have: restaurant 1, menu 1, menu2 restaurant 2, menu49, menu 22 — FishWave, Mar 23 '13 at 10:39
actually, you should have a one to many relationship defined somewhere in your ORM. I don't recall the details, but it should be possible with CakePHP, and it should return the objects as requested. — didierc, Mar 23 '13 at 10:44
No, the ORDER BY doesn't solve the problem. I tried to write it above but new lines were deleted. I want this scheme: restaurant1(menu1, menu2, menu3), restaurant44(menu5, menu8), etc. Now I have: restaurant1(menu1), restaurant1(menu2), restaurant1(menu3), restaurant44(menu5), restaurant44(menu8). — FishWave, Mar 23 '13 at 10:47
Yes didierc, I have that relationship. Everything works fine if I list all restaurants with their menus. But the question is how do I the query If I want only restaurants with menus? That's is the core question. If I got that, the get the rest by myself... Go over to the Menu Model is not the solution, that is what I know so far. — FishWave, Mar 23 '13 at 10:53

didierc · Answer 1 · 2013-03-23T11:12:17.653

0

If you are using the ORM machinery from CakePHP, you should have a Restaurant model and a Menu model to describe each table. In the Restaurant model, you should have a $hasMany = "Menu" field, and in menu a $belongsTo = "Restaurant" field (assuming the model names are Menu and Restaurant).

From that point, doing queries using ORM is fairly straightforward:

$this->Restaurant->recursive = 1; // grab the menus
$conditions = array('Menu.active' => '1'); // restrict to active menus only
$this->Restaurant->find('all', array('conditions' => $conditions));

The above in the ad-hoc method of the Restaurant controller should retrieve the rows as an array of Restaurant objects, each bundled with an array of active Menu.

edited Mar 23 '13 at 11:12

answered Mar 23 '13 at 11:00

didierc

14,572
3
32
52

Thank you. I understand the concept of cakephp and I have all $belogsTo and $hasMany (I baked the code). Your solution is good and I had this to at the first sight. But it seems I cannot do the condition Menu.active on that level... System says: Column not found: 1054 Unknown column 'Menu.active' in 'where clause' – FishWave Mar 23 '13 at 11:26
My solution for now is to do a [join] and then a [contain]. A little bit complicated, but it works. – FishWave Mar 23 '13 at 11:31
what's the query like? I think there's debug option somewhere to see it. – didierc Mar 23 '13 at 12:18

score 0 · Answer 2 · answered Mar 24 '13 at 13:50

Now I found the easy and clean solution for my concern! Yeah!

First I had to unbind the bindings and then I had to make a new binding with the condition. It works like a charm. Here is the code:

        $this->unbindModel(array('hasMany' => array('Menu')));
        $this->bindModel(array('hasMany'=>array(
            'Menu'=>array(
                'foreignKey' => 'restaurant_id',
                'conditions' => array(
                                    'Menu.active' => 1
                                    )
            )
        )));

I thank you all for your answers!

CakePHP sql-query

2 Answers2