drbi19093 2016-02-16 22:41
浏览 138
已采纳

Laravel 5.2查询范围内的多态关系

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?

  • 写回答

1条回答 默认 最新

  • duanfanta6741 2016-02-17 00:33
    关注

    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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 matlab数字图像处理频率域滤波
  • ¥15 ELGamal和paillier计算效率谁快?
  • ¥15 file converter 转换格式失败 报错 Error marking filters as finished,如何解决?
  • ¥15 ubuntu系统下挂载磁盘上执行./提示权限不够
  • ¥15 Arcgis相交分析无法绘制一个或多个图形
  • ¥15 关于#r语言#的问题:差异分析前数据准备,报错Error in data[, sampleName1] : subscript out of bounds请问怎么解决呀以下是全部代码:
  • ¥15 seatunnel-web使用SQL组件时候后台报错,无法找到表格
  • ¥15 fpga自动售货机数码管(相关搜索:数字时钟)
  • ¥15 用前端向数据库插入数据,通过debug发现数据能走到后端,但是放行之后就会提示错误
  • ¥30 3天&7天&&15天&销量如何统计同一行