Yii2: How do I write the below Active Query?

Question

Using this solution, how can I get my $conditions variable to my active query?

$conditions = 'main_category_id != :main_category_id', ['category_status' => 1, 'main_category_id' => 0, 'sub_category_id' => $id,'type'=> 4];

$result = Category::find()->where($conditions)->orderby('category_name ASC')->all();

On my query main_category_id != 0. Any other solution that works is also fine

Please note that I need $conditions variable as they vary. Here is my query with if statements:

    public function get_subcategory_list($id="",$type="")
{
    $conditions = ['category_status' => 1, 'main_category_id' => 0, 'main_category_id' => $id, 'type' => 2];
    if($type == 2){
        $conditions = ['category_status' => 1, 'main_category_id' => 0, 'sub_category_id' => $id, 'type' => 3];
    }
    if($type == 3){
        $conditions = ['category_status' => 1, 'main_category_id' => 0, 'sub_category_id' => $id,'type'=> 4];
    }
    $result = Category::find()->where($conditions)->orderby('category_name ASC')->all();
    return $result;
}

Please note that $conditions works fine on the above function the only problem is here main_category_id should not be equal to 0.

In the first condition assignment - `'main_category_id' => 0, 'main_category_id' => $id`. Is it a typo? Shoudn't it be `'main_category_id' => 0, 'sub_category_id' => $id`? — arogachev, Aug 08 '15 at 07:28

arogachev · Accepted Answer · 2015-08-08T07:48:09.820

2

You can't assign variable ($conditions) like you did, it's not valid.

Your ActiveQuery can be written like this:

$models = Category::find()
    ->where(['<>', 'main_category_id', 0])
    ->andWhere([
        'category_status' => 1,
        'sub_category_id' => $subCategoryId,
        'type'=> 4,
    ])
    ->orderBy(['category_name' => SORT_DESC])
    ->all();

You can replace <> with != or not in in case of using array of main category ids.

Read more about constructing where conditions in official docs.

Update: No need to change the whole $conditions array and copy-paste. You can calculate the $type for example like this:

switch ($type) {
    case 2:
        $typeValue = 3;

        break;
    case 3:
        $typeValue = 4;

        break;
    default:
        $typeValue = 2;
}

This logic is a little bit weird (maybe increment +1 will be better).

Then just insert $typeValue instead of static value here 'type'=> $typeValue.

Even you have some complex query constructing, you can divide query in separate where / orWhere / andWhere and dynamically change it.

edited Aug 08 '15 at 07:48

answered Aug 08 '15 at 06:47

arogachev

33,150
7
114
117

My conditions vary so I need to assign a variable. What is the way to do this? I know how to use `ActiveQuery` as you have stated but I need my `$conditions` to change according to different situations. – Zack Aug 08 '15 at 07:15
Please provide more details about how it can change. – arogachev Aug 08 '15 at 07:21
I have, please also note that assigning variable `$conditions` is completely valid. – Zack Aug 08 '15 at 07:25
1

Not the way you initially wrote it (with comma). – arogachev Aug 08 '15 at 07:27
True, I initially wrote it with errors to show what I needed to achieve. – Zack Aug 08 '15 at 07:40
The given solution seems to work fine only when [I do not filter using this](http://stackoverflow.com/a/22972500/3128638). I now need to find out what is causing the DB exception – Zack Aug 08 '15 at 07:57
Without seeing what you added and exception details, it's impossible to help. – arogachev Aug 08 '15 at 08:10

Yii2: How do I write the below Active Query?

1 Answers1