Estev
477
10
Eloquent

Get data on multiple tables grouped by hours

Posted 2 years ago by Estev

Hi,

This is my expected output:

{
    {
        "visits": 10,
        "clicks": 2,
        "sales": 0,
        "hour": 15
    }

    {
        "visits": 24
        "clicks": 12,
        "sales": 1,
        "hour": 15
    }
}
... 

I'm thinking of 2 differents approach.

For now i can get the results with 3 queries :

    $user->$visits
                ->select(DB::raw('count(id) as `visits`'),DB::raw('HOUR(created_at) hour'))
                ->groupby('hour')
                ->orderBy('hour')
                ->get();

    $user->$clicks
                ->select(DB::raw('count(id) as `clicks`'),DB::raw('HOUR(created_at) hour'))
                ->groupby('hour')
                ->orderBy('hour')
                ->get();

    $user->$sales
                ->select(DB::raw('count(id) as `sales`'),DB::raw('HOUR(created_at) hour'))
                ->groupby('hour')
                ->orderBy('hour')
               ->get()

Then i can try to merge this 3 collections.

Or i could do this in one query but i'm not able to...

Any help ?

Thank you.

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

Reply to

Use Markdown with GitHub-flavored code blocks.