Your Location is: Home > Php

How to get the data Using Yii2 Query Builder

From: Italy View: 4896 code-droid 

Question

What i am trying I have 2 table staff and salarydetails,i want to fetch staff name and there respective salary

   $salaries = SalaryDetails::find()->select('salary_details.total_salary AS salary,staff.name AS name')
            ->leftJoin("staff",'salary_details.staff_id = staff.id');
        
            


                if ($fcreated_date != null && $tcreated_date != null) {
                    $salaries->andFilterWhere(['>=','salary_details.salary_given_date', $fcreated_date]);   
                    $salaries->andFilterWhere(['<=', 'salary_details.salary_given_date', $tcreated_date]);
                }

when i try to print sql raw query by using

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

i got SELECT salary_details.total_salary AS salary, staff.name AS name FROM salary_details LEFT JOIN staff ON salary_details.staff_id = staff.id and this query gives the data i wanted in phpMyadmin

But the array gives ie, print_r($salaries); gives

yii\db\ActiveQuery Object ( [sql] => [on] => [joinWith] => [select] => Array ( [0] => salary_details.total_salary AS salary [1] => staff.name AS name ) [selectOption] => [distinct] => [from] => [groupBy] => [join] => Array ( [0] => Array ( [0] => LEFT JOIN [1] => staff [2] => salary_details.staff_id = staff.id ) ) [having] => [union] => [params] => Array ( ) [_events:yii\base\Component:private] => Array ( ) [_behaviors:yii\base\Component:private] => Array ( ) [where] => [limit] => [offset] => [orderBy] => [indexBy] => [modelClass] => common\models\SalaryDetails [with] => [asArray] => [multiple] => [primaryModel] => [link] => [via] => [inverseOf] => )

Thanks,

Best answer

As you can see in your print_r, $salaries is an ActiveQuery instance. Call the methods ->asArray()->all() on it to get the records.

$query = SalaryDetails::find()
    ->select('salary_details.total_salary AS salary,staff.name AS name')
    ->leftJoin("staff",'salary_details.staff_id = staff.id');
if ($fcreated_date != null && $tcreated_date != null) {
    $query->andFilterWhere(['>=','salary_details.salary_given_date', $fcreated_date]);   
    $query->andFilterWhere(['<=', 'salary_details.salary_given_date', $tcreated_date]);
}
$salaries = $query->asArray()->all();

Note that without asArray the all would return an array of SalaryDetails records and you would loss the staff name.