Yii2: How to override the yii\db\Query class to add default conditions with queries

Question

I want to make a cloud-based application where I store the application id and branch_id in session and I want to add the application_id and branch_id to each DB query.

Easy I am overriding the find() using

    public static function find()
{
    return parent::find()->where([]);
}

But the issue is how i override the query like this

  $total_return_price = (new Query())
            ->select('SUM(product_order.order_price * product_order.quantity) as return_price')
            ->from('order')
            ->innerJoin('product_order', 'product_order.order_id = order.id')
            ->where(['=', 'order.user_id', $user_id])
            ->andWhere(['=', 'order.status', '4'])
            ->one();

You can change that query with activeQuery and write like this `Order::find()->select('SUM(product_order.order_price * product_order.quantity) as return_price') ->innerJoin('product_order', 'product_order.order_id = order.id') ->where(['=', 'order.user_id', $user_id]) ->andWhere(['=', 'order.status', '4']) ->one();` — Golub, Mar 07 '18 at 13:16
Thak u for your response.There are more the 200 different type of queries in the application.i can not use this in each query. — rajwa766, Mar 07 '18 at 13:37
just tell me please how i can overide the each query to db using activeRecord class — rajwa766, Mar 07 '18 at 13:39
The answer is: you can't. An option is to override `Query` class by **`DependencyInjection`** — Yupik, Mar 07 '18 at 14:14
[Yii2 - Dependency Injection Guide](http://www.yiiframework.com/doc-2.0/guide-concept-di-container.html) — Yupik, Mar 07 '18 at 14:18
Hey did the answer worked out for you do select if it helped you somehow. Thanks — Muhammad Omer Aslam, Mar 14 '18 at 13:33
hey @Sajid i noticed you took back your upvote on my answer was there ay problem you faced? — Muhammad Omer Aslam, Mar 17 '18 at 00:24

Muhammad Omer Aslam · Accepted Answer · 2018-03-15T20:17:17.630

Well one way is to extend the Query class and override the traits where() from() and select() and change the namespace from the yii\db\Query to common\components\Query overall in the models where you want the condition to be added. But remember it is your responsibility to make sure all those tables have these 2 fields (application_id and branch_id) inside the tables where ever you replace the yii\db\Query with common\components\Query.

Why override where() from() and select() ? you have the possibility of writing queries in the following formats.

Let's say we have a product table with the fields id and name, now consider the following queries.

$q->from ( '{{product}}' )
        ->all ();

$q->select ( '*' )
        ->from ( '{{product}}' )
        ->all ();

$q->from ( '{{product}}' )
        ->where ( [ 'name' => '' ] )
        ->all ();

$q->from ( '{{product}}' )
        ->andWhere ( [ 'name' => '' ] )
        ->all ();

$q->select ( '*' )
        ->from ( '{{product}}' )
        ->where ( [ 'IN' , 'id' , [ 1 , 2 , 3 , 4 , 5 , 6 , 7 , 89 , 0 ] ] )
        ->andwhere ( [ 'name' => '' ] )
        ->all ();


$q->select ( '*' )
        ->from ( '{{product}}' )
        ->where ( [ 'and' ,
        [ 'IN' , 'id' , [ 1 , 2 , 3 , 4 , 5 , 6 , 7 , 89 , 0 ] ] ,
        [ 'name' => '' ]
        ] )
        ->all();

The above will generate the following SQL queries

SELECT * FROM `product` 
SELECT * FROM `product` 
SELECT * FROM `product` WHERE (`name`='') 
SELECT * FROM `product` WHERE (`name`='')
SELECT * FROM `product` WHERE (`id` IN (1, 2, 3, 4, 5, 6, 7, 89, 0)) AND (`name`='')
SELECT * FROM `product` WHERE (`id` IN (1, 2, 3, 4, 5, 6, 7, 89, 0)) AND (`name`='')

So you need to add all the above queries with two where conditions by default

create a file name Query inside the common/components and add the following,

Note: I have added conditions with hardcoded values for the columns like this [ 'application_id' => 1 ] , [ 'branch_id' => 1 ] replace them with the respective variables from the session before actually using it for testing purpose you can keep as is.I assume that you want the above two fields to be added with an and condition in the query.

<?php

namespace common\components;

use yii\db\Query as BaseQuery;

class Query extends BaseQuery {

    /**
     * 
     * @param type $condition
     * @param type $params
     * @return $this
     */
    public function where( $condition , $params = array() ) {
        parent::where ( $condition , $params );

        $defaultConditionEmpty = !isset ( $this->where[$this->from[0] . '.company_id'] );

        if ( $defaultConditionEmpty ) {
            if ( is_array ( $this->where ) && isset ( $this->where[0] ) && strcasecmp ( $this->where[0] , 'and' ) === 0 ) {
                $this->where = array_merge ( $this->where , [ [ $this->from[0] . '.company_id' => 1 ] , [ $this->from[0] . '.branch_id' => 1 ] ] );
            } else {
                $this->where = [ 'and' , $this->where , [ $this->from[0] . '.company_id' => 1 ] , [ $this->from[0] . '.branch_id' => 1 ] ];
            }
        }
        return $this;
    }

    /**
     * 
     * @param type $tables
     * @return $this
     */
    public function from( $tables ) {
        parent::from ( $tables );
        $this->addDefaultWhereCondition ();

        return $this;
    }

    /**
     * Private method to add the default where clause 
     */
    private function addDefaultWhereCondition() {
        if ( $this->from !== null ) {

            $this->where = [ 'and' ,
                [ $this->from[0] . '.company_id' => 1 ] , [ $this->from[0] . '.branch_id' => 1 ]
            ];
        }
    }

}

Now to test it create a test action inside your SiteController like below and access it

public function actionTest() {
        $q = new \common\components\Query();

        echo $q->from ( '{{product}}' )->createCommand ()->rawSql;
        echo "<br>";
        echo $q->select ( '*' )->from ( '{{product}}' )->createCommand ()->rawSql;
        echo "<br/>";
        echo $q->from ( '{{product}}' )->where ( [ 'name' => '' ] )->createCommand ()->rawSql;
        echo "<br>";
        echo $q->from ( '{{product}}' )->andWhere ( [ 'name' => '' ] )->createCommand ()->rawSql;
        echo "<br>";
        echo $q->select ( '*' )->from ( '{{product}}' )->where ( [ 'IN' , 'id' , [ 1 , 2 , 3 , 4 , 5 , 6 , 7 , 89 , 0 ] ] )->andwhere ( [ 'name' => '' ] )->createCommand ()->rawSql;

        echo "<br />";
        echo $q->select ( '*' )->from ( '{{product}}' )
                ->where ( [ 'and' ,
                    [ 'IN' , 'id' , [ 1 , 2 , 3 , 4 , 5 , 6 , 7 , 89 , 0 ] ] ,
                    [ 'name' => '' ]
                ] )
                ->createCommand ()->rawSql;
        return;
    }

Do not worry about the product table we need to check the query generated so we are not executing the query instead using ->createCommand()->rawSql to print the query built. so access the above action it should now print you the queries with both the columns added like below

SELECT * FROM `product` WHERE (`application_id`=1) AND (`branch_id`=1)
SELECT * FROM `product` WHERE (`application_id`=1) AND (`branch_id`=1)
SELECT * FROM `product` WHERE (`name`='') AND (`application_id`=1) AND (`branch_id`=1)
SELECT * FROM `product` WHERE (`application_id`=1) AND (`branch_id`=1) AND (`name`='')
SELECT * FROM `product` WHERE (`id` IN (1, 2, 3, 4, 5, 6, 7, 89, 0)) AND (`application_id`=1) AND (`branch_id`=1) AND (`name`='')
SELECT * FROM `product` WHERE (`id` IN (1, 2, 3, 4, 5, 6, 7, 89, 0)) AND (`name`='') AND (`application_id`=1) AND (`branch_id`=1)

Hope that helps you out or someone else looking for the same solution

EDIT

I updated the class above and added the fix to the queries using joins, that throws an error

Column 'company_id' in where clause is ambiguous

I have added the first table name available in the from array as all your tables have the field name and adding the condition for the first selected table will work as it would be joined with the next table with ON condition. And I have removed the select() trait override from the class as we won't be needing it.

Can you please help that how i can remove the ambiguous in where clause.waitng for your rsponse.. — rajwa766, Mar 15 '18 at 10:41
i could not understand what you meant by **ambiguous in where clause** @Sajid can you pleasae elaborate — Muhammad Omer Aslam, Mar 15 '18 at 14:44
sir the application_id and branch_id is in all the tables it is ok without join .the join query retrun ambigious error..its mean the query is confused wwith same attribut in both table return error — rajwa766, Mar 15 '18 at 18:18
hmm... let me test it with join and i will reply back @Sajid — Muhammad Omer Aslam, Mar 15 '18 at 18:21
i think we need to overide the core files of vendors .the ambigious error is always create the problem in normal search model also — rajwa766, Mar 15 '18 at 18:27
the search model has no link with it as it is a separate component and would effect only those queries that are using `common\components\Query` and not your normal search models. — Muhammad Omer Aslam, Mar 15 '18 at 18:32
i mean the ambigious error occur when we use the join in search model if there any attribute has same name,for that error we just use alias('a') and name the attribute on one table as a.id. — rajwa766, Mar 15 '18 at 18:53
The SQL being executed was: SELECT `users_level`.`display_name` AS `current_level` FROM `user` INNER JOIN `users_level` ON user.user_level_id = users_level.id WHERE (`user`.`id` = '6691BCEE-EFAF-4862-8C4D-8FC510B7547E') AND (`company_id`='F9E7025A-47B1-4D52-A73B-C1D83E0F4DD0') AND (`branch_id`='464C2FFD-B0BD-4C91-B482-34B1F82EEB42') Error Info: Array ( [0] => 23000 [1] => 1052 [2] => Column 'company_id' in where clause is ambiguous — rajwa766, Mar 15 '18 at 18:53
So in the condition of using `join` the ambiguity can be removed by using table name along with the field name and we can use the first table available in the `from` array , i will update the answer in a moment @Sajid — Muhammad Omer Aslam, Mar 15 '18 at 20:04
Now for all simple find method,i mean find() i am using this one — rajwa766, Mar 16 '18 at 06:25
public static function find() { return parent::find()->where(['=', 'company_id',Yii::$app->session['company_id']])->andWhere(['=','branch_id',Yii::$app->session['branch_id']]); } — rajwa766, Mar 16 '18 at 06:26
in all other models its working ok now the proble is when ever user vaidate for login it return validation flase becauser of this static method — rajwa766, Mar 16 '18 at 06:27
i think you should add a separate question for the issue so that the problem is segregated and a solution specific to this section should be suggested add the question with the related code and just add a message here for me i will look into it @Sajid — Muhammad Omer Aslam, Mar 16 '18 at 08:48
I have created the Question and tag you in the comment .i dont know exactily that u hae recived the notification or not due to name space there. here is the question link — rajwa766, Mar 16 '18 at 09:59
https://stackoverflow.com/questions/49317648/how-to-over-ride-the-find-method-for-each-model-in-yii2 — rajwa766, Mar 16 '18 at 09:59
thank you no problem sir..Its difficult to get help in this frame work.so thank u so much sir — rajwa766, Mar 16 '18 at 10:09

Yii2: How to override the yii\db\Query class to add default conditions with queries

1 Answers1