Laravel Chart can't show attribute value from query

16 views Asked by At

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

0

There are 0 answers