[sqlalchemy] Re: Absurd operational error in SQLite
What you're really doing here is creating a multiply inherited class on the SQL side, which is a use case SQLA was not at all designed to handle.So the fact that RatableRecord inherits from Soup here is not ideal since its really a (Ratable, Record). The specific concept we have no test coverage or support for is that a row in ratings and a row in records, both subclasses of soup, might share the same primary key. Additionally, SQLA when constructing the join() for joined table inheritance assumes the joinable units can be strung together like plain tables (all the test coverage involves plain tables as targets), so here we have to bypass that functionality altogether. It can work for the purposes of this example by forcing a particular select() statement from the mapping: orm.mapper(RatableRecord, ratable_record, with_polymorphic=([RatableRecord], ratable_record.join(soup, records.c.id==soup.c.id).select().apply_labels().alias()), inherits=Soup, inherit_condition=(records.c.id==soup.c.id)) I can't speak for how far you can go with this (meaning functions which construct joins like query.join() as well as eager loading may or may not have further issues), and you lock yourself in to a little more SELECT nesting than might be strictly necessary at a straight SQL level. The existence of the RatableRecord class and its mapping probably makes SQLA's polymorphic loading (i.e., from Soup on upwards) a non-option here, but might work to some degree if you specified with_polymorphic() on all mappers to force what it selects from in all cases. On Jun 14, 2008, at 6:10 AM, Malthe Borch wrote: Michael Bayer wrote: oh. how are you getting it to join from soup- (album join vinyl) ? soup has a relation to album join vinyl and you're using query.join() ? it should be creating an aliased subquery for the right side of the join in that case. I thought 0.4 was able to do this; 0.5 definitely can. Attached is the example script from my previous thread, adapted to show the present issue. The setup is basically this: ratable_record = records.join( ratings, onclause=(ratings.c.id==records.c.id)) orm.mapper(RatableRecord, ratable_record, inherits=Soup, inherit_condition=(records.c.id==soup.c.id)) \malthe example.tar.gz --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Absurd operational error in SQLite
Michael Bayer wrote: oh. how are you getting it to join from soup- (album join vinyl) ? soup has a relation to album join vinyl and you're using query.join() ? it should be creating an aliased subquery for the right side of the join in that case. I thought 0.4 was able to do this; 0.5 definitely can. Attached is the example script from my previous thread, adapted to show the present issue. The setup is basically this: ratable_record = records.join( ratings, onclause=(ratings.c.id==records.c.id)) orm.mapper(RatableRecord, ratable_record, inherits=Soup, inherit_condition=(records.c.id==soup.c.id)) \malthe --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~--- example.tar.gz Description: GNU Zip compressed data
[sqlalchemy] Re: Absurd operational error in SQLite
On Jun 13, 2008, at 5:43 AM, Malthe Borch wrote: When executing a query on some joined SQLA-mapper, SQLite throws the following exception (unlike Postgres, which handles it just fine): OperationalError: (OperationalError) no such column: album.id Here's the query: SELECT album.id AS album_id FROM soup JOIN (album JOIN vinyl ON vinyl.id = album.id) ON vinyl.id = soup.id How would you interpret this? Help much appreciated. sqlite doesn't like the parenthesis. when making the joins with a SQLA join() construct, you need to make the joins from left to right, i.e.: soup.join(album, ...).join(vinyl, ...) as opposed to: soup.join(album.join(vinyl, ...), ...) just a little taste of my world ! :) --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Absurd operational error in SQLite
Michael Bayer wrote: sqlite doesn't like the parenthesis. when making the joins with a SQLA join() construct, you need to make the joins from left to right, i.e.: soup.join(album, ...).join(vinyl, ...) as opposed to: soup.join(album.join(vinyl, ...), ...) Actually, we are sort of doing this already --except-- due to your previous advice, we're now using the ``inherits``-option to automatically have SQLA figure out the correct unit-of-work order. With this option, the above join results in this query: SELECT album.id AS album_id FROM soup JOIN (album JOIN vinyl ON vinyl.id = album.id) ON vinyl.id = soup.id --instead of-- SELECT album.id AS album_id FROM soup JOIN album on soup.id = album.id JOIN vinyl ON vinyl.id = soup.id That is, SQLA seems to make a left join (or whatever it is) by itself. How can tell it do this differently? just a little taste of my world ! :) :-) \malthe --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Absurd operational error in SQLite
On Jun 13, 2008, at 12:45 PM, Malthe Borch wrote: Actually, we are sort of doing this already --except-- due to your previous advice, we're now using the ``inherits``-option to automatically have SQLA figure out the correct unit-of-work order. With this option, the above join results in this query: SELECT album.id AS album_id FROM soup JOIN (album JOIN vinyl ON vinyl.id = album.id) ON vinyl.id = soup.id --instead of-- SELECT album.id AS album_id FROM soup JOIN album on soup.id = album.id JOIN vinyl ON vinyl.id = soup.id That is, SQLA seems to make a left join (or whatever it is) by itself. How can tell it do this differently? oh. how are you getting it to join from soup- (album join vinyl) ? soup has a relation to album join vinyl and you're using query.join() ? it should be creating an aliased subquery for the right side of the join in that case. I thought 0.4 was able to do this; 0.5 definitely can. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
