avoid separate query within second nested loop

[Alex Glaros]

Want to avoid separate query executed every time control gets to the second loop.  Is this the right way?

Build two sets from two separate joins, one for the parent record and one for the children.  Then run the parent join for the parent loop and the child join in the 2nd, nested loop

Parent_Data = db(db.parent_record.style=='yellow'),select()

Child_Data = db(db.parent_record.id == db.child_record.parent_id).select()

 

For r in Parent _Data

    print r.parent_name

   For n in Child_Data

       if n.child_record.parent_id == r.id

           print child_record.name

Or is there a way to make only one big join and somehow use a group_by in the parent, and undo the group_by when printing the child records in the nested loop below?

thanks,

Alex Glaros

[Niphlod]

is it better to issue two separate queries returning a set with 2 records and 10 respectively or to fetch a joined set (and massage it later on) that extracts 20 records ? There's no answer to that except that the two possibilities are entirely doable. Cardinality of the sets comes into play, plus the number of columns on either tables. In one word, there's no silver bullet.

The problem is, the "sane options" are either:

a) distinct sets

q = db.parent_record.style == 'yellow'

parents = db(q).select()

childs = db(db.child_record.parent_id.belongs(db(q)._select(db.parent_record.id))).select()

b) joined set

parent_and_childs = db((db.parent_record.style=='yellow') & (db.parent_record.id == db.child_record.parent_id)).select() 

what you do instead is fetching the parents AND then the joined set....... your second query extracts all the info already

[Alex Glaros]

did you mean I only need the second query?  I know it already extracts data that query one has, but I didn't know how to iterate using only the second one and not redundantly display the parent values.  

[Niphlod]

it's just a matter of ordering by the parent and then the child. Once you get a resultset similar to

parent.id | parent.style | child.id | child.name

1           yellow         4          foo1

1           yellow         5          bar1

1           yellow         6          baz1

2           yellow         7          foo2

2           yellow         8          bar2

2           yellow         9          baz2

there won't be issues, right ? you'll be looping through childs in the same exact order of your nested loop.

[Alex Glaros]

I get the ordering but parent appears redundantly.  

Do I iterate through loop checking for change in parent.id so parent only gets displayed first time?

if parent.id != previous_parent_id

    display this specific parent.id one time only

   previous_parent_id = parent.id ## reset the comparison value that checks for change in parent.id

else:

    display child.name ## all the children get listed under the parent

[Dave S]

On Friday, June 12, 2015 at 2:12:51 PM UTC-7, Alex Glaros wrote:

I get the ordering but parent appears redundantly.  

Do I iterate through loop checking for change in parent.id so parent only gets displayed first time?

if parent.id != previous_parent_id

    display this specific parent.id one time only

   previous_parent_id = parent.id ## am resetting the comparison value that checks for change in parent.id

else:

    display child.name

I /think/ you want to move the display child.name outside the if-else, or you may be losing 1 child name.

/dps

[Niphlod]

so you have a problem with python, not the database!

if you're not "loop-proficient", massage it first to a dictionary, then loop over it

from collections import defaultdict

resultset = defaultdict(list)

for row in parents_and_childs:

    resultset[row.parent_record.id].append(row)

then you can

for k,v in resultset.iteritems():

     print v[0].parent_record.name

     for child in v:

         print child.child_record.name

etc etc etc

[Alex Glaros]

are there file size limitations when moving to dict that are not an issue if python loop?

[Niphlod]

sorry.... what ?????

[Alex Glaros]

is there a size limitation to a dictionary? (want to know in general for future purposes)

is there consequential extra overhead if using the dictionary method? 

thanks

Alex

[Niphlod]

On Sunday, June 14, 2015 at 11:56:34 PM UTC+2, Alex Glaros wrote:

is there a size limitation to a dictionary? (want to know in general for future purposes)

generally, your OS free memory.

 

is there consequential extra overhead if using the dictionary method? 

of course, its an additonal loop