Tutorial :How to iterate over joined data in two or more nested loops?



Question:

Some time ago I asked a question about nested loops on SO and as it was, there were queries inside the loops of my example and I got a clear answer:

NEVER EVER NEVER put an SQL query inside a loop

I've tried ever since and mostly it works. Just need to make an effort and write a query that retrieves all you need at once.

BUT what do you do when you have a dataset from a JOIN query which contains nested data which you need to output in a nested way?

Example join from table A and B:

A.a     |  B.a     |  B.b  --------|----------|-------------  fruits  |  banana  |  yellow  fruits  |  apple   |  red  animals |  zebra   |  black&white  animals |  elefant |  gray  animals |  fox     |  red  planets |  earth   |  blue  planets |  mars    |  red  

ok, now I got that all in an array or rowset and now I need to display something like that:

fruits

  • yellow banana
  • red apple

animals

  • black&white zebra
  • gray elefant
  • red fox

planets

  • blue earth
  • red mars

it seems obvious that it should work but I've tried to wrap my mind around it several times now and I just can't come up with a solution.

At the moment I do it my old way:

query groups    foreach groups  {      query animals in group      foreach animal  }

but hey, NEVER EVER NEVER put sql inside a loop. so what shold I do? I do PHP but I think this is a meta question.


Solution:1

Use the control break algorithm.

I'd return a result set exactly as you show in the question:

A.a     |  B.a     |  B.b  --------|----------|-------------  fruits  |  banana  |  yellow  fruits  |  apple   |  red  animals |  zebra   |  black&white  animals |  elefant |  gray  animals |  fox     |  red  planets |  earth   |  blue  planets |  mars    |  red  

loop over all the rows:

  • when A.a changes, output the title
  • then always output the B.b + B.a value

pseudo code for application calling SQL:

set last_A = null  exec query    loop over result set {      if last_A == null or fetch_A!=last_A {          last_A=fetch_A          display fetch_a      }      display fetch_Bb + fetch_Ba      }  }//loop  


Solution:2

If what you have is a hierarchy, a "directed acyclic graph". SQL does not do these.

There are other graph-theory things SQL does not do.

Since SQL does not do this, the "never put SQL in a loop" rule goes out the window.

You must put the SQL in a loop for hierarchies and other graph-connection problems involving lattices and networks.

Indeed, for hierarchies, you must use recursive loops to connect all elements of the hierarchy to arbitrary depth.

If, on the other hand, you're just reformatting the query result to look like a nested hierarchy, then you're just reformatting a single SQL result set into what appears to be nested lists.

This will be one select with complex loops around the result set. One select -- not in a loop -- and a complex loop to process one result set.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »