Converting large MySQL query to work with Laravel's DB

Question

I have a very large query like so:

SELECT *

FROM cont_contracting
LEFT OUTER JOIN contacts_cont_contracting_1_c l1_1 ON cont_contracting.id=l1_1.contacts_cont_contracting_1cont_contracting_idb AND l1_1.deleted=0

LEFT OUTER JOIN contacts l1 ON l1.id=l1_1.contacts_cont_contracting_1contacts_ida AND l1.deleted=0
LEFT OUTER JOIN contacts_contacts_12_c l2_1 ON l1.id=l2_1.contacts_contacts_12contacts_ida AND l2_1.deleted=0

LEFT OUTER JOIN contacts l2 ON l2.id=l2_1.contacts_contacts_12contacts_idb AND l2.deleted=0
LEFT OUTER JOIN contacts_contacts_1_c l3_1 ON l1.id=l3_1.contacts_contacts_1contacts_ida AND l3_1.deleted=0

LEFT OUTER JOIN contacts l3 ON l3.id=l3_1.contacts_contacts_1contacts_idb AND l3.deleted=0
LEFT OUTER JOIN contacts_contacts_2_c l4_1 ON l1.id=l4_1.contacts_contacts_2contacts_ida AND l4_1.deleted=0

LEFT OUTER JOIN contacts l4 ON l4.id=l4_1.contacts_contacts_2contacts_idb AND l4.deleted=0
LEFT OUTER JOIN contacts_contacts_4_c l5_1 ON l1.id=l5_1.contacts_contacts_4contacts_ida AND l5_1.deleted=0

LEFT OUTER JOIN contacts l5 ON l5.id=l5_1.contacts_contacts_4contacts_idb AND l5.deleted=0
LEFT OUTER JOIN contacts_contacts_5_c l6_1 ON l1.id=l6_1.contacts_contacts_5contacts_ida AND l6_1.deleted=0

LEFT OUTER JOIN contacts l6 ON l6.id=l6_1.contacts_contacts_5contacts_idb AND l6.deleted=0
LEFT JOIN  huge__insurance_carriers_cont_contracting_1_c l7_1 ON cont_contracting.id=l7_1.huge__insurance_carriers_cont_contracting_1cont_contracting_idb AND l7_1.deleted=0

LEFT JOIN  huge__insurance_carriers l7 ON l7.id=l7_1.huge__insucae9arriers_ida AND l7.deleted=0
LEFT JOIN cont_contracting_cstm cont_contracting_cstm ON cont_contracting.id = cont_contracting_cstm.id_c

WHERE (((l1.id='b8a00721-40f1-7801-b4b9-50ce152ce2ec'
) OR (l2.id='b8a00721-40f1-7801-b4b9-50ce152ce2ec'
) OR (l3.id='b8a00721-40f1-7801-b4b9-50ce152ce2ec'
) OR (l4.id='b8a00721-40f1-7801-b4b9-50ce152ce2ec'
) OR (l5.id='b8a00721-40f1-7801-b4b9-50ce152ce2ec'
) OR (l6.id='b8a00721-40f1-7801-b4b9-50ce152ce2ec'
)))

I've been trying to convert this using Laravel's query builder, and this is what I've come up with but it's still not getting the data:

        $appts = DB::table('cont_contracting')
                    ->leftJoin('contacts_cont_contracting_1_c as l1_1', 'cont_contracting.id', '=', 'l1_1.contacts_cont_contracting_1cont_contracting_idb')
                    ->leftJoin('contacts as l1', 'l1.id', '=', 'l1_1.contacts_cont_contracting_1contacts_ida')
                    ->leftJoin('contacts_contacts_12_c as l2_1', 'l1.id', '=', 'l2_1.contacts_contacts_12contacts_ida')
                    ->leftJoin('contacts as l2', 'l2.id', '=', 'l2_1.contacts_contacts_12contacts_idb')
                    ->leftJoin('contacts_contacts_1_c as l3_1', 'l1.id', '=', 'l3_1.contacts_contacts_1contacts_ida')
                    ->leftJoin('contacts as l3', 'l3.id', '=', 'l3_1.contacts_contacts_1contacts_idb')
                    ->leftJoin('contacts_contacts_2_c as l4_1', 'l1.id', '=', 'l4_1.contacts_contacts_2contacts_ida')
                    ->leftJoin('contacts as l4', 'l4.id', '=', 'l4_1.contacts_contacts_2contacts_idb')
                    ->leftJoin('contacts_contacts_4_c as l5_1', 'l1.id', '=', 'l5_1.contacts_contacts_4contacts_ida')
                    ->leftJoin('contacts as l5', 'l5.id', '=', 'l5_1.contacts_contacts_4contacts_idb')
                    ->leftJoin('contacts_contacts_5_c as l6_1', 'l1.id', '=', 'l6_1.contacts_contacts_5contacts_ida')
                    ->leftJoin('contacts as l6', 'l6.id', '=', 'l6_1.contacts_contacts_5contacts_idb')
                    ->leftJoin('huge__insurance_carriers_cont_contracting_1_c as l7_1', 'cont_contracting.id', '=', 'l7_1.huge__insurance_carriers_cont_contracting_1cont_contracting_idb')
                    ->leftJoin('huge__insurance_carriers as l7', 'l7.id', '=', 'l7_1.huge__insucae9arriers_ida')
                    ->leftJoin('cont_contracting_cstm as cont_contracting_cstm', 'cont_contracting.id', '=', 'cont_contracting_cstm.id_c')
                    ->where(function($query) {
                        $query->orWhere('l1.id', '=', Session::get('id'))
                              ->orWhere('l2.id', '=', Session::get('id'))
                              ->orWhere('l3.id', '=', Session::get('id'))
                              ->orWhere('l4.id', '=', Session::get('id'))
                              ->orWhere('l5.id', '=', Session::get('id'))
                              ->orWhere('l6.id', '=', Session::get('id'));
                    })
                    ->get();

What am I doing incorrectly that's causing the Laravel query to not be able to get the records properly?

I've checked the docs up and down multiple times and fail to see the problem.

Thanks

your where clause would be simplified to `where $value in (field1, field2, ..., fieldN)`. this isn't the solution for your problem, but it's one way to reduce the amount of repetition in your query. plus, if your table had been normalized, you could reduce those repeated `contacts` joins down to a single one. — Marc B, Aug 28 '14 at 15:54
@MarcB I'm working with a legacy database and there's nothing I can do about the data because it's hooked up to a company's CRM database. — walter jones, Aug 28 '14 at 16:23
You can execute a 'raw' SQL query in various ways none of which i have tried! 1) [laravel-raw-queries](http://fideloper.com/laravel-raw-queries) using 'DB::select'. Another option: [4687993/laravel-4-how-to-run-a-raw-sql](http://stackoverflow.com/questions/14687993/laravel-4-how-to-run-a-raw-sql). Finally, get the PDO connection and run it all yourself: **DB::connection()->getPdo()**. — Ryan Vincent, Aug 28 '14 at 17:53

Converting large MySQL query to work with Laravel's DB

0 Answers0