laravel/pulse

Aggregation schema design for period and buckets

sts-ryan-holton opened this issue · 1 comments

sts-ryan-holton commented

Pulse Version

Latest

Laravel Version

Latest

PHP Version

Latest

Livewire Version

Latest

Database Driver & Version

No response

Description

Hi, I’m hoping that @jessarcher might be able to assist with this given that the behind the scenes for Pulse has been released on YouTube.

I’m trying to aggregate data in my application by day, hour and minute. I have three tables, one for daily aggregates, one for hourly and one for minutely.

The schema in each table is identical where there’s a unique index across several foreign id columns that link to other models, followed by an integer stat column, for example, “total_leads” and a date time column, where in each table this date time column would look like:

  • Daily table: 2024-01-01
  • Hourly table: 2024-01-01 13:00:00
  • Minutely table: 2024-01-01 13:01:00

The idea is that there would be one for for the day table, up to 24 rows for the hour table, and 1440 rows for the minute table.

each table is using ‘firstOrCreate’ to get the unique row and increment the data.

But found the problem that when filtering a complex UI that has a datetime picker “from” and “to” that it wouldn’t be able to sum rows across tables, for instance when going slightly over a window, like 13:00:00 to 14:05:00

Steps To Reproduce

I’m trying to fully understand then whether, in Pulse’s case, is this why everything is stored in the aggregates table, and the period column grabs all periods whereby the current datetime matches N, and how the timestamp is used with this and why not just a datetime used?

any advice / guidance?

crynobone commented

Hi there,

Thanks for reporting the problem you are encountering, but it looks like this is a question which may be better suited for a support channel. We only use this issue tracker for reporting bugs with the library itself. If you have a question on how to use functionality provided by this repository you can try one of the following channels:

However, this issue will not be locked and everyone is still free to discuss solutions to your problem!

Thanks.