orderBy Relationship and withCount() usage

Hi, I need to order a query based on a relationship results. I have a related votes table that gives me a list of votes for this model, each of these can be of type "up" or "down".

Now I want to order based on expression count(type.up) - count(type.down).

I've tried something like that, but this will count only up or only down.

$model->with('votes')->withCount(['votes' => function ($query) {
    return $query->where('type', 'up');
}])->orderBy('votes_count', 'desc')->paginate(10);

Can I add some raw query to do this? or exists another Eager Loading approach?

staudenmeir

7 years ago

Best Answer

Level 24

Use this:

$model->with('votes')
    ->withCount([
        'votes as votes_up' => function ($query) {
            return $query->where('type', 'up');
        },
        'votes as votes_down' => function ($query) {
            return $query->where('type', 'down');
        }
    ])->orderByRaw('votes_up - votes_down desc')->paginate(10);

5 likes

Please or to participate in this conversation.