Avoiding multiple joins

Yii 2.0 General Discussions
1

Hi

I have a complex form which can result in up to 5 table joins. I am using \yii\db\Query->leftJoin() which does joins fine but if I join the same table twice it blows up.

What is the best way to check if a join exists in the query already other than building my own map?

Thanks

2

Blows up?

3

Sorry, some code would help.

Error is SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: ‘o’

As you can see below, I’m doing




$query->leftJoin('order o', 'o.customer_id = cu.id');

twice.




		// products purchased

		$product = \yii\helpers\ArrayHelper::getValue($params, 'product', null);

		if (empty($product) === false) {

			$query->addSelect('oi.name');

			$query->leftJoin('order o', 'o.customer_id = cu.id');

			$query->leftJoin('order_item oi', 'oi.order_id = o.id');

			$query->andWhere(['like' , 'oi.name' , $product]);

		}


		// min/max spend

		$minSpend = \yii\helpers\ArrayHelper::getValue($params, 'minSpend', null);

		if (empty($minSpend) === false) {

			$query->addSelect('SUM(oi.price) AS spend');

			$query->addSelect('oi.name');

			$query->leftJoin('order o', 'o.customer_id = cu.id');

			$query->leftJoin('order_item oi', 'oi.order_id = o.id');

			$query->having([ '>', 'spend', $minSpend ]);

		}
4

I’ve thought about extending Query adding a addJoin method that does something like




(pseudocode)

public function addJoin($table, $on = '', $params = [])

{

if (in_array($this->joins, md5(serialize(function_get_args()) === true)

{

don't add the join

} else {


add the join




}

but I think there must be an easier way

5 



$product = \yii\helpers\ArrayHelper::getValue($params, 'product', null);

$minSpend = \yii\helpers\ArrayHelper::getValue($params, 'minSpend', null);


if (!empty($product) || !empty($minSpend)) {

    $query->addSelect('oi.name');

    $query->leftJoin('order o', 'o.customer_id = cu.id');

    $query->leftJoin('order_item oi', 'oi.order_id = o.id');

}


if (!empty($product)) {

    $query->andWhere(['like' , 'oi.name' , $product]);

}


if (!empty($minSpend)) {

    $query->addSelect('SUM(oi.price) AS spend');    

    $query->having([ '>', 'spend', $minSpend ]);
6

Thanks, understand the approach but there are around 10 parameters and the conditions for adding joins will have dependencies.

I’ve followed similar logic though, array of params that require certain joins is checked before adding select.

Thanks for your help.