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

Published 9 months 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?

Best Answer (As Selected By gianpiero.addis)
gianpiero.addis

Unfortunately this didn't work. However, I have finally found a solution working with foreach:

$entries = 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();

    $result = [];
    foreach ($entries as $entry) {
        $result[$entry->day][$entry->patronCategory->name] = $entry->visits;
    }

    return $result;

To allow for more flexibility, here is a better version:

public function scopeAggregateBy($builder, string $period)
{
    $allowedPeriods = ['month', 'day', 'hour'];

    if (!in_array($period, $allowedPeriods)) {
        return [];
    }

    $entries = $builder
        ->selectRaw("{$period}(visited_at) as {$period}, patron_category_id, count(*) as visits")
        ->with('patronCategory:id,name')
        ->groupBy('patron_category_id', $period)
        ->orderBy($period)
        ->get();

    $result = [];
    foreach ($entries as $entry) {
        $result[$entry->$period][$entry->patronCategory->name] = $entry->visits;
    }

    return $result;
}
Dry7
Dry7
9 months ago (151,880 XP)

@gianpiero.addis try like this

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();
    })->flatMap(function ($values) {
        $days = [];
        foreach ($values as $value) {
            if (!isset($days[$value->day)) { $days[$value->day] = []; }
            $days[$value->day][$value->patron_category->name] = $value->visits;
        }
        return $days;
    });
gianpiero.addis

Unfortunately this didn't work. However, I have finally found a solution working with foreach:

$entries = 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();

    $result = [];
    foreach ($entries as $entry) {
        $result[$entry->day][$entry->patronCategory->name] = $entry->visits;
    }

    return $result;

To allow for more flexibility, here is a better version:

public function scopeAggregateBy($builder, string $period)
{
    $allowedPeriods = ['month', 'day', 'hour'];

    if (!in_array($period, $allowedPeriods)) {
        return [];
    }

    $entries = $builder
        ->selectRaw("{$period}(visited_at) as {$period}, patron_category_id, count(*) as visits")
        ->with('patronCategory:id,name')
        ->groupBy('patron_category_id', $period)
        ->orderBy($period)
        ->get();

    $result = [];
    foreach ($entries as $entry) {
        $result[$entry->$period][$entry->patronCategory->name] = $entry->visits;
    }

    return $result;
}

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