How can I implement SUM() in Laravel relations?

Question

Here is my query which works as well:

SELECT sum(r.rating) as rank,b.* FROM books as b
LEFT JOIN ranks as r ON b.id = r.book_id
WHERE 1
GROUP BY (b.id)
ORDER BY rank DESC

Now I want to do the same in Laravel. Here is what I've tried:

// Book model
class Book extends Model
{
    public function ranks()
    {
        return $this->hasMany(Rank::class)->sum("rating");
    }
}

// Controller
$obj = new Book;
$get = $obj->ranks()->orderBy('rating', 'desc')->get();

It throws this error:

Call to a member function groupBy() on integer

Any idea how can I fix this problem?

You might find an answer here: https://stackoverflow.com/questions/28267550/how-to-group-and-sum-a-pivot-table-column-in-eloquent-relationship — sybear, Feb 25 '18 at 20:32

score 2 · Answer 1 · answered Feb 25 '18 at 21:18

2

public function ranks() {
    return $this->hasMany(Rank::class)
        ->select('book_id', \DB::raw('sum(`rating`) as `rank`'))
        ->groupBy('book_id');
}

$books = Book::with('ranks')->get();
$sortedBooks = $books->sortByDesc(function($book) {
    return $book->ranks->sum('rank');
});

answered Feb 25 '18 at 21:18

Jonas Staudenmeir

24,815
6
63
109

Doesn't this load all `Book` models into the memory and then sort them? That could lead to high memory usage. It would be preferable to sorting in the database layer. – Fredrik Jan 14 '19 at 19:58
Do you mean `Rank` models? – Jonas Staudenmeir Jan 14 '19 at 20:28
No, I mean the `Book` models. Say, there are 1000 books, and you only want the 10 best ranked. Then you would need to load 1000 `Book` + their `Ranks` in order to sort the 10 best. – Fredrik Jan 14 '19 at 20:31
Yes, but that's not what the question was about. – Jonas Staudenmeir Jan 14 '19 at 20:34

How can I implement SUM() in Laravel relations?

1 Answers1