Laravel 5.2 query scope on polymorphic relationship

Question

I have a one to many polymorphic relationship exactly like described in documentation, except I have votes instead of photos and an integer vote column in the votes table. Like that:

-votes
--id
--vote
--voteable_id
--voteable_type

The Vote model has the voteable method:

public function voteable()
{
    return $this->morphTo();
}

But for models that I want to have votes attached to (Page in this example) I have created a VoteableTrait, which has the votes() morphMany method and a scope I'm trying to get to work. The scope would add the sum of all votes that belong to a certain model and order the parent models by the sum. Scope as I've tried:

public function scopeWithVotesTrait($query)
{
    return $query->with(['votes' => function($q){
        $q->select(\DB::raw("SUM(vote) AS votes"), 'voteable_id')->first();
    }]);
}

But with this I cannot orderBy as Laravel performs 2 different queries, one for parent model and one for votes it seems. The result of Page::withVotesTrait()->first() puts votes in an array:

{
"id": 1,
    "votes": [
    {
        "votes": "-1",
        "voteable_id": 1
    }
    ]
}

The other idea I had was to perform a join instead of ->with() like that:

public function scopeWithVotesTrait($query, $model, $table)
{
    return $query->join('votes', function($join) use($model, $table)
    {
        $join->on('votes.voteable_id', '=', $table . '.id')
            ->where('votes.voteable_type', '=', $model);
    })->select('*', \DB::raw('sum(vote) as votes'))
    ->orderBy('votes')->groupBy($table . '.id');
}

But if I have to manually put in $model ('App\ParentModel') and $table ('parent_models') every time I use the scope, that kind of beats the purpose of putting it in a trait.

So is there either a way to efficiently order by the sum of votes in my first example of scope? Or is there any static method you could call from Eloquent Model that would return the model name and one for model table to make the second example work?

Any other ideas how to make a scope that would attach sum of all votes to the parent model and order by it?

Answer 1

I have got it to work with the second example using join. Eloquent\Model has a static method getTable() which returns models table name, so I can pass it as $model parameter dynamically. And I php function get_class() will return the namespace with classname of parent model for $model parameter.

Correct query for what I wanted to achieve:

public function scopeWithVotesTrait($query)
{
    return $query->leftJoin('votes', function($join) use($model, $table)
    {
        $join->on('votes.voteable_id', '=', $table . '.id')
            ->where('votes.voteable_type', '=', $model);
    })->addSelect('*', $table . '.id', \DB::raw('COALESCE(SUM(vote),0) as votes'))
    ->groupBy($table . '.id')->orderBy('votes');
}

I have also added COALESCE, which will make query return 0 if no votes are found. This is so the orderBy is always correct.

I have also specified * and parent_table.id to select statements. Without * the query will only return whatever we specify within select statement. And without parent_table.id the query will not return any models we might query with relationship methods (example: Model::withVotesTrait()->with('some_other_child') will not add some_other_child to the result).

I have also used leftJoin instead of join so that it will always return all parents, no matter the number of votes conncted to it.