gianpiero.addis
1 year ago

How to structure the JSON response with selected values from a collection?

Posted 1 year ago by gianpiero.addis

I am performing the following query to retrieve the visits count for each patron category, all grouped by day (each visit is a database tuple):

LogbookEntry::whereMonth('visited_at', $month) // Just a dummy time constraint
    ->selectRaw('DAY(visited_at) as day, patron_category_id, count(*) as visits')
    ->with('patronCategory:id,name')
    ->groupBy('patron_category_id', 'day')
    ->orderBy('day')
    ->get()
    ->map(function ($entry) {
        return collect($entry->toArray())
        ->only(['day', 'visits', 'patron_category'])
        ->all();
    });

The response is structured as follows:

"data": {
    "visits": [
    {
        "day": 2,
        "visits": 12,
        "patron_category": {
            "id": 1,
            "name": "Students"
        }
    },
    {
        "day": 2,
        "visits": 35,
        "patron_category": {
            "id": 2,
            "name": "Faculty"
        }
    },
    {
        "day": 3,
        "visits": 8,
        "patron_category": {
            "id": 1,
            "name": "Students"
        }
    },
(etc.)

I'd rather have this:

"data": {
    "visits": [
        2: {
            "Students": 12,
            "Faculty": 35
        },
        3: {
            "Students": 8
        },
(etc.)

I am struggling with all possible collection methods but I just can't figure it out. Does someone have an idea?

Please sign in or create an account to participate in this conversation.