php - MySQL加入三表/Laravel查询

我有三个表受益人，问题，回应..受益人

Benficiary
id name
1  user1
2 user2
3 user3

Questions
id question             type
1  what is your hobby   hobby
2  address              contact
3  phone                contact
..questions upto 500 and i need to select according to its type

Response
id beneficiary_id question_id response
1  1              1           football
2  1              3           5354353
3  2              1           basketball
4  3              2           cityname
5  3              3           432423

受益人表和问题表是静态的，但响应表将两者结合。我试图从laravel查询中做到这一点，但它只给我答复
sql查询

select response,questions.question, beneficiarys.name
from responses RIGHT join (questions,beneficiarys)
on beneficiarys.id = responses.beneficiary_id && questions.id = responses.question_id
where questions.sheet_type = 'SCDS' OR questions.sheet_type = 'SMM'

laravel查询

$beneficiaries =       Beneficiary::with('quests','response')->whereHas('response', function ($query)use($sheet_type) {
             $query->where('beneficiary_id', '>', 0);
        })
->whereHas('quests',function($query)use($sheet_type){
                $query->where('questions.sheet_type','=',$sheet_type);
                $query->orWhere('questions.sheet_type','=','SCDS');
            })
        ->where('updated_at','>=',!empty($this->request->from_date)?$this->request->from_date:$from)->where('updated_at','<=',!empty($this->request->to_date)?$this->request->to_date:$date)->get();

受益人模型

public function response()
{
return $this->hasMany('App\Model\Response');
}

public function quests(){
return $this->belongsToMany('App\Model\Question',   'responses','beneficiary_id','question_id');

}

问题模型

public function response(){
    return $this->hasMany('App\Model\Response');
}

public function bens(){
    return $this->belongsToMany('App\Model\Bebeficiary', 'responses','question_id','beneficiary_id');
}

反应模型

public function beneficiary(){
    return $this->belongsTo('App\Model\Beneficiary', 'beneficiary_id');
}

public function question(){
    return $this->belongsTo('App\Model\Question', 'question_id');
}

我需要根据其类型的所有问题（类型是由用户从视图中选择），我需要这样的输出

beneficary  hobby     address phono  ....
user1       football  null    4244   ....
user2       basketball null   null   ....

有什么方法可以通过sql或laravel查询来实现*

最佳答案

这是mysql选择，也许是更好的方法，但是这项工作

SELECT b.name,hobby.response as hobby,address.response as addres,phone.response as
phone FROM beneficiary as b
left join (select *  from response where question_id=1 ) as hobby
      on hobby.beneficiary_id = b.id
left join (select *  from response where question_id=2 ) as address
      on address.beneficiary_id = b.id
left join (select *  from response where question_id=3 ) as phone
      on phone.beneficiary_id = b.id