Hi there,
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)
--
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.
