Laravel: Order query by count id field on relation

Question

I have a resources table, and a resources_votes table in which I have a record for everytime a user likes [vote_id 1] or dislikes [vote_id 2] a resource. now I need to retrieve all the resource information ordered from most liked to the less one, and since resource has many resources_votes when I use ->with('votes') it returns an array of objects with each one of the resources_votes related to the resource ID.

Is there a way I can count how many positive votes a resource has [vote_id =2], add a field with this count and order the query from most voted to less voted?

PD: this is an example of a resource object with the resources_votesrelationship, there you can see the votes array and the vote ID which I need to count and order according to:

    {
            "id": 2,
            "name": "aspernatur",
            "image": "http://lorempixel.com/480/480/?31738",
            "author": "Max Thiel",
            "created_by": 6,
            "reviewed_by": "Mr. Emiliano Frami",
            "lang_id": 2,
            "resource_type_id": 1,
            "status": "Borrado",
            "resource_type": "Imagen",
            "platforms": [],
            "classifications": [],
            "votes": [
              {
                "id": 2,
                "user_id": 2,
                "resource_id": 2,
                "vote_id": 1
              },
              {
                "id": 29,
                "user_id": 1,
                "resource_id": 2,
                "vote_id": 2
              },
              {
                "id": 24,
                "user_id": 12,
                "resource_id": 2,
                "vote_id": 1
              },
            ]
          },

EddyTheDove · Accepted Answer · 2017-04-04 05:08:19Z

2

You can get it with eager loading like this

$resources = Resource::withCount(['votes' => function ($query) {
    $query->where('vote_id', '=', '2'); //count positive votes only
}])->get();

This will return a column named votes_count. You need to call that column to display the count.

answered Apr 4, 2017 at 5:08

EddyTheDove

13.2k2 gold badges40 silver badges46 bronze badges

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

2 Comments

miguelopezv Over a year ago

is there a way i can retrieve both the positive and negative counts? I've tried to create two withCountbut one overwrites the other. Is there a way to create a votes_count row and a negative_votes_count row?

Jickson Johnson Koottala Over a year ago

I didn't know there is a use case "withCount" exist. thank you.

Carlos González · Accepted Answer · 2017-04-04 01:52:45Z

0

You can use Eloquent Accessors.

Create the following custom field and function inside your model

protected $appends = ['positiveVotesCounter'];

Then, create the following function to retrieve your custom data.

function getPositiveVotesCounterAttribute() {
    $totalPositiveVotes = 0;
    foreach($this->votes as $vote) {
        if($vote['id'] == 2) {
            $totalPositiveVotes++;
        }
    }

    return $totalPositiveVotes;
}

More info can be found here: https://laravel.com/docs/5.4/eloquent-mutators

Good luck!

answered Apr 4, 2017 at 1:52

Carlos González

3621 gold badge4 silver badges14 bronze badges

Collectives™ on Stack Overflow

Laravel: Order query by count id field on relation

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related