I want to display chart data based on the results sum(value) as total_value from my query. why can't the total_value attribute be displayed on the chart? query:
$rooms2 = Room::query()
->join('notifications', 'notifications.room_id', '=', 'rooms.id')
->join('watt_histories', 'watt_histories.notification_id', '=', 'notifications.id')
->where('rooms.user_id', 1)
->whereIn('notifications.id', function ($query) {
$query->selectRaw('MAX(id)')
->from('notifications')
->groupBy('room_id');
})
->select([
'rooms.*',
'notifications.*',
'watt_histories.*',
DB::raw('(SELECT SUM(wh.value) FROM watt_histories AS wh INNER JOIN notifications AS n ON wh.notification_id=n.id WHERE n.room_id=notifications.room_id) as total_value'),
DB::raw('(SELECT SUM(wh.cost) FROM watt_histories AS wh INNER JOIN notifications AS n ON wh.notification_id=n.id WHERE n.room_id=notifications.room_id) as total_cost')
])
->groupBy('notifications.room_id')
->orderBy('watt_histories.created_at', 'desc')
->get();
how can I display the total value if I use a Laravel chart like this? code:
$charts = [];
foreach ($rooms2 as $room) {
$conditions = [];
$conditions[] = [
'condition' => "notification_id = {$room->notification_id}",
'color' => '',
'fill' => true,
];
$chart1 = [
'conditions' => $conditions,
'chart_title' => 'Energy Used ' . $room->room_name . ' by months',
'report_type' => 'group_by_date',
'model' => 'App\Models\WattHistory',
'group_by_field' => 'created_at',
'group_by_period' => 'day',
'aggregate_function' => 'sum',
'aggregate_field' => 'total_value',
'chart_type' => 'line',
'filter_field' => 'created_at',
'continuous_time' => true,
];
$chart2 = [
'conditions' => $conditions,
'chart_title' => 'Average Energy Used ' . $room->room_name . ' by months',
'report_type' => 'group_by_date',
'model' => 'App\Models\WattHistory',
'group_by_field' => 'created_at',
'group_by_period' => 'day',
'aggregate_function' => 'avg',
'aggregate_field' => 'total_value',
'chart_type' => 'line',
'filter_field' => 'created_at',
'continuous_time' => true,
];
$charts[$room->id] = new LaravelChart($chart1, $chart2);
}
for now this is the result. current result