How to manipulate query result into JSON-ready layout?

This is probably more of a PHP question than it is a Laravel-specific question - but here goes!

I'm trying to prepare the result of an Eloquent Query for use in a charting tool (in my case, Highcharts):

$rows = DB::table('pushups')
                ->join('users', 'pushups.user_id', '=', 'users.id')
                ->selectRaw('users.name as name, sum(pushups.amount) as amount, pushups.date as date')
                ->whereYear('datetime', '=', date('Y'))
                ->whereMonth('datetime', '=', date('m'))
                ->groupBy('name', 'date')
                ->get();

The result looks fine:

[{"name":"ace","amount":"100","date":"2018-04-16"},{"name":"ace","amount":"100","date":"2018-04-17"},{"name":"ace","amount":"100","date":"2018-04-18"},{"name":"bern","amount":"50","date":"2018-04-17"},{"name":"bern","amount":"100","date":"2018-04-18"}]

But now I'm trying to get it to look like this:

[{
name: "ace",
    data: [
        [Date.UTC(2018, 03, 16), 100],
        [Date.UTC(2018, 03, 17), 200],
        [Date.UTC(2018, 03, 18), 300],
        [Date.UTC(2018, 03, 19), 400],
    ]
}, {
name: "bern",
data: [
        [Date.UTC(2018, 03, 16), 100],
        [Date.UTC(2018, 03, 17), 150],
        [Date.UTC(2018, 03, 18), 170],
        [Date.UTC(2018, 03, 19), 190],
    ]
}, {
name: "chuck",
data: [
        [Date.UTC(2018, 03, 16), 100],
        [Date.UTC(2018, 03, 17), 200],
        [Date.UTC(2018, 03, 18), 250],
        [Date.UTC(2018, 03, 19), 300],
    ]}
 ]

I believe this is going to require nested loops? Just can't wrap my head around it at the moment. Any tips or help is greatly appreciated!

biishmar

8 years ago

Level 11

@tinybot if you have used eloquent model and relation instead of query builder, you would have got the result like u expected, or else foreach your result and create an new array and structure that new array like u expected and return he new array

tinybot

8 years ago

Level 2

Thanks @biishmar, I think you've put me on the right track!

Here's how I changed my approach to generating the JSON:

    $users = User::all();
    $chart = [];

    foreach ( $users as $user )
    {
        $pushups = $user->pushups->sortBy('datetime');
        $sum = 0;
        $data = [];

        foreach ( $pushups as $pushup ) 
        {
            if ( $pushup->datetime->year == date('Y') ) 
            {
                if ( $pushup->datetime->month == date('m') ) 
                {
                    $data[] = ['Date.UTC(' 
                                . $pushup->datetime->year . ', ' 
                                . ($pushup->datetime->month - 1) . ', ' 
                                . $pushup->datetime->day . ')', ($pushup->amount + $sum)];

                    $sum += $pushup->amount;
                }
            }
        }

        $chart[] = ['name' => $user->name, 'data' => $data];
    }

    return $chart;

Here's the data I'm getting:

[{"name":"ace","data":[["Date.UTC(2018, 3, 16)",100],["Date.UTC(2018, 3, 17)",200],["Date.UTC(2018, 3, 18)",300],["Date.UTC(2018, 3, 21)",388]]},{"name":"bern","data":[["Date.UTC(2018, 3, 17)",50],["Date.UTC(2018, 3, 18)",150]]}]

The problem I'm having now is that the date value "Date.UTC(2018, 3, 16)" has double quotes around it, since it's being treated as a string. However, I need the quotes removed, so that Javascript can properly process.

Any ideas on how to do this? Is there a simple way to exclude quotes from being included for a string value so that it can be treated as a Javascript object?

biishmar

8 years ago

Level 11

@tinybot without quotes its impossible but u can put double quotes to whole value "Date.UTC(2018, 3, 16), 100" like this and assign to javascript object .

Please or to participate in this conversation.