Laravel将数据计数附加到数据库对象

Question

Hello i have this function

public function readData(){
$TableB1 = \DB::table('users')
->join('group_user', 'users.id', '=', 'group_user.user_id')
->join('groups', 'groups.id', '=', 'group_user.group_id')
->join('meetings', 'users.id', '=', 'meetings.owned_by_id')
->select(
   'users.name as name',
   'group_user.user_id as id',
   'groups.name as groupname',
    )
->get();
foreach ($TableB1 as &$data){
    $meetings = \DB::table('meetings')
                ->where('company_id', 1)->where('owned_by_id', $data->id)
                ->where(\DB::raw('MONTH(created_at)'), Carbon::today()->month);
    echo $meetings->count();
}

return $TableB1;

}

i retrieve some data from database and return them and use AJAX Call to use them later

The problem is i have a table called users and table called meetings

i want to know how many meetings every user done everyday

so to do this i made a for each to take the user_id and use it to get the count of meetings done by each user

for example in meetings table i have a owned_by_id field which have the user_id

so what the code does if it found for example if owned_by_id(6) repeated in

meetings table 4 times it will return 4

but this is what i get in the browser

instead what i want to get is something like this

im using Laravel 5.7 and sorry i know my question is unclear this is the 1st time i have ever asked question on stackoverflow Image

Answer 1

You could add a new column in the select() using the COUNT SQL statement:

->select(
    'users.name as name',
    'group_user.user_id as id',
    'groups.name as groupname',
    \DB::raw('(
        SELECT COUNT(meetings.id) FROM meetings GROUP BY meetings.owned_by_id
    ) as meetings')
)