As well as totals per period, it would be good to group data by the cumulative total until that period. e.g. total users over time per period, as well as total new users per period.

Having the following code:

        $data = Trend::model(User::class)
            ->between(now()->subWeek(), now()->endOfDay())
            ->perDay()
            ->count();

Generates the error:

Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

Hello @Larsklopstra!

This PR allows the package to be installed in Laravel 9. Additionally, it updates the test suite for running on PHP 8.1 and Laravel 9.

The minimum required version of Laravel is now 8.71 (because early versions of Laravel 8 don't support PHP 8.1).

Resolves: #5

Hey,

First of all thx for the great package.

I'm upgrading some Laravel applications that use this package to Laravel 9. but when i run composer update it triggers soms errors.

Error:

Your requirements could not be resolved to an installable set of packages.

  Problem 1
    - Root composer.json requires flowframe/laravel-trend ^0.0.2 -> satisfiable by flowframe/laravel-trend[v0.0.2].
    - Conclusion: don't install laravel/framework v9.0.0-beta.2 (conflict analysis result)
    - Conclusion: don't install laravel/framework v9.0.0-beta.3 (conflict analysis result)
    - Conclusion: don't install laravel/framework v9.0.0-beta.4 (conflict analysis result)
    - Conclusion: don't install laravel/framework v9.0.0-beta.5 (conflict analysis result)
    - Conclusion: don't install laravel/framework v9.0.0 (conflict analysis result)
    - Conclusion: don't install laravel/framework v9.0.1 (conflict analysis result)
    - Conclusion: don't install laravel/framework v9.0.0-beta.1 (conflict analysis result)
    - flowframe/laravel-trend v0.0.2 requires illuminate/contracts ^8.37 -> satisfiable by illuminate/contracts[v8.37.0, ..., 8.x-dev].
    - Only one of these can be installed: illuminate/contracts[dev-master, v5.4.0, ..., 5.8.x-dev, v6.0.0, ..., 6.x-dev, v7.0.0, ..., 7.x-dev, v8.0.0, ..., 8.x-dev, v9.0.0-beta.1, ..., 9.x-dev], laravel/framework[v9.0.0-beta.1, ..., 9.x-dev]. laravel/framework replaces illuminate/contracts and thus cannot coexist with it.
    - Root composer.json requires laravel/framework ^9.0 -> satisfiable by laravel/framework[v9.0.0-beta.1, ..., 9.x-dev].

Use the option --with-all-dependencies (-W) to allow upgrades, downgrades and removals for packages currently locked to specific versions.

There's something weird going on with sorting. Because we're also support perYear we can't use integers as keys, that's why we use data objects. However, they're not being sorted after the data and placeholder data has been merged.

The proposed solutions are:

• Rethink merging strategy
• Do another sort on the collection
• Ditch perYear

I have a model for transactions that includes these two fields:

• date: when did the transaction take place
• created_at: when was the record created

This results in an error message:

Illuminate\Database\QueryException  SQLSTATE[42000]: 
Syntax error or access violation: 1055 'myapp.transactions.created_at' isn't in GROUP BY

Using e.g.

Flowframe\Trend\Trend::model(Transaction::class)
->between(start: now()
->subMonths(1), end: now())
->perMonth()
->count()

which "expands" to:

select 
  date_format(created_at, '%Y-%m') as date, -- this alias conflicts with the table's column `date`
  count(*) as aggregate
from `transactions` 
where `created_at` between '2022-10-09 18:48:11' and '2022-11-09 18:48:11'
group by `date` 
order by `date` asc

It seems the date_format(...) as date conflicts with the table column date.

The above works fine for a model without a date column.

Related: can the field to group by be customized? I don't want to group by created_at but group by the table's column date instead.

The method convertTimezone adds the ability to aggregate in a different timezone than the timezone of the database (closes #19):

$trend = Trend::model(...)
    ->between(...)
    ->perMonth()
    ->convertTimezone(
        from: 'UTC',
        to: 'Europe/Amsterdam',
    )
    ->count();

Unfortunately, I couldn't find a way to convert the timezone for SQLite and therefore implemented an error message in that case. Additionally, I wasn't able to test these changes in pgSQL. Could please somebody verify the changes on a pgSQL database?

The new placeholders method allows to get the placeholders as a collection in a custom date format for further use in diagrams and reports:

$trend = Trend::model(...)
    ->between(...)
    ->perDay();

$placeholders = $trend->placeholders('D');
$totals = $trend->count();

Please let me know if a can support you in any way to merge these changes and thanks for your work. :tada:

Problem description

When trying to retrieve unique attendees of an event. I'm using this query:

Attendee::query()->distinct('user_id')

It works on its own but when using it in this chart widget;

Trend::query(Attendee::query()->distinct('user_id'))
            ->between(
                start: now()->setDate(2021, 1, 1),
                end: now(),
            )
            ->perMonth()
            ->count();

I get this error:

SQLSTATE[42P10]: Invalid column reference: 7 ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions LINE 1: select distinct on ("user_id") ^ (SQL: select distinct on ("user_id") to_char(created_at, 'YYYY-MM') as date, count(*) as aggregate from "attendees" where "created_at" between 2021-01-01 16:43:04 and 2022-10-14 16:43:04 group by "date" order by "date" asc)

Sometimes, we store timestamp in database as UNIX Timestamp not Y-m-d H:i:s format, so I create betweenTimestamp() function to support it and do some adjustment in Adapters and Trend class.