sql - Eloquent：检查关系是否有一定的条件

Question

我有一个Survey与另一个模型相关hasOne 的模型，该模型与另一个模型Installation相关。所以我定义了这样的关系hasManyAssignmenthasManyThrough

public function assignments()
    {
        return $this->hasManyThrough(Assignment::class,Installation::class);
    }

我想编写一个查询来获取与调查相关联的任何Survey地方Assignments没有 assignment.type 0、1、2、3 和 4。

即每项调查应有 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,installations where assignments.installation_id = installations.id and installations.survey_id = surveys.id and assignments.type= 1');
                    })
                    ->orwhereNotExists(function ($query) {
                        return $query->raw('SELECT * FROM assignments,installations where assignments.installation_id = installations.id and installations.survey_id = surveys.id and assignments.type= 2');
                    })
                    ->orwhereNotExists(function ($query) {
                        return $query->raw('SELECT * FROM assignments,installations where assignments.installation_id = installations.id and installations.survey_id = surveys.id and assignments.type= 3');
                    })
                    ->orwhereNotExists(function ($query) {
                        return $query->raw('SELECT * FROM assignments,installations where assignments.installation_id = installations.id and installations.survey_id = surveys.id and assignments.type= 4');
                    });

            })
            ->with('customer', 'installer', 'installation')
            ->latest('updated_at')->get();

请任何建议和帮助将不胜感激。

标签： sqllaraveleloquentlaravel-query-builder

Answer 1

如果我正确理解了您的问题，您可以使用whereHas()和whereNotIn()来实现您的目标：

$schedulables = Survey
    ::with('customer', 'installer', 'installation')
    ->whereNotNull('installer_id')
    ->whereHas('assignments', function ($query) {
        $query->whereNotIn('type', [0, 1, 2, 3, 4]);
    })
    ->latest('updated_at')
    ->get();