我希望使用Laravel根据条件返回特定数量的记录。

Question

我正在使用Laravel8.0开发一个google表单克隆

我的桌子结构如下：

1调查

1. ID
2. survey_name
3. survey_description
4. survey_type
5. start_date
6. end_date

2.项目

1. id
2. project_name
3. project_description
4. company_id

3.公司

1. id
2. company_name
3. company_address

我编写了如下代码来返回项目：

$projects = \DB::table('projects')
                ->join('companies', 'projects.company_id', '=', 'companies.id')
                ->select('projects.*', 'companies.company_name as company_name',
                    \DB::raw('(SELECT COUNT(*) FROM surveys WHERE DATE(surveys.end_date) < "'.date('Y-m-d').'" AND surveys.project_id = projects.id) as completed_surveys'),
                    \DB::raw('(SELECT COUNT(*) FROM surveys WHERE surveys.project_id = projects.id) as total_surveys') 
                )
                ->paginate(6);

现在我只想返回那些total_surveys = completed_surveys.的项目

请引导我。

提前鸣谢。

Answer 1

完成了..。

下面的是我的代码.

 $projects = \DB::table('projects')->where(
                \DB::raw('(SELECT COUNT(*) FROM surveys WHERE DATE(surveys.end_date) < "'.date('Y-m-d').'" AND surveys.project_id = projects.id)'),
                \DB::raw('(SELECT COUNT(*) FROM surveys WHERE surveys.project_id = projects.id)')
            )
                ->join('companies', 'projects.company_id', '=', 'companies.id')
                ->select('projects.*', 'companies.company_name as company_name',
                    \DB::raw('(SELECT COUNT(*) FROM surveys WHERE DATE(surveys.end_date) < "'.date('Y-m-d').'" AND surveys.project_id = projects.id) as completed'),
                    \DB::raw('(SELECT COUNT(*) FROM surveys WHERE surveys.project_id = projects.id) as total_surveys') 
                )
                ->paginate(6);