Sort data based on count with sub-relationship in Laravel 5

Question

I have three tables, users, branches and jobs. users table has one-to-one relationship with branches table and one-to-many relationship with jobs table. Branches are responsible for uploading jobs and users table can include various categories including branch. Both branches and jobs table has user_id as a foreign key. Now I want to obtain the branches list with number of jobs uploaded by them and also sort them by highest number of uploads first.

I am able to display list of branches along with the number of jobs they posted as in the following code, but I want to sort according to the number of jobs posted. How can I achieve that?

@foreach(\App\Branch::all() as $branchesJob) 
<div class="col-lg-3 col-xs-3">
  <div class="small-box bg-olive-active">
    <div class="inner">
      <h3>{{\App\Job::where('user_id','=',$branchesJob->user->id)->count()}}</h3>
      <p>{{$branchesJob->branch_name}}</p>
    </div>
    <a class="small-box-footer" href="{{(route('admin.branch_job.show',$branchesJob->id))}}">
                                            More info
                                            <i class="fa fa-arrow-circle-right"></i>
                                        </a>
  </div>
</div>
@endforeach

It would be usefull if you posted your table structure instead of just explaining it — cmorrissey, Mar 02 '16 at 17:09
why you doing your database call in your view ?? It's a bad practice. — Drudge Rajen, Mar 02 '16 at 17:12

score 0 · Answer 1 · edited May 23 '17 at 12:15

0

You cannot get it done retrieving all your records in the template. And you won't be able to do it through a separate aggregate function (counting the relations).

I would do it with a join clause with an order by, as the marked-correct answer explains here: Laravel Eloquent sort by relation table column

edited May 23 '17 at 12:15

Community

1
1

answered Mar 02 '16 at 18:55

subzeta

419
3
7

score 0 · Accepted Answer · edited May 23 '17 at 11:45

0

Thank you for the reply. I was able to do it by using sortBy() and count() methods.

$user = \App\User::where('category','=','Branch')->with('job')->get()->sortBy(function($u){
        return count($u->job);
 }, $options = SORT_REGULAR, $descending = true);

Thanks to this post! Laravel OrderBy relationship count

edited May 23 '17 at 11:45

Community

1
1

answered Mar 05 '16 at 02:49

sazanrjb

138
2
8

Sort data based on count with sub-relationship in Laravel 5

2 Answers2