Question:

Why is the Larvel query builder query taking 20 seconds to execute?

Problem

I have a Laravel query builder query which is taking more than 20 seconds to retrieve ~2500 records. Ideally, it should take much less time. I think the orOn join in my query is what is causing this issue. Am not able to figure out how can I optimize this query to execute much more quickly

DB::table('customer as c')->leftJoin('feedback as fr',

function($leftJoin){

$leftJoin->on('c.active_feedback_req_id_1','=','fr.id')

->orOn('c.active_feedback_req_id_2','=', 'fr.id')

->orOn('c.active_feedback_req_id_3','=', 'fr.id');

})

->leftJoin('feedback_request_status as frs', 'fr.feedback_request_status_id', 'frs.id')

->leftJoin('customer_service as cs', 'c.id', 'cs.customer_id')

->where('c.business_location_id', $request->location_id)

->where('c.is_active', 1)

->select('c.id as customer_id','c.name as customer_name','c.created_at as customer_creation_date','c.email as customer_email', 'c.phone as customer_phone','c.phone_extension as customer_phone_extension','c.whatsApp as customer_whatsApp','c.whatsApp_phone_code as customer_whatsApp_extension','active_feedback_req_id_email as email_req','active_feedback_req_id_sms as sms_req','active_feedback_req_id_whatsApp as whatsApp_req','fr.service_taken as service_taken','fr.service_provider as service_provider', 'fr.reminders_sent as reminders_sent', 'frs.status as status','frs.color_code as color_code', 'c.is_active as active_status', 'c.is_unsubscribed as is_unsubscribed','cs.service_taken as customer_service_taken', 'cs.service_provider as customer_service_provider')

->orderBy('c.created_at','DESC')

->groupBy('c.id')

->get();

Please let me know if anyone can help with this.

Solution

I think for this you can use eager loading for all of these leftjoins you can define relationship functions inside the customer module.

I think you should treat them as separate relationships and call themes like these.

Customer::with('getActiveFeedback1','getActiveFeedback2','getActiveFeedback3','getFeedbackRequestStatus','getCustomerService','getbusinesslocation')->where('is_active',1)->orderBy('created_at','DESC')->groupBy('id')->get();

Answered by: >Ankit Shekhawat

Credit: >StackOverflow

Blog Links:

>How to manage the Text in the container in Django?

>Fix webapp stops working issue in Django- Python webapp

>Creating a form in Django to upload a picture from the website

>Sending Audio file from Django to Vue

>How to keep all query parameters intact when changing page in Django?

>Solved: TaskList View in Django

>Implement nested serializers in the Django rest framework

>How to filter events by month in Django?

>Sorting the restframework in Django