optimizing db queries

[Vladimir Makarov]

Hi there!

I have a few tables:

db.trucks        (id, num ...)

db.operations (id, truck_id ...)

db.controls     (id, operation_id ...)

There is one to many relation: unique db.trucks.num corresponds to many operations and the only db.operations.id corresponds to many controls. 

I can do search all operations with exact truck number this way:

rows1 = db(db.trucks.num.like('%'+str(request.vars.truck)+'%')).select(join=db.operations.on(db.trucks.id == db.operations.truck_id), orderby=~db.operations.id, limitby=limitby)

And then I easily can export the rows to *.xls file - one excell sheet for selected operations by truck number.

It works fine, but not so well if I need to add another sheet to that *.xls and export new set containing all controls belonging to selected before operations.

rows2 = db(db.trucks.num.like('%'+str(request.vars.truck)+'%')).select(join=db.operations.on(db.trucks.id == db.operations.truck_id), left=db.controls.on(db.controls.operation_id == db.operations.id), orderby=~db.operations.id) 

So, there are two queries to fill two excell work sheets. And now I have to show only rows1 into the corresponding view but rows2 is only for export to *.xls.

To perform the export I call another def with thoose two selects. For now there are 4 selects.

I guess I can reduce number of queries. Can somebody give me a clue of how to export selected rows without another selects? 

Por favor amigos!!!