Hallo!
Lets say we have a table called 'cities' and another one called
'institutions' with a field referencing to cities:
db.define_table('cities',
Field('name'))
db.define_table('institutions',
Field('name'),
Field('city', 'reference cities')
Field('type', 'reference types'))
db.define_table('types',
Field('name'))
Then every city from the first table may have many institutions referencing
to it.
The question is how to select all the cities from the first table with all
their institutions from the second table in the most efficient way. The
final result should be the list of cities appearing only once and the list
of their institutions. Something like this:
[city A, [institution A, institution B, institution C]]
[city B, [institution D, institution E]]
[city C, [institution F, institution G, institution H]]
The first idea is to do a query on these two tables with left join to
select all the stuff at once. After that however we have many rows with the
same city (if it has many institutions) so it is necessary to do a loop to
gather records from these rows together.
The second idea is to do a simple query on the first table to select just
all the cities. Then inside a loop we do a query for institutions belonging
to each city.
Both these two ways have disadvantages:
The first one needs additional data analysing on Python code side (not by
the database engine) so it is supposed to be slower.
Second one generates many queries what doesn't seem to be efficient.
Additionally we need to know the type name for each institution from the
second table (not just id of 'type'). Using 'institution.type.name' will do
another query for each record if I'm not mistaken.
So what is the best way to select and format all the data in the most
efficient way?
Przemek
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
