flarpy
(Peter)
November 19, 2014, 5:34pm
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
flarpy
(Peter)
November 20, 2014, 9:59am
3
samdark:
Blows up?
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 ]);
}
flarpy
(Peter)
November 20, 2014, 10:02am
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
samdark
(Alexander Makarov)
November 20, 2014, 1:17pm
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 ]);
flarpy
(Peter)
November 20, 2014, 5:23pm
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.