Question 238133 in Laravel Question posted in
The official Laravel Documentation can be found here.

How to query from mutiple tables and group them by DATE in laravel?

I want to get data from two tables and group them by date, like a daily report that shows data from two tables.

Example output:

Date Active User Total Deposit
2023-07-31 10 12500
2023-08-01 5 10000
2023-08-02 20 20000
2023-08-03 3 100

Migrations

users
-id
-created_at
...

deposits
-id
-user_id
-amount
-created_at

Models

users
public function deposits()
    {
        return $this->hasMany(Deposit::class, 'user_id');
    }

deposits
public function users()
    {
        return $this->belongsTo(User::class, 'user_id');
    }

Edit

What I’ve tried so far, I can query from single table and group them by date, but when it comes to joining two tables into a single query, I’m lost. I dont think I can use UNION or UNION ALL for this, neither LEFT JOIN or RIGHT JOIN as I want to get ALL data from the table USER and DEPOSIT.

Deposit::select(DB::raw("date(created_at) as date, SUM('amount') as total_deposit)->groupby('date)->orderBy('date')->get();

User::select(DB::raw("date(created_at) as date, count('*') as active_player->groupby('date)->orderby('date')->get();

Tags:

2

Answers


  1. 0

    I hope this could be helpfull to you.

    $combinedData = DB::table('users')
->select(
    DB::raw("DATE(users.created_at) as date"),
    DB::raw("COUNT(users.id) as active_players"),
    DB::raw("SUM(deposits.amount) as total_deposit")
)
->leftJoin('deposits', DB::raw("DATE(users.created_at)"), '=', DB::raw("DATE(deposits.created_at)"))
->groupBy(DB::raw("DATE(users.created_at)"))
->orderBy(DB::raw("DATE(users.created_at)"))
->get();
    Login or Signup to reply.
  2. 0

    I don’t think you need to join users at all here. You need to query only deposits and calculate distinct user_id values from the same table, without joining.

    Try this:

    Deposit::selectRaw("DATE(created_at) as date, COUNT(distinct user_id) as active_players, SUM(amount) as total_deposit")
  ->groupBy('date')
  ->orderBy('date')
  ->get();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search