public function veryTemp()
{
$page = Request::input('page', '1');
$bdate = Request::input('begindate'); // got from datepicker with correct format.
$edate = Request::input('enddate');
// rest of query
How to query the last 12 months worth of data? Instead of every month this year.
I need to make a query of the last 12 months OF DATA and sums the readings for each month so that I can output this to a chart. This is what I currently use now:
if (Utility_Meter_Readings::where('property_id', $property_id)->where('resource_type','1')->count() > 0) {
$huffdash_energydata = [];
$i=12;
while ($i >= 0){
$reading = Utility_Meter_Readings::where('property_id', $property_id)->where('resource_type','1')
->where('time','>=',Carbon::now()->startOfMonth()->subMonth($i)->timestamp)
->where('time','<=',Carbon::now()->endOfMonth()->subMonth($i)->timestamp)
->pluck('kwh')->sum();
// Convert KWH to GJ and add to array
array_push($huffdash_energydata, $reading*.0036);
$i--;
}
$monthly_formattedDates = [];
$i=12;
while ($i >= 0){
$reading = Carbon::today()->subMonths($i)->format('F');
array_push($monthly_formattedDates, $reading);
$i--;
}
return [$monthly_formattedDates, $huffdash_energydata];
}
This method is kinda bad because if there is now data for the last 6 months it's going to show a big gap in the charts.
I thought of doing an:
if ($reading ==0)
{
continue
}
I think I'm closer now I combined the two:
if (Utility_Meter_Readings::where('property_id', $property_id)->where('resource_type','1')->count() > 0) {
$huffdash_energydata = [];
$monthly_formattedDates = [];
$i=12;
while ($i >= 0){
$reading = Utility_Meter_Readings::where('property_id', $property_id)->where('resource_type','1')
->where('time','>=',Carbon::now()->startOfMonth()->subMonth($i)->timestamp)
->where('time','<=',Carbon::now()->endOfMonth()->subMonth($i)->timestamp)
->pluck('kwh')->sum();
// Convert KWH to GJ and add to array
if ($reading == 0){
continue;
}
// Convert KWH to GJ and add to array
array_push($huffdash_energydata, $reading*.0036);
$reading = Carbon::today()->subMonths($i)->format('F');
array_push($monthly_formattedDates, $reading);
$i--;
}
return [$monthly_formattedDates, $huffdash_energydata];
Nope this still does not help me.. I think I have to determine last month I do have data for and figure out how to count back from there.
@fbc So you want your results to begin with the month which has the latest data?
$lastestMonth = Utility_Meter_Readings::where('property_id', $property_id)->latest('time')->first()->time;
Then you can start working backwards. If time is already a carbon instance, you're ready to go. Otherwise you might need parse:
Carbon::parse($latestMonth)->startOfMonth()->subMonth($i)->timestamp
Please or to participate in this conversation.