Vico Vico - 5 months ago 16
MySQL Question

MYSQL/Laravel : convert a WHERE into a variable

I've got a query allowing me to filter some projects.

You don't have to understand it to help me.

$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');
});
});


So this is a WHERE, that will help me to get only projects from returning clients (a previous project existed before).

I would like to add this to another request, but as a value (returning = 1, or returning = 0) and no longer as a WHERE.

Just FYI, here is the request in which I would like to add this variable:

$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();


I really don't know how to convert it. Do you have any idea how I could do this?

Answer

Thanks to the comments, here is my working result.

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'),