CODEWITHSUNDEEP
laravellaravel-5laravel-5.4
Harry
Harry

Reputation: 25

Sort Model by a nested Relationship count

Room Model

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Room extends Model
{
    /**
     * Get the comments for the room.
     */
    public function comments()
    {
        return $this->hasMany('App\Comment');
    }
}

Comment Model 

namespace App;

use Illuminate\Database\Eloquent\Model;

class Comment extends Model
{

    /**
     * Get the room that owns the comment.
     */
    public function room()
    {
        return $this->belongsTo('App\Room');
    }

    public function upvotes() {
        return $this->hasMany('App\Upvote');
    }
}

Upvote Model 

namespace App;

use Illuminate\Database\Eloquent\Model;

class Upvote extends Model
{

    /**
     * Get the comment that the upvote belongs to.
     */    
    public function comment() {
        return $this->belongsTo('App\Comment');
    }
}

How would I be able to have a list of comments for the room that is sorted by the count of upvotes each comment has?

I have searched online and have only managed to find answers relating to having the model sorted by its directed relation e.g $room->comments() rather than being sorted by $room->comments()->upvotes()?

Thanks

Upvotes: 1

Views: 935

Answers (2)

Alexey Mezenin
Alexey Mezenin

Reputation: 163778

Use the withCount() method which will calculate number of upvotes for each comment and will create upvotes_count property:

 Comment::where('room_id', $roomId)
        ->withCount('upvotes')
        ->orderBy('upvotes_count', 'desc')
        ->get();

Upvotes: 3

Laerte
Laerte

Reputation: 7083

You can try this with a join and a raw query:

$comments = \App\Comment::join('upvotes', function($j) {
            $j->on('upvotes.comment_id', '=', 'comments.id');
        })
        ->where('comments.room_id', $roomId)
        ->with('upvotes')
        ->groupBy('comments.id')
        ->orderBy('count', 'desc')
        ->select((['comments.*', \DB::raw('COUNT(upvotes.comment_id) as count')]))->get();

Upvotes: 1

Related Questions