Yii2 Model ViaTable

Question

For simplicity, lets say I have 3 tables: menu, page and a junction table menu_page.

So If I want to get all menus which are available for page "home", in the model I defined a relationship:

public function getAllMenus() {
    return $this->hasMany(Menu::className(), ['id' => 'menu_id'])->viaTable(PageMenu::tableName(), ['page_id' => 'id']);
}

But now we have added an attribute to menu table called show_all_pages, if this is set as 1, menu should be returned, if not we should check if menu is enabled to be used on home.

Is there a way to add this condition here?

do you want it to be globally integrated within the relation or it is required for any specific `controller` or `action` ? — Muhammad Omer Aslam, Jun 25 '18 at 20:05

score 0 · Answer 1 · answered Jun 25 '18 at 16:50

0

You can make use of an andWhere clause to apply this filter on show_all_pages

public function getAllMenus() {
    return $this->hasMany(Menu::className(), ['id' => 'menu_id'])
                ->viaTable(PageMenu::tableName(), ['page_id' => 'id'])
                ->andWhere(['show_all_pages' => 1]);
}

answered Jun 25 '18 at 16:50

ajmedway

1,492
14
28

if I understand correctly, you are adding an extra condition over the query which is already doing a join with pagemenu. The idea is that a menu can have field show_all_pages but no records on pagemenu table. – Eduardo Jun 26 '18 at 03:25
@Eduardo "you are adding an extra condition over the query which is already doing a join with pagemenu" - this is correct, however the next part of your comment I do not understand, but effectively this `andWhere` serves to add in `show_all_pages = 1` to the `WHERE` clause of the generated sql – ajmedway Jun 26 '18 at 09:12
My case I need or doing the join using viaTable or using show_all_pages attribute condition, both can not be applied. – Eduardo Jun 27 '18 at 04:43

score 0 · Accepted Answer · answered Jul 20 '18 at 17:59

The solution I found so far was doing to separate Active Queries and do a Union:

public function getSelectedMenus() {

        return $this->hasMany(Menu::className(), ['id' => 'menu_id'])->viaTable(PageMenu::tableName(), ['page_id' => 'id'])->onCondition(['menu.active' => Page::ACTIVE]);
    }

    public function getAllMenus() {
        return Menu::find()->where(['active' => Page::ACTIVE, 'show_all_pages' => 1]);
    }

    public function getMenus() {

        $selectedMenus = $this->getSelectedMenus();
        $allMenus = $this->getAllMenus();

        return $selectedMenus->union($allMenus);

    }

Yii2 Model ViaTable

2 Answers2