On Oct 11, 2013, at 1:25 PM, Gustavo Baratto <gbara...@gmail.com> wrote:
> - is it possible to know which columns in the result of a join belong to each
> of the tables?
>
>
sure:
for row in result:
cols_from_table_a = [row[col] for col in tablea.c]
cols_from_table_b = [row[col] for col in tableb.c]
> - is there a more efficient way of building collections with core? It makes
> me cringe to load large resultsets in memory and iterate over them several
> times :)
>
>
the techinique you describe is what the ORM calls "subquery eager loading".
There is another approach that was pointed out to me by a user, which may make
for a nice new feature someday, which is "IN clause" eager loading.
Subq eager loading looks like this:
SELECT id, data FROM a
SELECT b.id, b.data, b.a_id FROM b JOIN (SELECT id FROM a) AS a_anon ON
b.a_id=a_anon.id
IN clause is like this:
SELECT id, data FROM a
<gather A.ids....>
SELECT b.id, b.data, b.a_id FROM b WHERE b.a_id IN (<list of A ids>)
the IN version is for small parent result sets, much more efficient than the
subq version especially on a JOIN-hostile database like MySQL. For very large
parent result sets, it's not, because the SQL statement grows too large;
databases like Oracle have a hard limit on how many params can be in the IN
clause.
signature.asc
Description: Message signed with OpenPGP using GPGMail
