Question 16480 in Laravel Question posted in
The official Laravel Documentation can be found here.

How to get data from null in month – laravel

I trying to get the data in mysql database, so i want to get all of the data even the date field is null, this is my code

$prod = DB::table('daily_enters')
                ->leftJoin('daily_inputs', 'daily_enters.daily_input_id', '=', 'daily_inputs.id')
                ->select('daily_inputs.date',DB::raw('sum(daily_enters.crops) as crops_actual'))
                ->whereIn('daily_inputs.date', $dates)
                ->where('daily_inputs.divisi', 'A')
                ->groupBy('daily_inputs.date','daily_inputs.divisi')
                ->get();

this the result

^ IlluminateSupportCollection {#1403 ▼
  #items: array:30 [▼
    0 => {#1402 ▼
      +"date": "2022-01-02"
      +"crops_actual": 353.91
    }
    1 => {#1407 ▼
      +"date": "2022-01-03"
      +"crops_actual": 465.2
    }
    2 => {#1405 ▶}
    3 => {#1406 ▶}
    4 => {#1410 ▶}
    5 => {#1408 ▶}
    6 => {#1409 ▶}
    7 => {#1412 ▶}
    8 => {#1413 ▶}
    9 => {#1411 ▶}
    10 => {#1414 ▶}
    11 => {#1415 ▶}
    12 => {#1416 ▶}
    13 => {#1417 ▶}
    14 => {#1418 ▶}
    15 => {#1419 ▶}
    16 => {#1420 ▶}
    17 => {#1421 ▶}
    18 => {#1422 ▶}
    19 => {#1423 ▶}
    20 => {#1424 ▶}
    21 => {#1425 ▶}
    22 => {#1426 ▶}
    23 => {#1427 ▶}
    24 => {#1428 ▶}
    25 => {#1429 ▶}
    26 => {#1430 ▶}
    27 => {#1431 ▶}
    28 => {#1432 ▶}
    29 => {#1433 ▶}
  ]
  #escapeWhenCastingToString: false
}

the data is return for 31 days in month but in 2022-01-01 is not found because its not have a data, but in this case i want to show the ‘2022-01-01’ with data return = 0

so if you an any idea for this issue, please tell me the correct way for solve that issue. thank you

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    this is the another way that i was trying

      $dateRange = CarbonPeriod::create($startDay, $endDay);
        foreach ($dateRange as $dt) {
            $dates[] = $dt->format('Y-m-d');
            $days[] = $dt->format('d');
        }
        $results = [];
        foreach ($dates as $date) {

            $prod_alls = DB::table('daily_enters_new')
                ->leftJoin('daily_inputs_new', 'daily_enters_new.daily_input_new_id', '=', 'daily_inputs_new.id')
                ->select('daily_inputs_new.sub_division', 'daily_inputs_new.date', DB::raw('sum(daily_enters_new.crops) as crops_actual'))
                ->whereDate('daily_inputs_new.date', $date)
                ->groupBy('daily_inputs_new.sub_division', 'daily_inputs_new.date')
                ->orderBy('daily_inputs_new.date', 'ASC')
                ->pluck('crops_actual');
            if ($prod_all) {
                $results[] = array($date => $prod_alls);
            } else {
                $results[] = array($date => 0);
            }
        }

    This is the result

    ^ array:31 [▼
  0 => array:1 [▼
    "2023-01-01" => IlluminateSupportCollection {#1477 ▼
      #items: []
      #escapeWhenCastingToString: false
    }
  ]
  1 => array:1 [▼
    "2023-01-02" => IlluminateSupportCollection {#1474 ▼
      #items: array:1 [▼
        0 => 372.27
      ]
      #escapeWhenCastingToString: false
    }
  ]
  2 => array:1 [▼
    "2023-01-03" => IlluminateSupportCollection {#1482 ▼
      #items: array:1 [▼
        0 => 370.67
      ]
      #escapeWhenCastingToString: false
    }
  ]
  3 => array:1 [▶]
  4 => array:1 [▶]
  5 => array:1 [▶]
  6 => array:1 [▶]
  7 => array:1 [▶]
  8 => array:1 [▶]
  9 => array:1 [▶]
  10 => array:1 [▶]
  11 => array:1 [▶]
  12 => array:1 [▶]
  13 => array:1 [▶]
  14 => array:1 [▶]
  15 => array:1 [▶]
  16 => array:1 [▶]
  17 => array:1 [▶]
  18 => array:1 [▶]
  19 => array:1 [▶]
  20 => array:1 [▶]
  21 => array:1 [▶]
  22 => array:1 [▶]
  23 => array:1 [▶]
  24 => array:1 [▶]
  25 => array:1 [▶]
  26 => array:1 [▶]
  27 => array:1 [▶]
  28 => array:1 [▶]
  29 => array:1 [▶]
  30 => array:1 [▶]
]

    this results that what i want, but the question is how to foreach that array into blade?


  2. 0

    I think best if you let laravel handle the date range, for example using

    $dateRange = CarbonPeriod::create($startDate, $endDate)->toArray();

    And with the existing query, you get add ->get()->keyBy('daily_inputs.date');

    After that, just looping through $dateRange

    foreach ($dateRange as $range) {
   $date = $range->format('Y-m-d');
   var_dump(isset($prod[$date]) ? $prod[$date] : null)
}

    Hope this can help you.

    NOTE:
    as mention by @takumabo, you can also use laravel helper data_get()

    $item = data_get($prod, $date);
var_dump($item ?? null)

    and if daily_inputs.date type is datetime you can format it when used as key

    ->get()->keyBy(function ($record) { 
  return $record->date->format('Y-m-d');
});
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search