Question 22042 in Mysql Question posted in
The official MySQL documentation can be found here.

How to ignore where clause if condition is empty in Yii2 – Mysql

I know this same question is already asked before. But I have tried the solution but it’s not working for me.

 $comp_ids = AllowArea::find()
    ->select(['comp_code'])
    ->where(['user_id' => Yii::$app->user->id])
    ->column();

    $ref = (new yiidbQuery())
        ->select([
            'ProductCode',
            'ProductNameFull',
            'ProductSpec',
            'ProductGroup',
            'CompanyCode',
            'CompanyName'
            ,'Price',
            'PurchasePrice'
        ])->from('Product')
        ->andFilterWhere(['CompanyCode' =>  $comp_ids])
        ->all(Yii::$app->sds);

It’s giving me empty data.

Flow
The users are assigned areas and some users are assigned areas with a company. So I want the above query to return me the result whether the condition fails or not.

Update 1
The SQL which I am getting is

SELECT `ProductCode`, `ProductNameFull`, `ProductSpec`, `ProductGroup`, 
`CompanyCode`, `CompanyName`,
`Price`, `PurchasePrice` FROM `Product` WHERE `CompanyCode` IS NULL

Any help would be highly appreciated.

Tags:

3

Answers


  1. 0

    Try user orFilterWhere() this Adds an additional WHERE condition to the existing one but ignores empty operands.
    The new condition and the existing one will be joined using the ‘OR’ operator.
    This method is similar to orWhere(). The main difference is that this method will remove empty query operands. As a result, this method is best suited for building query conditions based on filter values entered by users.

    Login or Signup to reply.
  2. 0

    If nothing works, from what you were told, then you can try:

    $ref = (new yiidbQuery())
    ->select([
        'ProductCode',
        'ProductNameFull',
        'ProductSpec',
        'ProductGroup',
        'CompanyCode',
        'CompanyName'
        ,'Price',
        'PurchasePrice'
    ])->from('Product');

if (!empty($comp_ids)) {
    $ref->andFilterWhere(['CompanyCode' => $comp_ids]);
}

$ref = $ref->all(Yii::$app->sds);
    Login or Signup to reply.
  3. 0

    I propose you to add Is Not Null condition to the first query

     $comp_ids = AllowArea::find()
    ->select(['comp_code'])
    ->where(['user_id' => Yii::$app->user->id])
    ->andWhere('`comp_code` IS NOT NULL' )  
    ->column();

    And change andFilterWhere in the second query on

    ->andWhere('`CompanyCode` IN (' . join(',',  $comp_ids) . ')'))
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search