On May 27, 2009, at 12:23 PM, Nathan Harmston wrote:
>
> Hi,
>
> I have been struggling with trying to create relations which reference
> objects a couple of tables away.
>
> e.g
>
> Sentence has many entities
> Entity has many NormalisedVersion
> NormalisedVersion has one Gene
>
> kind of thing
>
> and was trying to link from Sentence to genes directly.
>
> secondary = entities_table.join(entities_genes_normalised_table,
> onclause
> =
> and_
> (entities_table
> .c.entity_id==entities_genes_normalised_table.c.entity_id,
> entities_table.c.deleted == 0 )).alias("fubar")
>
> mapper(Sentence, sentences_table, properties={ "genes":relation(Gene,
> primaryjoin=sentences_table.c.sentence_id ==
> secondary.c.entities_sentence_id,
>
> secondaryjoin=secondary.c.entities_genes_normalised_entrez_gene_id ==
> genes_table.c.entrez_gene_id,
>
> viewonly = True,
>
> secondary=secondary)
>
> I spent ages on this hacking/reading/getting frustrated and finally
> tried putting the alias call on the end of the secondary join, which
> makes it work perfectly.
> Without it calling sentence.genes gives me all of the genes found in
> all of the sentences. So my question is really ... why does this work?
> What effect does the alias have?
the alias has the effect of converting the join expression to a fully
parenthesized subquery with a name, thus encapsulating its data as a
relation (a relation in the Codd sense of the word). its the
difference between:
select * from table, someothertable join somethirdtable on
someothertable.id=somethirdtable.foo where table.id=somethirdtable.bar
and
select * from table, (select * from someothertable join somethirdtable
on someothertable.id=somethirdtable.foo) as anon_1 where table.id =
anon_1.bar
in the first case, we have a FROM clause that isn't even legal on many
databases. in the second we have a FROM clause that selects from two
distinct selectables.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
