查询多态表和相关表的记录 [英] Querying polymorphic table and related tables for records

问题描述

您好，我有一个活动表，其中包含发布的回复"和线程"的记录.我正在尝试查询活动表以到达回复"表和线程"表以进行搜索以返回记录，但是我无法理解为什么我的查询无法正常工作.

Hi I have an activities table which holds records for Reply and Thread posted. I'm trying to query the activities table to reach the Reply and Thread tables for a search to return records but I'm having trouble understanding why my query is not working.

这就是我所拥有的:

$results = \DB::table('activities')
        ->join('threads', function($builder) use ($search) {
            $builder->on('threads.id', '=', 'activities.activity_id')
            ->where('threads.body', 'LIKE', '%' . $search . '%');

        })
        ->join('replies', function($builder) {
            $builder->on('replies.id', '=', 'activities.activity_id');

        })
        ->where('replies.body', 'LIKE', '%' . $search . '%')
        ->orWhere('threads.title', 'LIKE', '%' . $search . '%')
        ->orWhere('threads.body', 'LIKE', '%' . $search . '%')
        ->get();

有人可以帮我吗?

谢谢.

"select * from `activities` inner join `threads` on `threads`.`id` = `activities`.`activity_id` and `activities`.`activity_type` = ? inner join `replies` on `replies`.`id` = `activities`.`activity_id` and `activities`.`activity_type` = ? where `replies`.`body` LIKE ? or `threads`.`title` LIKE ? or `threads`.`body` LIKE ?

推荐答案

在多态关系中，您有两列用于保存关系信息，在这种情况下，必须有'activities.activity_id'，'activities.activity_type'.

in polymorph relation you have two columns to hold the relation information, in this case there must be 'activities.activity_id','activities.activity_type'.

您应该在join语句中包括第二列:

you should include the second column in join statement:

 $results = \DB::table('activities')
            ->leftJoin('threads', function($builder) {
                $builder->on('threads.id', '=', 'activities.activity_id')
                ->where('activities.activity_type',Thread::class);

        })
        ->leftJoin('replies', function($builder) {
            $builder->on('replies.id', '=', 'activities.activity_id')
            ->where('activities.activity_type',Reply::class);
        })
        ->where('replies.body', 'LIKE', '%' . $search . '%')
        ->orWhere('threads.title', 'LIKE', '%' . $search . '%')
        ->orWhere('threads.body', 'LIKE', '%' . $search . '%')
        ->get();

您可以通过以下方式做到这一点:

and you can do it this way:

在活动模型中添加以下关系:

in your Activity Model add this relations:

  public function thread()
    {
        return $this->belongsTo(Thread::class,'activity_id')
            ->where('activity_type',Thread::class);
    }

public function replay()
{
    return $this->belongsTo(Replay::class,'activity_id')
        ->where('activity_type',Replay::class);
}

现在您可以在这样的地方使用普通的

now you can use normal whereHas like this:

  $results = Activity::with('activityable')->whereHas('thread', function ($query) use ($search) {
            $query->where('threads.body', 'LIKE', '%' . $search . '%')
                ->orWhere('threads.title', 'LIKE', '%' . $search . '%');
        })->orWhereHas('replay', function ($query) use ($search) {
            $query->where('replies.body', 'LIKE', '%' . $search . '%');

        })->get();

请注意，我不确定您的Activity模型中的多态关系名称，将关系名称活动的"替换为模型中的实际名称.

note that im not sure of the polymorphic relation name in your Activity model, replace the relation name 'activityable' with the actually name in your model.

这篇关于查询多态表和相关表的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！