Mysql join three table/ Laravel query

Question

我有三个表格受益人，问题，回复。受益人

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'

拉拉维尔查询

$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('AppModelResponse');
}
public function quests(){
return $this->belongsToMany('AppModelQuestion',   'responses','beneficiary_id','question_id');

}

问题模型

public function response(){
return $this->hasMany('AppModelResponse');
}
public function bens(){
return $this->belongsToMany('AppModelBebeficiary', 'responses','question_id','beneficiary_id');
}

响应模型

public function beneficiary(){
return $this->belongsTo('AppModelBeneficiary', 'beneficiary_id');
}
public function question(){
return $this->belongsTo('AppModelQuestion', 'question_id');
}

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

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

有没有办法通过SQL或Laravel查询来实现它*

Answer 1

这是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

Answer 2

您将从此查询中获得结果。拉拉维尔也支持定制。

$sSQL = "SELECT * FROM RESPONSE 
INNER JOIN QUESTION ON QUESTION.id=RESPONSE.QUESTION_id
INNER JOIN  BENEFICIARY ON BENEFICIARY.id=RESPONSE.BENEFICIARY_id";
DB::query($sSQL);

Answer 3

尝试

$res = DB::table('responses')
->join('questions','responses.question_id','=','questions.id')
->join('beneficiarys','responses.beneficiary_id','=','beneficiarys.id')
->select('responses.*','questions.question','beneficiarys.name')
->where('questions.sheet_type', '=','SCDS')
->orWhere('questions.sheet_type','=','SMM')
->get();

将结果用作

foreach($res as $res)
{
echo $res->name;
}

Answer 4

如果你想以雄辩的方式做到这一点，你可以尝试在你的嵌套关系上使用wherehas。尝试类似以下内容：

Beneficiary::with('response.question')->whereHas('response.question')

有关更多参考，请尝试此链接 使用嵌套关系雄辩