Laravel 5, pagination and ordering a list by a relationship count

Question

Am trying to sort a query by a linked table count and cannot figure out how to use Laravel pagination; below is my query that is passed to the view

I think the Laravel 5 paginator method has been moved?

    $companies = Company::with('locations')->get()->sortBy(function($count)
    {
        return $count->locations->where('status', '=', 'Active')->count();
    }, null, true);

Because the get and sortby functions are called I cannot apply pagination (error below)

Call to undefined method Illuminate\Database\Eloquent\Collection::pagination()

Maybe there is a alternative way to do sorting by the count of a hasMany relationship - in this case "locations". (side question; I do not know how to make the above work with a hasManyThrough relationship).

The model relationships are shown below

public function locations()
{
    return $this->hasMany('App\Location');
}

public function equipment()
{
    return $this->hasManyThrough('App\Equipment', 'App\Location');
}

Using something like the below gives an undefined "make" method?

return Paginator::make($companies->all(), $companies->getTotal(), $companies->getPerPage())

Answer 1

Here is my edited answer, the sort By command and pagination can indeed work together and the following should work for you, of course you may need to adjust the columns in the scopes join statement.

Add this function to your Company class.

    public function scopeLocationCount($query){
    return $query->join('locations','locations.company_id','=','companies.id')
            ->selectRaw('companies.*, count(locations.id) as count')->where('locations.status','=','active')->groupBy('companies.id');
}

And then you can make the call to the paginate method like so.

$companies= (Company::with('locations')->locationCount()->orderBy('count')->paginate(30));

And then after you write your foreach loop to display the details you need to render the paginator like so {!! $companies->render() !!}.

Hope this helps.