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 <[email protected]> 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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
