Naeem Marzu Naeem Marzu - 2 months ago 10
MySQL Question

yii2 : add IFNULL exception to Command

i've create command and it works fine but when I add IFNull exception it add a comma and i searched a lot there is no answer my code :

public function actionTotal($id)
{
$query1 = new Query;
$query1 ->select(' sum(patient_services.price) price ,
sum( IFNULL(receipts.price,0)) receipts')
->from('patient_services')
->leftJoin('receipts', 'patient_services.patient_id = receipts.patient_id')
->where('patient_services.patient_id=:id', array(':id'=>$id));
$command1 = $query1->createCommand();
$price = $command1->queryAll();
echo Json::encode($price);
}


when i try it ... the select code have a comma and idon't know how to remove it

SELECT sum(patient_services.price) price, sum( IFNULL(receipts.price, `0))` AS `receipts` FROM `patient_services` LEFT JOIN `receipts` ON patient_services.patient_id = receipts.patient_id WHERE patient_services.patient_id=2


enter image description here

Answer

In you code

   $query1  ->select('sum(patient_services.price) price
                 ,sum( IFNULL(receipts.price,)) receipts')
                                            ^^ here is missing the value for ifnull 
                                               eg: ifnull(your_column, 0);
     ->from('patient_services')
     ->leftJoin('receipts', 'patient_services.patient_id = receipts.patient_id')
     ->where('patient_services.patient_id=:id', array(':id'=>$id));        

then try

 $query1  ->select(' sum(patient_services.price) price ,
                    sum( IFNULL(receipts.price,0)) receipts') 
     ->from('patient_services')
     ->leftJoin('receipts', 'patient_services.patient_id = receipts.patient_id')
     ->where('patient_services.patient_id=:id', array(':id'=>$id)); 

looking at the strange result in your img

try using this this notation and (remove also the two space between query1 and ->

$query1->select(["sum(patient_services.price) AS price", 
     "sum( IFNULL(receipts.price,0)) AS receipts"]  )

And eventually try to clear runtime directory ..and flush the db cache

Comments