CodeIgniter Forums
Optimization on Nested Loop - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forum-5.html)
+--- Forum: Best Practices (https://forum.codeigniter.com/forum-12.html)
+--- Thread: Optimization on Nested Loop (/thread-69369.html)



Optimization on Nested Loop - codeigniter_guru - 11-10-2017

I have some concerns on Nested loop with sub query for example i have a main query that states like this


$main_query = "select fname,lname,dept from employee AS e INNER JOIN dept AS d on d.emp_id = e.emp_id"

foreach($main_query as $val):

then adding some query loops like
$sub_query = select sum(users) as total from assignees where emp_id = {$val->emp_id}


echo $sub_query ? $sub_query->total : "0";


endforeach;

is this a bad habit? My fix for this was to create and array on all assignments then loading it once then on the forloop i just called the array and check if its equal then i'll get the data on it?

is this the best approach?


RE: Optimization on Nested Loop - Narf - 11-10-2017

Best approach is to avoid queries inside loops, at all costs.


RE: Optimization on Nested Loop - PaulD - 11-10-2017

I would say that sometimes I have reverted to a loop through one results doing more database queries on one of the values. Although only when I know the loop will only have a few items in it.

Generally it is a bad idea. If the first query returns 50 results you are now doing 50 database queries in your loop. What if it returns 5000 results at some point in the future?

The answer is to do a more complex SQL query, where one of your selects has a sub query in it. Personally I find these very difficult to get the format right, so I cannot give an example.

Here are some examples from a quick google search: http://beginner-sql-tutorial.com/sql-subquery.htm

May not be an example in there for what you are looking for but the point is not to do looped database queries, but to do better sql queries in the first place.

Hope that helps,

Paul

PS Disclaimer - I am no expert on this stuff at all.
PSS Narf posted above while I was writing. It seems it is right that you should avoid looped queries.


RE: Optimization on Nested Loop - Narf - 11-10-2017

Well, I said best approach, but that was an understatement.
Database querying inside a loop is an inherently bad practice.


RE: Optimization on Nested Loop - averroez - 11-14-2017

bad idea as Narf said queries in loop are bad practice, as I see you are trying to get employees with total users assigned group by query would be ideal in this case.

this might help you

https://stackoverflow.com/questions/17848886/group-by-two-columns-and-display-grand-total-in-every-row