Support aggregate functions

[yoelpc4]

Currently I can't find any clues about the aggregate / multi row functions i.e: AVG, COUNT, SUM, MAX, and MIN on docs.

Do you have any idea how to perform request & response on laravel json api for case unread notifications count?

[lindyhopchris]

There's nothing in the JSON API spec about aggregation data, so there's nothing in this package.

The way we'd do an unread notifications count is to use the pagination meta. If your notifications resource is paginated, and has a filter unread, then you could do:

GET /api/notifications?page[number]=1&page[size]=1&filter[unread]=true

And then the page meta would tell you how many notifications there are matching the filter.

[yoelpc4]

So in order to calculate let say average sales value of items foo and bar between years 2019 and 2020, we must utilize filter query param & pagination meta as the solution?

[lindyhopchris]

Well it's up to you as to what is the best solution for your use case.

For that scenario they'd be two approaches that I can think of.

The first would be to download the data into the client and calculate the average in the client.

The second would be to have a JSON API resource that contains the aggregate values. E.g. a foo-stats resource, whose attributes contain the aggregate values about the foo resource. That would probably be the most efficient way of doing it, particularly for large datasets.

[TristanKobalt]

I have a similar problem, where I want to expose opening hours for specific dates as a calculated resource (based on default opening hours and exceptions to those opening hours which both reside in the database and have Eloquent models).

I'm considering taking the second route you suggested above and offering this as a readonly resource, using the dates as the ID's.

I'm a bit lost on how I should go about this however. I'm assuming that OpeningHours could/should be a plain PHP class with its own non-Eloquent Schema, and that I would also need a custom non-eloquent Adapter? I'm stuck on which Adapter methods I should implement for this (and how), and on how to support pagination for my custom Adapter.

Does there happen to be an example of something like the foo-stats resource or another calculated resource somewhere?

[lindyhopchris]

There's an example of a non-Eloquent resource in the tests, which would probably be the best thing to look at: https://github.com/cloudcreativity/laravel-json-api/tree/develop/tests/dummy/app/JsonApi/Sites

[yoelpc4]

Hi there, I have an approach that can be proposed as a solution after reading this post https://stitcher.io/blog/eloquent-mysql-views.

In order to use JSON API features (pagination, sort, filter, include, & fields) with aggregate resource along with this package, we must create view in migration and eloquent model to represents the aggregate resource using this view instead of table.

JSON API also states that we must provide a single & unique identification of resource https://jsonapi.org/format/#document-resource-object-identification. To fulfill this requirement we can add ROW_NUMBER() OVER(ORDER BY column_name) id as the resource id on the migration.

Things to keep in mind, any model using view instead of table becomes readonly in other word we can't perform create, update, or delete record(s) on this model.

I don't think my approach is the best for this kind of issue, but at the time I wrote this comment this approach worked like a charm for me. @lindyhopchris if you think this is the right solution, you can mention me for any future aggregate related question to this issue.