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();
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 anandcondition 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.