CODEWITHSUNDEEP
phpmysqllaraveleloquent
miguelopezv
miguelopezv

Reputation: 860

Laravel: Order query by count id field on relation

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
              },
            ]
          },

Upvotes: 0

Views: 728

Answers (2)

EddyTheDove
EddyTheDove

Reputation: 13259

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.

Upvotes: 2

Carlos González
Carlos González

Reputation: 362

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!

Upvotes: 0

Related Questions