Get data on multiple tables grouped by hours

You just got 3 separeted collections. So if you merge these with

$merged = $user->merge($clicks);
$merged = $merged->merge($sales);

You can without problems order them by hour with:

$merged = $merged->sortBy('hour');

You should use the whole date for a correct sorting of items, and show only hour if you want. (At this time, it's useless you order items in your query, you already do it in your merged collection).

Hey @Estev this is the perfect use case for Redis Sorted Sets :)

Where ever in your code you want to increment the various stats add the following:

for visits I would use:
\Redis::command('zincrby', ['sset:stats:' . date('Ymd:H'), 1, 'visits']);
For a Click:
\Redis::command('zincrby', ['sset:stats:' . date('Ymd:H'), 1, 'click']);
For a Sale:
\Redis::command('zincrby', ['sset:stats:' . date('Ymd:H'), 1, 'sales']);

Then when you want the info just use the following:

Redis::command('zrange', ['sset:stats:20161014:10', 0, -1, 'withscores']);

Here is a dump from a quick play in Tinker:

>>> use \Redis;
=> null
>>> Redis::command('zincrby', ['sset:stats:' . date('Ymd:H'), 1, 'visits']);
=> "1"
>>> Redis::command('zincrby', ['sset:stats:' . date('Ymd:H'), 1, 'visits']);
=> "2"
>>> Redis::command('zincrby', ['sset:stats:' . date('Ymd:H'), 1, 'visits']);
=> "3"
>>> Redis::command('zincrby', ['sset:stats:' . date('Ymd:H'), 1, 'click']);
=> "1"
>>> Redis::command('zincrby', ['sset:stats:' . date('Ymd:H'), 1, 'click']);
=> "2"
>>> Redis::command('zincrby', ['sset:stats:' . date('Ymd:H'), 1, 'click']);
=> "3"
>>> Redis::command('zincrby', ['sset:stats:' . date('Ymd:H'), 1, 'visits']);
=> "4"
>>> Redis::command('zincrby', ['sset:stats:' . date('Ymd:H'), 1, 'visits']);
=> "5"
>>> Redis::command('zincrby', ['sset:stats:' . date('Ymd:H'), 1, 'sales']);
=> "1"
>>> Redis::command('zrange', ['sset:stats:' . date('Ymd:H'), 0, -1]);
=> [
     "sales",
     "click",
     "visits",
   ]
>>> Redis::command('zrange', ['sset:stats:' . date('Ymd:H'), 0, -1, 'withscores']);
=> [
     "sales" => "1",
     "click" => "3",
     "visits" => "5",
   ]
>>>

Advantages:

1. Lighting fast
2. Easy to implement
3. Doesn't break existing functionality.

You can leave them around as long as you like and you will have instant access to past stats as well as real time (e.g. by using the zrange command with the current date / hour). As I said, this is a perfect use case for adding Redis to your stack.

And here is your Bonus!!! To get Daily statistics, all you need to do is...

// Get a list of all the keys for Today - Real time! The keys command is a bit heavy, you could maintain a "LIST" with the stat keys.
$todaysKeys = Redis::command('keys', ['sset:stats:' . date('Ymd').':*']);
=> [ "sset:stats:20161014:11",
     "sset:stats:20161014:01" ]

// Merge the information required for the command to work; Note the different Key shape
$redisParams = array_merge(['sset:stats:'.date('Ymd').'-daily', count($todaysKeys) ], $todaysKeys);
=> [ "sset:stats:20161014-daily",
     2,
     "sset:stats:20161014:11",
     "sset:stats:20161014:01" ]

// Create the Daily statistics Sorted Set (the scores are summed)
>>> Redis::command('zunionstore', $redisParams);
=> 3

// Now get the daily stats...
Redis::command('zrange', ['sset:stats:'.date('Ymd').'-daily', 0, -1, 'withscores']);
=> [ "sales" => "1",
     "click" => "3",
     "visits" => "8" ]

@giovanniciriello Thank you, this is working. What is the best practice ? merge the 3 collections or doing it in one unique query ? Seems that the merging method is more readable.

@MarkLL This is really interesting, i will take a look. Thank you :)

You can obviously make a more complex query (with union syntax). And that's of course more performant then three queries and union with collection method "merge".

@giovanniciriello Do you have an example to do this 3 queries in one ? Everything i tried is returning me an error... Futur readers of this post would also be happy to see an example :) Many thanks

It would be like something like this in a raw query:

(SELECT count(id) as `visits`, created_at as `date` FROM visits)
UNION
(SELECT count(id) as `clicks`, created_at as `date` FROM clicks)
UNION
(SELECT count(id) as `sales`, created_at as `date` FROM sales)
ORDER BY date DESC;

Of course it' would easly written using the power of laravel query builder and union method :)

@giovanniciriello

$query = $visits
                ->select(DB::raw('count(id) as `visits`'),DB::raw('HOUR(created_at) hour'))
                ->union($clicks->select(DB::raw('count(id) as `clicks`'),DB::raw('HOUR(created_at) hour')))
                ->union($sales->select(DB::raw('count(id) as `sales`'),DB::raw('HOUR(created_at) hour')))
                ->groupby('hour')
                ->orderBy('hour')
                ->get();

This is returning duplicates data, it's not grouped like in the expected output.

{
{
    "sales": 0,
    "hour": 15
}

{
    "clicks": 12,
    "hour": 15
}
{
    "visits": 24
    "hour": 15
}
}

Maybe I couldn't understand exactly your needs, but try in this way:

$query = $visits
                ->select(DB::raw('count(id) as `visits`'),DB::raw('HOUR(created_at) hour'))
                ->union($clicks->select(DB::raw('count(id) as `clicks`'),DB::raw('HOUR(created_at) hour')))
                ->union($sales->select(DB::raw('count(id) as `sales`'),DB::raw('HOUR(created_at) hour')))
                ->groupby('hour')
                ->orderBy('hour')
                ->get()
        ->groupBy('hour')
        ->transform(function($item, $key){
            return $item->collapse();
        });

But at this point I advice you to use first solution in this discussion :)

Yes this is post treated by php like the first solution.

I think there should be a way to let sql do this job.

I think so too :)