我有一個模型Survey，它與hasOne另一個模型Installation相關，它與hasMany另一個模型Assignment相關。
所以我定義了一個hasManyThrough的關系，像這樣
public function assignments()
{
return $this->hasManyThrough（Assignment::class,Installation::class）。
}
我想寫一個查詢來獲取任何Survey，其中與調查相關的Assignments沒有0、1、2、3和4的assignment.type。
i.e. 每個調查應該有5個帶有記錄的任務
Survey => [
[Assignment => [type = 0] ]
[Assignment => [type = 1] ]
[Assignment => [type = 2] ]
[Assignment => [type = 3] ]
[Assignment => [type = 4 ]]
]
我試了一下這個查詢
$schedulables = Survey::whereNotNull（'installer_id')
->where(function ($query) {
$query
->whereNotExists(function ($query) {
return $query->raw('SELECT * FROM assignments,instants where assignments.installation_id = installations.id and installations.survey_id = surveys.id and assignments.type= 1')。
})
->orwhereNotExists(function ($query) /span>{
return $query->raw('SELECT * FROM assignments,instants where assignments.installation_id = installations.id and installations.survey_id = surveys.id and assignments.type= 2')。
})
->orwhereNotExists(function ($query) /span>{
return $query->raw('SELECT * FROM assignments,instants where assignments.installation_id = installations.id and installations.survey_id = surveys.id and assignments.type= 3')。
})
->orwhereNotExists(function ($query) /span>{
return $query->raw('SELECT * FROM assignments,instants where assignments.installation_id = installations.id and installations.survey_id = surveys.id and assignments.type= 4')。
});
})
->with('customer', 'installer', 'installation')
->最新('uped_at')->get()。
歡迎任何建議和幫助。
uj5u.com熱心網友回復：
如果我沒有理解你的問題，你可以使用whereHas()和whereNotIn()來實作你想要的東西：
$schedulables = Survey
::with('customer'/span>, 'installer'/span>, 'installation'/span>)
->whereNotNull('installer_id')
->whereHas('assignments', function ($query) /span>{
$query-> whereNotIn('type'/span>, [0, 1, 2, 3, 4]）。)
})
->最新('uped_at')
->get()。
uj5u.com熱心網友回復：
我的最終解決方案是在Survey模型中創建這些關系
public function assignment_outdoor（）{
return $this->hasManyThrough(Assignment::class,Installation::class)
->where('assignments.type',1)
->where('assignments.status',2) 。
}
public function assignment_indoor（）{
return $this->hasManyThrough（Assignment::class,Installation::class)
->where('assignments.type',2)
->where('assignments.status',2) 。
}
public function assignment_testing_activation（）{
return $this->hasManyThrough(Assignment::class,Installation::class)
->where('assignments.type',3)
->where('assignments.status',2) 。
}
public function assignment_lm_splicing（）{
return $this-> hasManyThrough(Assignment::class,Installation::class)
->where('assignments.type',4)
->where('assignments.status',2) 。
}
然后使用doesntHave和ordoesntHave構建查詢。
$schedulables = Survey:: when($installer_filter, function ($query, $installer_filter) {
return $query->where（'installer_id', $installer_filter）。
})
->whereNotNull('installer_id')
//實際的解決方案。
->where(function ($query) {
return $query
->doesntHave('assignment_outdoor'/span>)
->或DoesntHave('assignment_indoor')
->或者DoesntHave('assignment_testing_activation')
->或者DoesntHave('assignment_lm_splicing')。
})
//end
->doesntHave('network_job_order_customer')
->with('客戶', '安裝人員', '安裝')
->最新('uped_at')。
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/324241.html
標籤：