So, I need to add one more line to the grouped object that I get from the database, it will tell you how much user_ref_id_ * this user has. In addition, you need to understand that the lines in the Matrix table may be several to one user_id.
My task is that I need to select all the rows from the transaction table, add their values to the amount field and group by user_id. With this, I decided the question, but the bottom line is that I also need to go to the Matrix table for each user_id, there are rows user_ref_id_1, user_ref_id_2, user_ref_id_3, user_ref_id_4.
This sample code:
Transaction::
select('transactions.user_id as user_id','profile.*',
DB::raw('SUM(amount) as total_amount'))
->orderBy('total_amount', 'DESC')
->limit(10)
->join('profiles as profile', function ($join) {
$join->on('transactions.user_id', '=', 'profile.user_id');
})
->groupBy('transactions.user_id','profile.id')
->get();
In essence, according to the existing user_id, I need to go to the Matrix table and calculate how much user_ref_id_ * this user_id has, and it may also be that one user_id has several rows in this table. All day I break my head (((