Laravel count-function in Model, then sortBy count()

Question

I am trying to Order the Posts table by the number of votes a Post has got. The votes are stored in an other table

(Votes: post_id, user_id, vote_type)

Post-Model:

class Post extends Model
{
    public function user()
    {
      return $this->hasOne(User::class);
    }

    public function votes()
    {
      return DB::table('votes')->where('post_id','=',$this->id)->sum('vote_type');
    }
}

The votes functions returns the number of votes a post has recieved(The Votes a stored in a seperate table)

Now I am trying to order all the Posts by the number of votes they have got.

Post::get()->sortBy('votes');

This returns follwing Error:

Relationship method must return an object of type Illuminate\Database\Eloquent\Relations\Relation

I would be thankful about any help to fix this!

Mr. Pyramid · Accepted Answer · 2017-10-12 19:08:43Z

4

give it a try

Post::get()->sortBy(function($query){
return $query->votes();
});

Alternative

You can use withCount() as it will place a {relation}_count column on your resulting models.

Post::withCount(['votes'])
->orderBy('votes_count')
->get()

For Pagination

Refer docs for more details on paginations

Post::withCount(['votes'])
->orderBy('votes_count')
->paginate();

edited Oct 12, 2017 at 19:08

answered Oct 11, 2017 at 10:47

Mr. Pyramid

3,9355 gold badges35 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

rsaw Over a year ago

The first solution doesn't work: It return following error: Relationship method must return an object of type Illuminate\Database\Eloquent\Relations\Relation This is because the votes() method returns an integer.

Mr. Pyramid Over a year ago

try now I have forgot () there

rsaw Over a year ago

Thanks! This worked. Do you know if there is any possability to paginate the result, as it is an instance of collection

pseudoanime · Accepted Answer · 2017-10-11 10:52:38Z

0

Add the following function in your Post Model

 protected static function boot()
    {
        parent::boot();
        static::addGlobalScope('voteCount', function ($builder) {
            $builder->withCount('votes');
        });
    }

Now, each of your post model will always have a voteCount value and You can sort on that.

In your controller, use:

Post::get()->sortBy('voteCount');

Keep in mind that this will always return a votesCount with the PostModel, but I'm assuming it will be required as it usually does in this kind of applications.

edited Oct 11, 2017 at 10:52

answered Oct 11, 2017 at 10:44

pseudoanime

1,59312 silver badges19 bronze badges

Comments

Kyaw Kyaw Soe · Accepted Answer · 2017-10-11 11:01:13Z

-1

If you want to sort votes as a attribute you have to to make it to be attribute by adding getVotesAttribute() method in your Post Model.

class Post extends Model
{
    public function user()
    {
      return $this->hasOne(User::class);
    }

    public function getVotesAttribute()
    {
      return DB::table('votes')->where('post_id','=',$this->id)->sum('vote_type');
    }
}

edited Oct 11, 2017 at 11:01

answered Oct 11, 2017 at 10:32

Kyaw Kyaw Soe

3,4001 gold badge16 silver badges25 bronze badges

3 Comments

Bartłomiej Sobieszek Over a year ago

When you will want to display last 100 posts and their votes, will you execute this query for each post you want to print on screen? How will you sort it?

Kyaw Kyaw Soe Over a year ago

@BartłomiejSobieszek If you want to display 100 last posts just do Post::limit(100)->latest()->->get()->sortBy('votes'); what is the problem?

Bartłomiej Sobieszek Over a year ago

You sort it by query execution, it means you would execute the getVotesAttribute query 100 times as you need to make a query for each model to sort it out. If you don't see the problem then try to use 3 solutions like this on single page and think why loading of the pages takes more than 3 seconds

Collectives™ on Stack Overflow

Laravel count-function in Model, then sortBy count()

3 Answers 3

3 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related