Vico Vico - 1 year ago 70
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

->from('project as oldProjects')
->whereRaw('oldProjects.deleted = 0')
->whereRaw(' !=')
->whereRaw('oldProjects.date_entered < project.date_entered')

// We have to check if the project found belongs to the specified account
->from('accounts_project_1_c as oldProjectsAccount')
->where('oldProjects.deleted', 0)
->whereRaw('oldProjectsAccount.accounts_project_1project_idb =')
->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')
' as trlog_id',
' as quote_id',
' as quote_name',
->leftJoin('trlog_trustaccountlog_project_c', 'trlog_trustaccountlog_project_c.trlog_trustaccountlog_projecttrlog_trustaccountlog_idb', '=', '')
->leftJoin('project', 'trlog_trustaccountlog_project_c.trlog_trustaccountlog_projectproject_ida', '=', '')
->leftJoin('project_quotes_1_c', 'project_quotes_1_c.project_quotes_1project_ida', '=', '')
->leftJoin('quotes', '', '=', 'project_quotes_1_c.project_quotes_1quotes_idb')
->leftJoin('quotes_cstm', 'quotes_cstm.id_c', '=', '')
->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)

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

Answer Source

Thanks to the comments, here is my working result.

DB::raw('IF (EXISTS (
                                SELECT 1 FROM project as oldProjects 
                                WHERE oldProjects.deleted = 0 
                                AND !=
                                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 =
                                        AND oldProjectsAccount.accounts_project_1accounts_ida = account_id
                                ), 1, 0) as returning'),
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download