I need to combine the sql2 into the first sql.How can i combine the second array into first array ?I'm trying to make a second query because i cant join the table inside the first query because the data will not accurate.

Below is the first query

> $sql = "SELECT m.id,ms.duration,ms.item_code, m.machine_tag,m.mac_address,mss.data,mss.date_time_reading,mss.variable
> FROM machine_subscription ms
> JOIN machine_status mss ON mss.machine_id = ms.machine_id
> INNER JOIN machine m ON m.id = ms.machine_id
> WHERE ms.id IN (" . implode(',', $max_ids) . ") AND DATE(mss.date_time_reading) = '" . date('Y-m-d') . "'
> ORDER BY ms.id DESC ";
> $machine_data = Yii::$app->db->createCommand($sql)->queryAll();
   

Below is second query

> $sql2 = "SELECT m.id,ms.duration,ms.item_code, m.machine_tag,m.mac_address,ld.description
> FROM machine_subscription ms
> JOIN log_dispense ld ON ld.machine_id = ms.machine_id
> INNER JOIN machine m ON m.id = ms.machine_id
> WHERE ms.id IN (" . implode(',', $max_ids) . ") 
> ORDER BY ms.id DESC ";
> $machine_description = Yii::$app->db->createCommand($sql2)->queryAll();
   

Looping to get the data of the machine

> $machine_key = [];
> $count_idle = $count_sale = $count_total = $count_completed = $count_failed = $count_description = [];
> if ($machine_data) {
> foreach ($machine_data as $machine_row) {
>                 $machine_key[$machine_row["id"]][] = $machine_row;
>             }
> 
> 
> 
> foreach ($machine_key as $machine_id => $m_data) {
> foreach ($m_data as $data_row) {
> if ($data_row["variable"] == 'status') {
> if (strpos($data_row["data"], "sale") !== false) {
> if (!isset($count_sale[$machine_id])) {
>                             $count_sale[$machine_id] = 1;
> else {
>                             $count_sale[$machine_id]  ;
>                         }
>                     }
> if (strpos($data_row["data"], "idle") !== false) {
> if (!isset($count_idle[$machine_id])) {
>                             $count_idle[$machine_id] = 1;
> else {
>                             $count_idle[$machine_id]  ;
>                         }
>                     }
> if (!isset($count_total[$machine_id])) {
>                         $count_total[$machine_id] = 1;
> else {
>                         $count_total[$machine_id]  ;
>                     }
> 
> 
>                 }
> 
>         }

I need to combine sql2 (second query)and loop into the master_array to get the array_description_completed and array_description_failed

> $master_array = [];
> foreach ($machine_key as $mid => $mac_data) {
>             $master_array[] = [
>                 'id' => $mid,
>                 'machine_tag' => $mac_data[0]["machine_tag"],
>                 'mac_address' => $mac_data[0]["mac_address"],
>                 'duration' => $mac_data[0]["duration"],
>                 'item_code' => $mac_data[0]["item_code"],
>                 'array_status_idle' => isset($count_idle[$mid]) ? $count_idle[$mid] : 0,
>                 'array_status_sale' => isset($count_sale[$mid]) ? $count_sale[$mid] : 0,
>                 'array_status_total' => isset($count_total[$mid]) ? $count_total[$mid] : 0,
>                 'array_description_completed' => isset($count_completed[$mid]) ? $count_completed[$mid] : 0,
>                 'array_description_failed' => isset($count_failed[$mid]) ? $count_failed[$mid] :0,
>             ];
>         }

I want the result become like below image

But the array_description_completed and array_description_failed must have value.Can someone help me how to loop it into array and combine into the $master_array?

CodePudding user response：

To combine the second query/SQL into an ArrayDataProvider in Yii2, you can follow these steps:

Execute the second query using Yii2's Query Builder or Active Record. This will return an array of rows that represent the results of the query.

Create an ArrayDataProvider object and pass the array of rows as the first argument. You can also pass in additional configuration options, such as the key field and the pagination settings.

Here is an example of how you can combine the second query into an ArrayDataProvider:

    $rows = Yii::$app->db->createCommand($secondQuery)->queryAll();
$dataProvider = new ArrayDataProvider([
    'allModels' => $rows,
    'key' => 'id', // specify the column that should be used as the key for each row
    'pagination' => [
        'pageSize' => 20,
    ],
]);

This will create an ArrayDataProvider object that contains the results of the second query and is configured with a page size of 20. You can then use this ArrayDataProvider object in your Yii2 application, such as in a grid view or list view, to display the data.

CodePudding user response：

I already solve it,just do like the first query and loop it.