Monday, April 3, 2017

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



via Miguel López

Advertisement