Laravel: How can I order by a child field using th ORM?-CodePudding

I'm writing an app that allows to rank (vote) among several options. If an user from the Internet (not signed into the app) votes, I just store it with a user_id = NULL, but if some authenticated user votes, I save his user_id

I have the following data structure (the fields are the minimum for the question)

Issues (What the people is gonna vote about)
------------
id
title
description

Options
------------
id
issue_id
title
description

Votes
------------
id
option_id
user_id
vote          // vote can be  1 or -1 for a signed user, and a whole number for anyone else

So everything is working correctly, creating records, updating, etc., but now I want to display my options in votes descending order

If I do a SELECT option_id, sum(vote) AS s FROM votes ORDER BY s GROUP BY option_id I get what I want.

Now, I want to use Laravel's ORM, using its relationships, I'm passing a veriable named $issue to my blade file, and in then I just do a foreach

@foreach ($issues as $issue)
   ...
@endforeach

So the blade displays the issues in order they were created or modified, but I want to order them by the number of votes they have.

The SQL sentence would be like:

SELECT issues.title, options.id, options.title, SUM(vote) AS s
FROM issues, options, votes
WHERE issues.id=9 
  AND options.issue_id = issues.id
  AND votes.options_id = options.id
GROUP BY options.id
ORDER BY s DESC

So I think I can replicate it with the query builder, but I don't know how to do it with the ORM. Any idea?

CodePudding user response：

If your $issues variable is an Eloquent collection you can do that:

@foreach ($issues->sortBy('SUM(votes) name here') as $issue)
    //Your code here
@endforeach

More info in official docs (SortBy)

CodePudding user response：

Assuming you have set all the models and their relationships: Issue, Option and Vote

Add a Issue hasMany Vote Through Option relationship.

class Issue {

    function votes(){
        return $this->hasManyThrough(Vote::class, Option::class);
    }

}

Then :

Issue::with('options.votes')->withCount('votes_count')->orderBy('votes_count')->get()