Hello Michael, thanks a lot again, sorry for not extracting the right parts...I'll try to do that better next time.
The mentioned approach: > lc1 = aliased(LookupSubclassOne) > lc2 = aliased(LookupSubclassTwo) > > query(Building).\ > join(Building.condition,aliased=True).\ > join(lc1, BuildingCondition.rating, from_joinpoint=True).\ > join(Building.condition, aliased=True).\ > join(lc2, BuildingCondition.care_level, from_joinpoint=True).\ > filter(...) leads to the following sql error: 05.07.11 09:28: no such column: building_condition.year_of_costruction_class_id: Could not execute query The reason: There's only a building_condition_1 and a building_condition_2 alias in the query but no building_condition This one: > bc1 = aliased(BuildingCondition) > bc2 = aliased(BuildingCondition) > lc1 = aliased(LookupSubclassOne) > lc2 = aliased(LookupSubclassTwo) > > query(Building).\ > join(bc1, Building.condition).\ > join(lc1, bc1.rating).\ > join(bc2, Building.condition).\ > join(lc2, bc2.care_level).\ > filter(...) (with tuples on 0.6.8) works!!! Yippieh! I know, i ask much...but I have two remaining questions: 1. Why do I have to make two joins on a table, which I need only once? Two joins to Building.condition, although I need only aliased versiones of the lookup table and in sql I need only one join to Building.condition 2. The workinig method produces two times the polymorphic_identity on the second join: It looks like this: SELECT ... FROM building JOIN building_condition AS building_condition_1 ON building.id = building_condition_1.building_id JOIN lookup AS lookup_1 ON building_condition_1.year_of_construction_id = lookup_1.id AND lookup_1.lookup_category_id IN (4) JOIN building_condition AS building_condition_2 ON building.id = building_condition_2.building_id JOIN lookup AS lookup_2 ON building_condition_2.maintenance_state_id = lookup_2.id AND lookup_2.lookup_category_id = 6 AND lookup_2.lookup_category_id IN (6) lookup_category_id is the polymorphic identity. The table def of 'lookup' looks like this: Table('lookup',metadata, Column('id',...), Column('lookup_category_id',Integer(4),ForeignKey('lookup_category.id'), primary_key=True, nullable=False,autoincrement=False), Column('value'...)) The table def of 'building_condition' looks like this: Table('gebaeude_zustand',metadata, Column('gebaeude_id',None,ForeignKey('gebaeude.id'), primary_key=True,autoincrement=False), Column('charakteristik_id',Integer,ForeignKey('lookup.id'),nullable=True), Column('baujahrklasse_id',Integer,ForeignKey('lookup.id'),nullable=True), Column('pflegezustand_id',Integer,ForeignKey('lookup.id'),nullable=True) ) The mapper of BuildingCondition is called with the following properties: properties = { "gebaeude": relationship(Gebaeude, uselist=False), "charakteristik": relationship(Lookup, uselist=False, primaryjoin=and_(tables['gebaeude_zustand'].c.charakteristik_id == tables['lookup'].c.id, tables['lookup'].c.lookup_category_id == 4)), "baujahrklasse": relationship(Baujahrklasse, uselist=False, primaryjoin=tables['gebaeude_zustand'].c.baujahrklasse_id == tables['lookup'].c.id, ), "pflegezustand": relationship(Pflegezustand, uselist=False, primaryjoin=and_(tables['gebaeude_zustand'].c.pflegezustand_id == tables['lookup'].c.id, tables['lookup'].c.lookup_category_id == 6)) } So perhaps I doubled something with the primary join statements inside the properties and the polymorphic_identities? Greets and thanks a lot...if I could send you a beer or something you like let me know ;-) Michael On 4 Jul., 22:57, Michael Bayer <mike...@zzzcomputing.com> wrote: > On Jul 4, 2011, at 3:25 PM, Michael Tils wrote: > > > Here is my mapping, this time in german... > > OK, sifting through lots of extraneous details as well as the lack of the > actual table definitions, it seems like you're looking to join from > Building->BuildingCondition->Lookup. > > I don't use aliased=True very often, but I think its usage pattern would > allow this: > > lc1 = aliased(LookupSubclassOne) > lc2 = aliased(LookupSubclassTwo) > > query(Building).\ > join(Building.condition,aliased=True).\ > join(lc1, BuildingCondition.rating, from_joinpoint=True).\ > join(Building.condition, aliased=True).\ > join(lc2, BuildingCondition.care_level, from_joinpoint=True).\ > filter(...) > > I don't talk about aliased=True often because it has a specific effect on > subsequent modifications to the query, which are then reset on the next call > to join(), and its a little confusing/hard to explain. In this case, the > second and fourth calls to join() add from_joinpoint=True so that it goes > from the previous joinpoint. > > For a full explicit approach, just alias everything: > > bc1 = aliased(BuildingCondition) > bc2 = aliased(BuildingCondition) > lc1 = aliased(LookupSubclassOne) > lc2 = aliased(LookupSubclassTwo) > > query(Building).\ > join(bc1, Building.condition).\ > join(lc1, bc1.rating).\ > join(bc2, Building.condition).\ > join(lc2, bc2.care_level).\ > filter(...) > > note I'm using the 0.7 style of joins here where you can say join(target, > onclause) without an embedded tuple. -- 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.