How to explicit name aliased classes in joins of orm.query
Michael Tils
I have a problem to understand how aliased classes are used in
orm.query.
I have an polymorphic single-inheritance table, which is a lookup
table for about 10 other tables.
On some of that tables there are more than one column which relates to
that table, so they have to be aliased.
The SQL query would be something like:
SELECT * FROM building
JOIN condition ON building.id = condition.building_id
JOIN lookup_1 ON condition.rating_id = lookup_1.id AND
lookup_1.category_id = 6
JOIN lookup_2 ON condition.care_level_id = lookup_2.id AND
lookup_2.category_id = 7
WHERE lookup_1.value LIKE "%good"
OR lookup_2.value LIKE "well-groomed"
This leads to several problems, probably I'm using the wrong approach
because I miss something. I didn't found something on google or
sqlalchemy docs...
If I do sonething like this:
query =
session.query(Building).join('condition','rating').filter(BuildingRating.value.like('%good'))
query
=query.join('condition','care_level').filter(BuildingCareLevel.value.like('well-
groomed'))
This won't work because the lookup table appears in two parts of the
query. So it has to be aliased:
query = session.query(Building).join('condition','rating',
aliased=True).filter(BuildingRating.value.like('%good'))
query =query.join('condition','care_level',
aliased=True).filter(BuildingCareLevel.value.like('well-groomed'))
Solves this problem, but it creates an AND condition between both
criterias. So I need the aliased Object like:
rating = aliased(BuildingRating)
care_level = aliased(BuildingCareLevel)
to make later:
.....query(Building).filter( or_( rating.value.like("%good"),
care_level.value.like("well_groomed") ) )
But I couldn't work out how to explicit name the aliased classes or
get the aliased name. If I could do something like:
rating = aliased(BuildingRating)
care_level = aliased(BuildingCareLevel)
query = session.query(Building).join('condition',
rating).join('condition', care_level)
query = query.filter( or_( care_level.value.like('well-groomed'),
rating.value.like('%good') ) )
The problem would be solved or is there another way to do that?
Thanks a lot
Michael
(I use sqlalchemy 0.6.8 on linux with sqlite and python 2.6.5)
Michael Bayer
On Jul 4, 2011, at 2:29 AM, Michael Tils wrote:
>
> rating = aliased(BuildingRating)
> care_level = aliased(BuildingCareLevel)
>
> to make later:
>
> .....query(Building).filter( or_( rating.value.like("%good"),
> care_level.value.like("well_groomed") ) )
>
> But I couldn't work out how to explicit name the aliased classes or
> get the aliased name. If I could do something like:
>
> rating = aliased(BuildingRating)
> care_level = aliased(BuildingCareLevel)
>
> query = session.query(Building).join('condition',
> rating).join('condition', care_level)
> query = query.filter( or_( care_level.value.like('well-groomed'),
> rating.value.like('%good') ) )
this is nearly a valid query. join is called as in join(rating, Building.condition), join(care_level, Building.condition). Assuming BuildingRating and BuildingCareLevel are both single table off of whatever Building.condition is it should be straightforward.
Michael Tils
Michael Bayer
--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/Z-JEIbJEInkJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Michael Tils
Michael Bayer
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.
Michael Tils
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(...)
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(...)
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),
uselist=False,
primaryjoin=and_(tables['gebaeude_zustand'].c.charakteristik_id ==
tables['lookup'].c.id,
tables['lookup'].c.lookup_category_id == 4)),
uselist=False,
primaryjoin=tables['gebaeude_zustand'].c.baujahrklasse_id ==
tables['lookup'].c.id,
),
uselist=False,
primaryjoin=and_(tables['gebaeude_zustand'].c.pflegezustand_id ==
tables['lookup'].c.id,
tables['lookup'].c.lookup_category_id == 6))
}
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
Michael Bayer
On Jul 5, 2011, at 4:25 AM, Michael Tils wrote:
> Hello Michael,
>
> thanks a lot again, sorry for not extracting the right parts...I'll
> try to do that better next time.
>
>
>> 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
ah good point. yes you only need BuildingCondition once really
query(Building).join(Building.condition).join(lc1, Building.rating).join(lc2, Building.care_level)
should work too.
>
> 2. The workinig method produces two times the polymorphic_identity on
> the second join:
> It looks like this:
it seems like you have "lookup_category_id=6" in your primary join conditions. relationship() when presented with A->C, where C is a single table subclass of B, will always stick the " discriminator IN (C, <descendants of C>)" on the end of a JOIN condition.