How to add has Many Relation withCount column in laravel

DB Schema Model (Table 1)

id, name, code, parent_id ( I have Parent child relation in the same table)

eg:- We have relation (Model Table)

Data

1, abc, asw0001, 0 ( parent)

2,def, asw00000001,1 ( child In the same table)

3,ghi, asw00000002,1 ( child In the same table)

Users (Table 2)

id, name, email, model_id

eg:- We have relation (Users table)

Note: Only child of Model Table is having a user relation, Also One child have has many relation with users tables

Data

1, lmn, lmn@gmailcom, 2

2, opq, opq@gmailcom, 3

Model Class

function children(){  // For Getting Child belongs to a Parent in Model Table
    return $this->hasMany(Model::class,'parent_id');
}

public function parent(){  // For Getting Parent belongs to a Child in Model Table 
    return $this->belongsTo(Model::class,'parent_id');
}

public function users(){
    return $this->hasMany(User::class, 'model_id', 'id');
}

MyController Class

public function index(){
    Model::withCount(['children'])->with(['children' => function($query){ $query->withCount('users'); }])->get();
}

It gave result like below

{ array:1 [ 0 => Model { attributes: array:7 [ "id" => 1 "name" => "abc" "code" => "asw0001" "created_at" => "2021-03-26 09:12:26" "updated_at" => "2021-03-26 09:13:19" "deleted_at" => null "children_count" => 2 ] relations: array:1 [ "children" => { array:2 [ 0 => Model { attributes: array:7 [ "id" => 1 "name" => "def" "code" => "asw00000001" "created_at" => "2021-03-26 09:13:05" "updated_at" => "2021-04-01 06:03:48" "deleted_at" => null "users_count" => 1 ] } 1 => Model { attributes: array:7 [ "id" => 2 "name" => "ghi" "code" => "asw00000002" "created_at" => "2021-03-26 09:13:05" "updated_at" => "2021-04-01 06:03:48" "deleted_at" => null "users_count" => 1 ] } ] } ] } ] }

I want to get result like

Thanks in advanced

chaudigv

5 years ago

Level 16

You can leverage collection sum()

$models = Model::withCount(['children'])->with(['children' => function($query){ $query->withCount('users'); }])->get();

$models->transform(function($model){
    $model['users_count'] = $model->children->sum('users_count');
    return $model;
})

TarunShrivastva

5 years ago

Level 1

Hi chaudigv,

Thanks for your response. One more thing I want to ask. Actually I want to apply order by to this user count. I want it in same query so that my pagination will work.

Thanks in advanced.

TarunShrivastva

5 years ago

Level 1

Has Many Through is works for me in this case. Also my sorting and pagination working fine.

1 like

Please or to participate in this conversation.