How to avoid Overriding in YII2 Query chaining

Question

I am trying to select some data from Db To a single Array These have multiple SELECT with respective WHERE conditions.

I am trying to chain Those Queries Into a single Array $bill_details.

$bill_details = BillDetails::find()->select('billdetails.typecode,sales_bill.created_date')
->leftJoin("sales_bill",'sales_bill.id = billdetails.bill_id')->groupby('billdetails.bill_id');
$bill_details->leftJoin("salesman",'salesman.id = sales_bill.salesman');
$bill_details->andFilterWhere(['>=','sales_bill.created_date',$fcreated_date]);
$bill_details->andFilterWhere(['<=','sales_bill.created_date',$tcreated_date]);
$bill_details->andFilterWhere(['=','sales_bill.salesman',$salesman]);

$bill_details->select('sum(billdetails.weight) AS gold_sale_weight,')->andWhere(['billdetails.typecode'=>'S'])->andWhere(['billdetails.metaltype'=>'gold']);

$bill_details->select('sum(billdetails.weight) AS gold_return_weight,')->andWhere(['billdetails.typecode'=>'R'])->andWhere(['billdetails.metaltype'=>'gold']);

$bill_details->select('sum(billdetails.weight) AS silver_sale_weight,')->andWhere(['billdetails.typecode'=>'S'])->andWhere(['billdetails.metaltype'=>'silver']);

$bill_details->select('sum(billdetails.weight) AS silver_return_weight,')->andWhere(['billdetails.typecode'=>'R'])->andWhere(['billdetails.metaltype'=>'silver']);

I displayed the SQLRawQUERY by

echo $bill_details->createCommand()->getRawsql();

in the last line,it gives the Query chain of LastSelected that is silver_return_weight

SELECT sum(billdetails.weight) AS silver_return_weight FROM billdetails LEFT JOIN sales_bill ON sales_bill.id = billdetails.bill_id LEFT JOIN salesman ON salesman.id = sales_bill.salesman WHERE ((((((((((sales_bill.created_date >= '2020-10-01') AND (sales_bill.created_date <= '2020-12-05')) AND (sales_bill.salesman = '3')) AND (billdetails.typecode='S')) AND (billdetails.metaltype='gold')) AND (billdetails.typecode='R')) AND (billdetails.metaltype='gold')) AND (billdetails.typecode='S')) AND (billdetails.metaltype='silver')) AND (billdetails.typecode='R')) AND (billdetails.metaltype='silver') GROUP BY billdetails.bill_id

SO that selection Override one by one.

How to overcome this..

Thanks,

Michal Hynčica Michal Hynčica · Accepted Answer · 2020-12-05T12:25:39

That's because select() method replaces the selected columns. If you want to add more columns instead of replacing you need to use addSelect(). If you use the addSelect do not put , after the column definition like you've done it here select('sum(billdetails.weight) AS gold_sale_weight,').

Another option is to use array in one select() call instead of multiple calls like this:

$bill_details = BillDetails::find()->select([
    'billdetails.typecode',
    'sales_bill.created_date',
    'gold_sale_weight' => 'sum(billdetails.weight)',
    // ... other columns
])

How to avoid Overriding in YII2 Query chaining

1 Answers