从现有和未存在的数据创建csv文件

I'm really happy to meet you guys. you're awesome!

So, i got a problem with query builder on laravel. In fact, i would like to create a csv file with non-existing data. I make a request with a timestamp and some data doesn't exist before. To create my csv i need to get the headers first with this function:

public static function getHeaders($sTables) {


    $data = DB::connection('mysql2')
            ->table('data_sub_category')
            ->select('data_sub_category.designation as sub', 'data_key.designation as dkey')
            ->join('data_key', 'data_key.id_data_sub_category', '=', 'data_sub_category.id')
            ->whereIn('data_sub_category.id', $sTables)
            ->orderBy('data_sub_category.id', 'ASC')
            ->distinct()
            ->get();

    return $data;
}

And to get the data, I use this:

 public static function prepare_data_export($iIdCodeTransmetteur, $sTables, $dtStart, $dtEnd) {

    $data = DB::connection('mysql2')
            ->table('data_row')
            ->select('data_sub_category.designation as sub_d','data_sub_category.id as sub_id', 'data_key.designation as key_d','data_key.id as key_id','data_row.id_data as id_data', 'data_row.value', DB::raw("(DATE_FORMAT(FROM_UNIXTIME(timestamp), '%d/%m/%Y %h:%i:%s')) as timestamp"))
            ->join('data', 'data.id', '=', 'data_row.id_data')
            ->join('data_key', 'data_key.id', '=', 'data_row.id_data_key')
            ->join('data_sub_category', 'data_sub_category.id', '=', 'data_row.id_data_sub_category')
            ->join('data_category', 'data_category.id', '=', 'data_row.id_data_category')
            ->where('code_unique', $iIdCodeTransmetteur)
            ->whereIn('data_sub_category.id', $sTables)
            ->whereBetween('data.timestamp', array($dtStart, $dtEnd))
            ->orderBy('data.timestamp', 'ASC')
            ->orderBy('data_sub_category.id', 'ASC')
            ->orderBy('data_key.id','ASC')
            ->get();

    return $data;
}

this 2 function are in variables called:

    $getHeaders = Data::getHeaders($sTables);
    $results = Data::prepare_data_export($iIdCodeTransmetteur, $sTables, $dtStart, $dtEnd)->groupBy('id_data')->toArray();

Then, I create the csv file, line by line, putting the values one after another, separated by a ','. At the end, the csv file looks like this:

public function ajaxCreateCsv(Request $request) {
   ini_set('memory_limit', '-1');
   ini_set('max_execution_time', 900000); //15 minutes


   $bError = false;
   $messages_errors = array();
   $isSuccess = false;

   $aTables = $request->tables;
   $dtStart = $request->dtStart;
   $dtEnd = $request->dtEnd;
   $iIdCodeTransmetteur = $request->code_transmetteur;

   $sTables = [];

   /*
     for ($i=0; $i < count($dataRow); $i++) {
     $sRow .= $dataRow[$i]['value'] . ',';
     }
     $sRow = substr($sRow, 0, -1);
    */

   if ($aTables) {
       for ($i = 0; $i < count($aTables); $i++) {
           array_push($sTables, intval($aTables[$i]['value']));
       }
   } else {
       $aTables = Data::getAllGroupDataByIidForAjax($iIdCodeTransmetteur);

       for ($i = 0; $i < count($aTables); $i++) {
           array_push($sTables, intval($aTables[$i]->id));
       }
   }


   $getHeaders = Data::getHeaders($sTables);
   $result = Data::prepare_data_export($iIdCodeTransmetteur, $sTables, $dtStart, $dtEnd);
   $line = 'Date de réception, ';
   $last_timestamp = '01/01/2018 00:00:00';


   if (count($result) < 1) {
       $response_array['status'] = 'error';
       $error_message = $error_message . "Aucune données disponible pour ces dates";
       array_push($messages_errors, ["date", $error_message]);
   } else {

       foreach ($getHeaders as $key) {
           $array = $key->sub . '.' . $key->dkey;
           $line .= $array . ", ";
       }
       $line = substr($line, 0, -1);

       foreach ($result as $key) {
           $datetime = $key->timestamp;
           $value = $key->value;

           if ($datetime == $last_timestamp) {
               if ($value == NULL) {
                   $value = "NULL";
               }
               $line .= $value . ", ";
               $last_timestamp = $datetime;
           } else {
               $line = substr($line, 0, -1);
               $line .= "
";
               $line .= $datetime . ", ";
               if ($value == NULL) {
                   $value = "NULL";
               }
               $line .= $value . ", ";
               $last_timestamp = $datetime;
           }
       }
       $line = substr($line, 0, -1);

       $isSuccess = true;
   }

   if ($isSuccess == false) {
       $response_array['status'] = 'error';
       $response_array['messages'] = $messages_errors;
   } else {
       file_put_contents("file.csv", $line);
       $response_array['status'] = 'success';
   }

   return response()->json($response_array);

The output csv file with data

Now, the file looks great and all, and for most of it, you are right. Although, at the end of the line, some columns are empty. That is because in the database, for a precise timestamp, some data are non-existing

The problem:

the data_key receive apparent_wind_speed, true_wind_speed and water_speed from the request getHeader but for now we have no value for them. So, my csv doesn't create colums with the right values.

id of values for two different timestamps, missing 92 to 94

I can't find a way to fill the values, either in the csv file or the database, to get a "null" value where the value doesn't exist. I tried to use a LEFT JOIN in my request to force MySQL to run through all the ids, even the missing ones, but it can't seem to work.

I hope you can find a way to help, I'll keep working on that and update the request.