php - MYSQL/Laravel : convert a WHERE into a variable

Question

我有一个查询，允许我过滤一些项目。

你不必理解它来帮助我。

$payments->whereExists(function($query) use ($startDate, $endDate){
                    // We must browse the projects to know if there was a project created BEFORE the project ^ and with different id

                    $query->select(DB::raw(1))
                      ->from('project as oldProjects')
                      ->whereRaw('oldProjects.deleted = 0')
                      ->whereRaw('oldProjects.id != project.id')
                      ->whereRaw('oldProjects.date_entered < project.date_entered')

                      ->whereExists(function($query2){
                            // We have to check if the project found belongs to the specified account
                            $query2->select(DB::raw(1))
                            ->from('accounts_project_1_c as oldProjectsAccount')
                            ->where('oldProjects.deleted', 0)
                            ->whereRaw('oldProjectsAccount.accounts_project_1project_idb = oldProjects.id')
                            ->whereRaw('oldProjectsAccount.accounts_project_1accounts_ida = accounts_pm7_payments7_1_c.accounts_pm7_payments7_1accounts_ida');
                       });
                });

因此，这是一个“WHERE”，它将帮助我仅从回头客那里获取项目(以前的项目已经存在)。

我想将其添加到另一个请求中，但作为一个值(返回 = 1 或返回 = 0)，而不再作为 WHERE。

仅供引用，这是我想添加此变量的请求:

$trustLogsQuotes = DB::table('trlog_trustaccountlog')
            ->select(
                'trlog_trustaccountlog.id as trlog_id',
                'quotes.id as quote_id',
                'quotes.name as quote_name',
                'quotes_cstm.quote_type_c')
            ->leftJoin('trlog_trustaccountlog_project_c', 'trlog_trustaccountlog_project_c.trlog_trustaccountlog_projecttrlog_trustaccountlog_idb', '=', 'trlog_trustaccountlog.id')
            ->leftJoin('project', 'trlog_trustaccountlog_project_c.trlog_trustaccountlog_projectproject_ida', '=', 'project.id')
            ->leftJoin('project_quotes_1_c', 'project_quotes_1_c.project_quotes_1project_ida', '=', 'project.id')
            ->leftJoin('quotes', 'quotes.id', '=', 'project_quotes_1_c.project_quotes_1quotes_idb')
            ->leftJoin('quotes_cstm', 'quotes_cstm.id_c', '=', 'quotes.id')
            ->where('trlog_trustaccountlog.transfer_date_c', '>=', $startDate)
            ->where('trlog_trustaccountlog.transfer_date_c', '<', $endDate)
            ->where('trlog_trustaccountlog.transfer_type_c', 'Transfer')
            ->where('trlog_trustaccountlog.deleted', 0)
            ->where('trlog_trustaccountlog_project_c.deleted', 0)
            ->where('project.deleted', 0)
            ->where('project_quotes_1_c.deleted', 0)
            ->where('quotes.deleted', 0)
            ->get();

我真的不知道如何转换它。你知道我该怎么做吗？

Answer 1

感谢评论，这是我的工作结果。

DB::raw('IF (EXISTS (
                                SELECT 1 FROM project as oldProjects 
                                WHERE oldProjects.deleted = 0 
                                AND oldProjects.id != project.id
                                AND oldProjects.date_entered < project.date_entered
                                AND EXISTS(
                                        SELECT 1 FROM accounts_project_1_c as oldProjectsAccount
                                        WHERE oldProjects.deleted = 0
                                        AND oldProjectsAccount.accounts_project_1project_idb = oldProjects.id
                                        AND oldProjectsAccount.accounts_project_1accounts_ida = account_id
                                    )
                                ), 1, 0) as returning'),