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 anand
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.