On Tuesday, January 6, 2015 1:09:05 PM UTC-8, Przemysław wrote:
>
> Hallo!
>
Hello! Partial response below ...
> 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.
>
I would think using the "groupby" would simplify this. You still need to
iterate to collapse the entries, but you don't need to make multiple passes.
> 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
>
>
/dps
--
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.
