Convert Joins to Subqueries in SQL (laravel controller )

Question

I have more than two tables, while fetching 2,00,000 records from database taking too much time what I am expecting, so I need to convert it to sub queries?

The following code:

->join('track_details','track_details.code','=',$esealTable.'.primary_id')
->join('track_history as th','th.track_id','=','track_details.track_id')
->join('locations as l','l.location_id','=','th.src_loc_id')
->join('products','products.product_id','=',$esealTable.'.pid')
->where(['level_id'=>0, 'products.product_type_id'=>8003])
->whereIn('l.location_type_id',[741,744])
->whereIn('th.transition_id',[537,569]);
->get(['primary_id as iot','products.material_code','th.sync_time as datetime'])
->take(200000);

I think 2,00,000 is not too much while you fetch data from query. But it takes time while you process data with custom methods and loops. — Sagar Gautam
– Sagar Gautam, Commented Jul 3, 2017 at 5:30
yeah thanks @Sagar Gautam Actually i have array_map() and array_unique methods while retrieving this data to json. — Prashanth vadla
– Prashanth vadla, Commented Jul 3, 2017 at 5:39
So, you have to look for better way to retrieve data at first rather than query optimization — Sagar Gautam
– Sagar Gautam, Commented Jul 3, 2017 at 5:43
By the way when i use order by clause to particular query it will taking too much time, without order by clause, it is faster. — Prashanth vadla
– Prashanth vadla, Commented Jul 3, 2017 at 6:11

Sagar Gautam · Accepted Answer · 2017-07-03 05:55:03Z

1

If you need all these data retrieved with query in json,

Laravel provides toJson() function similar to toArray() function. See docs here. You just need to add toJson() at the end of query like this:

->join('track_details','track_details.code','=',$esealTable.'.primary_id')
...
...
..
->get(['primary_id as iot','products.material_code','th.sync_time as datetime'])
->take(200000)
->toJson();

In this way, you can directly convert fetched data to json format.

answered Jul 3, 2017 at 5:55

Sagar Gautam

9,4099 gold badges62 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert Joins to Subqueries in SQL (laravel controller )

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related