[sqlalchemy] Re: filter() on inherited class doesn't point to the correct table
Michael Bayer wrote: that doesnt sound right. taking out select_table, and doing: print Media .query .select_from (media_table .join (catalog_table ).join (catalog_channel_table )).filter(CatalogChannel.c.id_channel==playlist.id_channel).all() leads to the SQL: SELECT medias.id AS medias_id, medias.name AS medias_name, medias.id_catalog AS medias_id_catalog FROM medias JOIN catalogs ON catalogs.id = medias.id_catalog JOIN catalog_channels ON catalogs.id = catalog_channels.id WHERE catalog_channels.id_channel = ? ORDER BY medias.oid which is entirely acceptable (and works in mysql). Okay, my fault. I was editing the wrong code in my test case concerning the select_from alternative. Let's go back to that new feature from r4060 you've proposed, concerning joins directly from classes. Attached is my updated test case. If I wanted to join only from classes, I'd suppose we'd have the following syntax: Media.query.join([Media.catalog, CatalogChannel.id, CatalogChannel.channel]).filter(CatalogChannel.c.id_channel==playlist.id_channel).all() This would mean join Media on Catalog, join CatalogChannel on Catalog, join CatalogChannel on Channel; WHERE CatalogChannel.c.id_channel==foo. This is just an idea, but here the join with CatalogChannel.id would mean that is need to figure out that PK is also FK to Catalog. Regards, -- Alexandre CONRAD --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- SA_joined_inherited_class.py Description: application/python
[sqlalchemy] Re: filter() on inherited class doesn't point to the correct table
On Jan 14, 2008, at 10:44 AM, Alexandre Conrad wrote: Michael Bayer wrote: I came up with a very easy way to implement this which I'd like you to try out in rev 4060. So far I've tested with a basic joined table inheritance setup. All you do is place class-mapped attributes directly in the join() arguments along with strings. and thats it ! so it looks like: session.query(Media).join(['catalog', CatalogChannel.channel]) The class-attributes can be freely intermixed with the existing string- based attributes. Technically you can place any class-attribute you want in there but it only makes sense if it can be joined against the immediately preceding attribute or parent mapper. I'm not sure if that restriction could change eventually. model.Media.query.join([catalog, model .CatalogChannel .channel ]).filter (model.CatalogChannel.c.id_channel==c.playlist.id_channel).all() generates: SELECT files.id AS files_id, medias.id AS medias_id, files.name AS files_name, files.mime AS files_mime, files.date AS files_date, files.size AS files_size, files.checksum AS files_checksum, files.description AS files_description, files.type AS files_type, medias.id_catalog AS medias_id_catalog FROM catalog_channels, files INNER JOIN medias ON files.id = medias.id INNER JOIN catalogs ON catalogs.id = medias.id_catalog INNER JOIN channels ON channels.id = catalog_channels.id_channel WHERE catalog_channels.id_channel = %s ORDER BY files.name whats that filter() trying to accomplish ? please work this into a full test case using SQLite so I can run through it. --~--~-~--~~~---~--~~ 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: filter() on inherited class doesn't point to the correct table
On Jan 14, 2008, at 10:44 AM, Alexandre Conrad wrote: model.Media.query.join([catalog, model .CatalogChannel .channel ]).filter (model.CatalogChannel.c.id_channel==c.playlist.id_channel).all() is CatalogChannel.channel a self referential relation to another Channel ? the classes you use in filter() are usually of the type that channel would be here. generates: SELECT files.id AS files_id, medias.id AS medias_id, files.name AS files_name, files.mime AS files_mime, files.date AS files_date, files.size AS files_size, files.checksum AS files_checksum, files.description AS files_description, files.type AS files_type, medias.id_catalog AS medias_id_catalog FROM catalog_channels, files INNER JOIN medias ON files.id = medias.id INNER JOIN catalogs ON catalogs.id = medias.id_catalog INNER JOIN channels ON channels.id = catalog_channels.id_channel WHERE catalog_channels.id_channel = %s ORDER BY files.name which returns, with MySQL: 1054, Unknown column 'catalog_channels.id_channel' in 'on clause' also the actual error here is a known MySQL 5 issue, but the SQL is still not what SQLAlchemy would want to produce. --~--~-~--~~~---~--~~ 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: filter() on inherited class doesn't point to the correct table
On Jan 14, 2008, at 12:04 PM, Michael Bayer wrote: On Jan 14, 2008, at 11:59 AM, Michael Bayer wrote: On Jan 14, 2008, at 10:44 AM, Alexandre Conrad wrote: model.Media.query.join([catalog, model .CatalogChannel .channel ]).filter (model.CatalogChannel.c.id_channel==c.playlist.id_channel).all() like, are you sure you dont want to just say: model .Media .query .join ([catalog ]).filter (model.CatalogChannel.c.id_channel==c.playlist.id_channel).all() ? that is if you want the Media which contains a CatalogChannel with a certain id_channel. otherwise im still having trouble wrapping my head around what youre trying to do there. oh also, this might be complicating thingsthe above query I just gave you will only work at the moment if you are using select_table on your Catalog mapper to define a polymorphic join. Its possible that you could define select_table and just go with the above, simpler query (if my particular guess here is correct as to what youre trying to accomplish). or if not using select_table, force it manually like this: session .query (Media ).select_from (files .join (media ).join (catalog).join(catalog_channel)).filter(CatalogChannel.id_channel==foo) --~--~-~--~~~---~--~~ 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: filter() on inherited class doesn't point to the correct table
Mike, Michael Bayer wrote: a certain id_channel. otherwise im still having trouble wrapping my head around what youre trying to do there. Enough guessing, here's the full test case with description of what I'm trying to do. :) Although, I wasn't able to make it run with sqlite, so it's running a under a foo table with MySQL. (another hidden bug, I'm getting (IntegrityError) medias.id may not be NULL). Regards, -- Alexandre CONRAD --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- SA_joined_inherited_class.py Description: application/python
[sqlalchemy] Re: filter() on inherited class doesn't point to the correct table
On Jan 14, 2008, at 12:12 PM, Alexandre Conrad wrote: Mike, Michael Bayer wrote: a certain id_channel. otherwise im still having trouble wrapping my head around what youre trying to do there. Enough guessing, here's the full test case with description of what I'm trying to do. :) Although, I wasn't able to make it run with sqlite, so it's running a under a foo table with MySQL. (another hidden bug, I'm getting (IntegrityError) medias.id may not be NULL). thats not hidden. SQLite cannot autoincrement primary key columns if the table contains a composite primary key which is the case here. --~--~-~--~~~---~--~~ 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: filter() on inherited class doesn't point to the correct table
On Jan 14, 2008, at 11:59 AM, Michael Bayer wrote: On Jan 14, 2008, at 10:44 AM, Alexandre Conrad wrote: model.Media.query.join([catalog, model .CatalogChannel .channel ]).filter (model.CatalogChannel.c.id_channel==c.playlist.id_channel).all() like, are you sure you dont want to just say: model .Media .query .join ([catalog ]).filter (model.CatalogChannel.c.id_channel==c.playlist.id_channel).all() ? that is if you want the Media which contains a CatalogChannel with a certain id_channel. otherwise im still having trouble wrapping my head around what youre trying to do there. oh also, this might be complicating thingsthe above query I just gave you will only work at the moment if you are using select_table on your Catalog mapper to define a polymorphic join. Its possible that you could define select_table and just go with the above, simpler query (if my particular guess here is correct as to what youre trying to accomplish). --~--~-~--~~~---~--~~ 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: filter() on inherited class doesn't point to the correct table
Alexandre Conrad wrote: under a foo table with MySQL. (another hidden bug, I'm getting a foo schema/db, not table. -- Alexandre CONRAD --~--~-~--~~~---~--~~ 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: filter() on inherited class doesn't point to the correct table
On Jan 14, 2008, at 10:44 AM, Alexandre Conrad wrote: model.Media.query.join([catalog, model .CatalogChannel .channel ]).filter (model.CatalogChannel.c.id_channel==c.playlist.id_channel).all() like, are you sure you dont want to just say: model .Media .query .join ([catalog ]).filter (model.CatalogChannel.c.id_channel==c.playlist.id_channel).all() ? that is if you want the Media which contains a CatalogChannel with a certain id_channel. otherwise im still having trouble wrapping my head around what youre trying to do there. --~--~-~--~~~---~--~~ 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: filter() on inherited class doesn't point to the correct table
Michael Bayer wrote: thats not hidden. SQLite cannot autoincrement primary key columns if the table contains a composite primary key which is the case here. Ah, yes. Plus, I don't need composite primary_key here, it's articact from an old many-to-many secondary table (which I usually set as primary_key=True on both columns). Regards, -- Alexandre CONRAD --~--~-~--~~~---~--~~ 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: filter() on inherited class doesn't point to the correct table
On Jan 14, 2008, at 12:12 PM, Alexandre Conrad wrote:
Mike,
Michael Bayer wrote:
a certain id_channel. otherwise im still having trouble wrapping my
head around what youre trying to do there.
Enough guessing, here's the full test case with description of what
I'm
trying to do. :)
Although, I wasn't able to make it run with sqlite, so it's running a
under a foo table with MySQL. (another hidden bug, I'm getting
(IntegrityError) medias.id may not be NULL).
ok, you can also do it like this:
select_table =
catalog_table.outerjoin(catalog_channel_table).select().alias('pjoin')
catalog_mapper = mapper(Catalog, catalog_table,
select_table=select_table, polymorphic_on=catalog_table.c.type,
polymorphic_identity=catalog,
properties={
medias:relation(Media,
backref=catalog,
cascade=all, delete-orphan,
),
},
)
print
Media
.query
.join
('catalog').filter(CatalogChannel.id_channel==playlist.id_channel).all()
Still working on getting select_table to not have to be aliased like
that.
--~--~-~--~~~---~--~~
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: filter() on inherited class doesn't point to the correct table
Michael Bayer wrote: your best bet with this mapping right now is: print Media .query .select_from (media_table .join (catalog_table ).join (catalog_channel_table )).filter(CatalogChannel.c.id_channel==playlist.id_channel).all() which is really how select_from() was intended to be used. This works with SQLite, but not MySQL: (1054, Unknown column 'catalog_channels.id_channel' in 'on clause') You'd say this is a MySQL bug ? Darn... Also, select_from still makes us play with tables. At first, I was looking at an alternative to fully use classes rather than tables for doing the joins. I remember I already played with that select_from syntax, which was working. Regards, -- Alexandre CONRAD --~--~-~--~~~---~--~~ 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: filter() on inherited class doesn't point to the correct table
On Jan 14, 2008, at 12:12 PM, Alexandre Conrad wrote: Mike, Michael Bayer wrote: a certain id_channel. otherwise im still having trouble wrapping my head around what youre trying to do there. Enough guessing, here's the full test case with description of what I'm trying to do. :) Although, I wasn't able to make it run with sqlite, so it's running a under a foo table with MySQL. (another hidden bug, I'm getting (IntegrityError) medias.id may not be NULL). your best bet with this mapping right now is: print Media .query .select_from (media_table .join (catalog_table ).join (catalog_channel_table )).filter(CatalogChannel.c.id_channel==playlist.id_channel).all() which is really how select_from() was intended to be used. the select_table option should be working here, which would make this super easy, but its not - will investigate more closely. --~--~-~--~~~---~--~~ 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: filter() on inherited class doesn't point to the correct table
Mike, My day is over, I'm leaving the office. I'll read your replies tomorrow. Thanks for taking some time one that. Regards, -- Alexandre CONRAD --~--~-~--~~~---~--~~ 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: filter() on inherited class doesn't point to the correct table
On Jan 14, 2008, at 12:44 PM, Alexandre Conrad wrote: Michael Bayer wrote: your best bet with this mapping right now is: print Media .query .select_from (media_table .join (catalog_table ).join (catalog_channel_table )).filter(CatalogChannel.c.id_channel==playlist.id_channel).all() which is really how select_from() was intended to be used. This works with SQLite, but not MySQL: (1054, Unknown column 'catalog_channels.id_channel' in 'on clause') that doesnt sound right. taking out select_table, and doing: print Media .query .select_from (media_table .join (catalog_table ).join (catalog_channel_table )).filter(CatalogChannel.c.id_channel==playlist.id_channel).all() leads to the SQL: SELECT medias.id AS medias_id, medias.name AS medias_name, medias.id_catalog AS medias_id_catalog FROM medias JOIN catalogs ON catalogs.id = medias.id_catalog JOIN catalog_channels ON catalogs.id = catalog_channels.id WHERE catalog_channels.id_channel = ? ORDER BY medias.oid which is entirely acceptable (and works in mysql). --~--~-~--~~~---~--~~ 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: filter() on inherited class doesn't point to the correct table
Michael Bayer wrote: I dont see where the type element youre mentioning is present in this query. if Media points to Catalog, thats the end of the story - what is telling it about a CatalogChannel ? Okay, okay Mike and Svilen. Of course ! I was thinking we could figure out that if a media being attached to a Catalog of type channel, thus a CatalogChannel polymorphically speaking, would figure out that Media.query.join([catalog, channel]) would automaticly join to the channel attribute of CatalogChannel. But of course, at this point we're still building up the query and are not on the ORM part. So we need to explicitly join tables between each other. Got it. Anyway, my inital issue according to the subject's title, is that: .filter(Catalog.c.id==CatalogChannel.c.id) generates the SQL query catalogs.id = catalogs.id rather than the expected catalogs.id = catalog_channels.id which .filter(Catalog.c.id==catalog_channel_table.c.id) generates correctly when explcitly pointing the wanted table. Is that a bug Mike ? Regards, -- Alexandre CONRAD --~--~-~--~~~---~--~~ 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: filter() on inherited class doesn't point to the correct table
On Jan 13, 2008, at 1:02 PM, Alexandre Conrad wrote: Okay, okay Mike and Svilen. Of course ! I was thinking we could figure out that if a media being attached to a Catalog of type channel, thus a CatalogChannel polymorphically speaking, would figure out that Media.query.join([catalog, channel]) would automaticly join to the channel attribute of CatalogChannel. But of course, at this point we're still building up the query and are not on the ORM part. So we need to explicitly join tables between each other. Got it. Anyway, my inital issue according to the subject's title, is that: .filter(Catalog.c.id==CatalogChannel.c.id) generates the SQL query catalogs.id = catalogs.id rather than the expected catalogs.id = catalog_channels.id which .filter(Catalog.c.id==catalog_channel_table.c.id) generates correctly when explcitly pointing the wanted table. Is that a bug Mike ? i dont really think it is. the c collection is deprecated anyway; the id attribute on Catalog and all of its subclasses refers to the base id column on the catalog table; the other foreign key versions of that primary key are factored out when the mapper compiles. that the id gets shoved into c is an implementation artifact, but is also why c is deprecated. since the join youre doing intends to break through the abstraction of the polymorphic mapping, using the literal tables is the appropriate thing to do here, barring the existence of a new feature that would make what you're trying to do easier (which we should probably add). --~--~-~--~~~---~--~~ 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: filter() on inherited class doesn't point to the correct table
Michael Bayer wrote: On Jan 13, 2008, at 1:02 PM, Alexandre Conrad wrote: Anyway, my inital issue according to the subject's title, is that: .filter(Catalog.c.id==CatalogChannel.c.id) generates the SQL query catalogs.id = catalogs.id rather than the expected catalogs.id = catalog_channels.id which .filter(Catalog.c.id==catalog_channel_table.c.id) generates correctly when explcitly pointing the wanted table. Is that a bug Mike ? i dont really think it is. the c collection is deprecated anyway; c is deprecated ? On the class or the table ? I haven't seen warning being printed in my console during execution. the id attribute on Catalog and all of its subclasses refers to the base id column on the catalog table; the other foreign key versions of that primary key are factored out when the mapper compiles. that the id gets shoved into c is an implementation artifact, but is also why c is deprecated. since the join youre doing intends to break through the abstraction of the polymorphic mapping, using the literal tables is the appropriate thing to do here, barring the existence of a new feature that would make what you're trying to do easier (which we should probably add). Ahh, good to hear. It would feel more natural to me. We'd expect that CatalogChannel's table is catalog_channel_table, specially when we earlier set mapper(CatalogChannel, catalog_channel_table). Plus, using tables makes me have extra imports in my code, which I didn't need until now because I'm only using mapped classes. Regards, -- Alexandre CONRAD --~--~-~--~~~---~--~~ 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: filter() on inherited class doesn't point to the correct table
On Jan 13, 2008, at 2:33 PM, Alexandre Conrad wrote: Ahh, good to hear. It would feel more natural to me. We'd expect that CatalogChannel's table is catalog_channel_table, specially when we earlier set mapper(CatalogChannel, catalog_channel_table). Plus, using tables makes me have extra imports in my code, which I didn't need until now because I'm only using mapped classes. I came up with a very easy way to implement this which I'd like you to try out in rev 4060. So far I've tested with a basic joined table inheritance setup. All you do is place class-mapped attributes directly in the join() arguments along with strings. and thats it ! so it looks like: session.query(Media).join(['catalog', CatalogChannel.channel]) The class-attributes can be freely intermixed with the existing string- based attributes. Technically you can place any class-attribute you want in there but it only makes sense if it can be joined against the immediately preceding attribute or parent mapper. I'm not sure if that restriction could change eventually. --~--~-~--~~~---~--~~ 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: filter() on inherited class doesn't point to the correct table
svilen wrote: On Friday 11 January 2008 16:12:08 Alexandre Conrad wrote: Channel - Playlist - Media Channel - CatalogChannel(Catalog) - Media (Media has a fk to Catalog, not CatalogChannel) The only element I have, is playlist (instance of Playlist). At this point, I need to find out the available Media of the Playlist's Channel's catalog so I can attach them to the Playlist. At first, I tryied out: Media.query.join([catalog, channel]).filter(Channel.c.id==playlist.id_channel).all() But then it complains that channel is not part of the Catalog mapper. Catalog ? I want it to be looking at CatalogChannel, this is the one having the channel relation, not Catalog. i see what u want, but formally (computer languages are formal, SA is a language) u are contradicting yourself. u said above that media points to catalog and not to catalogchannel. How u expect it to find a .channel there? I was expecting that SA would know that from the polymorphic type flag. I have a catalog relation on media. When I do media.catalog, it doesn't just return a Catalog object, but really a CatalogChannel object (which is the whole point of polymorphic inheritance). And I thought it could figure out channel from that. But Mike said no. :) That's why he talked about having some extra API query methods: Media.query.join_to_subclass(CatalogChannel).join(channel).filter(Channel.c.id==playlist.id_channel).all() We could even join classes only directly (isn't this ORM after all?): Media.query.join([CatalogChannel, Channel]) your query above is missing the isinstance-filter specifying that u need catalogchannels and not just any catalogs. i'm not sure how this would be expressed in SA but it has to be explicit - and probably somewhere on level of tables. have u tried Media.query.join( [catalog, id, channel])... ??? Nope, that doesn't work, it's like doing ([catalog, catalog, channel]) Regards, -- Alexandre CONRAD --~--~-~--~~~---~--~~ 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: filter() on inherited class doesn't point to the correct table
On Friday 11 January 2008 16:12:08 Alexandre Conrad wrote: svilen wrote: Here is the syntax followed by the generated query: query.filter(Catalog.c.id==CatalogChannel.c.id) WHERE catalogs.id = catalogs.id why u need such a query? that's exactly what (inheritance) join does, and automaticaly - just query( CatalogChannel).all() would give u the above query. I have hidden the full concept I'm working on and only focused my problem. Here's my full setup the query is involved with: Channel - Playlist - Media Channel - CatalogChannel(Catalog) - Media (Media has a fk to Catalog, not CatalogChannel) The only element I have, is playlist (instance of Playlist). At this point, I need to find out the available Media of the Playlist's Channel's catalog so I can attach them to the Playlist. At first, I tryied out: Media.query.join([catalog, channel]).filter(Channel.c.id==playlist.id_channel).all() But then it complains that channel is not part of the Catalog mapper. Catalog ? I want it to be looking at CatalogChannel, this is the one having the channel relation, not Catalog. i see what u want, but formally (computer languages are formal, SA is a language) u are contradicting yourself. u said above that media points to catalog and not to catalogchannel. How u expect it to find a .channel there? Forget DB; think plain objects. u point to a base class but expect it always to have an attribute that belongs to one of children classes... which is implicitly saying that while pointing to base-class AND u need only those pointing to the child-class AND the attribute is whatever. (i.e. isinstance(x,CatChanel) and x.channel==... your query above is missing the isinstance-filter specifying that u need catalogchannels and not just any catalogs. i'm not sure how this would be expressed in SA but it has to be explicit - and probably somewhere on level of tables. have u tried Media.query.join( [catalog, id, channel])... ??? --~--~-~--~~~---~--~~ 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: filter() on inherited class doesn't point to the correct table
On Friday 11 January 2008 13:58:34 Alexandre Conrad wrote: Hi, playing with inheritance, I figured out that an inherited mapped class passed to filter doesn't point to the correct table. I have 2 classes, Catalog and CatalogChannel(Catalog). Here is the syntax followed by the generated query: query.filter(Catalog.c.id==CatalogChannel.c.id) WHERE catalogs.id = catalogs.id why u need such a query? that's exactly what (inheritance) join does, and automaticaly - just query( CatalogChannel).all() would give u the above query. as of the relations, they are quite automatic BUT magic does not always work, so u have to explicitly specify some things manualy. Normaly, I would join([A, B]) the tables between each other. But if a channel relation only exists on the CatalogChannel class, join(channel) wouldn't work as SA looks at superclass Catalog. I thought it would naturally find the relationship by looking at the polymorphic type column from Catalog, but it doesn't. Mike suggested we would need to extend the API with a new method like join_to_subclass() or so... Even though, I still think SA should figure out which relation I'm looking at... i'm not sure i can follow u here... i do have tests about referencing to baseclas / subclasses, self or not, and they work ok. dbcook/tests/sa/ref_*.py for plain sa (160 combinations), and dbcook/tests/mapper/test_ABC_inh_ref_all.py (1 combinations) IMO u are missing some explicit argument --~--~-~--~~~---~--~~ 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: filter() on inherited class doesn't point to the correct table
On Friday 11 January 2008 17:03:06 Alexandre Conrad wrote: svilen wrote: On Friday 11 January 2008 16:12:08 Alexandre Conrad wrote: Channel - Playlist - Media Channel - CatalogChannel(Catalog) - Media (Media has a fk to Catalog, not CatalogChannel) The only element I have, is playlist (instance of Playlist). At this point, I need to find out the available Media of the Playlist's Channel's catalog so I can attach them to the Playlist. At first, I tryied out: Media.query.join([catalog, channel]).filter(Channel.c.id==playlist.id_channel).all() But then it complains that channel is not part of the Catalog mapper. Catalog ? I want it to be looking at CatalogChannel, this is the one having the channel relation, not Catalog. i see what u want, but formally (computer languages are formal, SA is a language) u are contradicting yourself. u said above that media points to catalog and not to catalogchannel. How u expect it to find a .channel there? I was expecting that SA would know that from the polymorphic type flag. I have a catalog relation on media. When I do media.catalog, it doesn't just return a Catalog object, but really a CatalogChannel object hey, polymorphic means ANY subtype, u could have 5 other CatalogRivers that have no .channel in them... how to guess which one? Find the one that has .channel? the root-most one or some of its children-klasses? (which is the whole point of polymorphic inheritance). And I thought it could figure out channel from that. But Mike said no. :) That's why he talked about having some extra API query methods: Media.query.join_to_subclass(CatalogChannel).join(channel).filter (Channel.c.id==playlist.id_channel).all() We could even join classes only directly (isn't this ORM after all?): Media.query.join([CatalogChannel, Channel]) this is completely different beast. it might be useful... although the whole idea of the join(list) is list of attribute-names and not klasses/tables - to have a.b.c.d.e.f, i.e. be specific and avoid thinking in diagram ways (klasA referencing klasB means nothing if it happens via 5 diff.attributes) when i needed similar thing, a) i moved the .channel into the root or b) changed media to reference CatChannel and not the base one. ciao svilen --~--~-~--~~~---~--~~ 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: filter() on inherited class doesn't point to the correct table
On Jan 11, 2008, at 10:03 AM, Alexandre Conrad wrote: svilen wrote: On Friday 11 January 2008 16:12:08 Alexandre Conrad wrote: Channel - Playlist - Media Channel - CatalogChannel(Catalog) - Media (Media has a fk to Catalog, not CatalogChannel) The only element I have, is playlist (instance of Playlist). At this point, I need to find out the available Media of the Playlist's Channel's catalog so I can attach them to the Playlist. At first, I tryied out: Media.query.join([catalog, channel]).filter(Channel.c.id==playlist.id_channel).all() But then it complains that channel is not part of the Catalog mapper. Catalog ? I want it to be looking at CatalogChannel, this is the one having the channel relation, not Catalog. i see what u want, but formally (computer languages are formal, SA is a language) u are contradicting yourself. u said above that media points to catalog and not to catalogchannel. How u expect it to find a .channel there? I was expecting that SA would know that from the polymorphic type flag. I have a catalog relation on media. When I do media.catalog, it doesn't just return a Catalog object, but really a CatalogChannel object (which is the whole point of polymorphic inheritance). And I thought it could figure out channel from that. But Mike said no. :) That's why he talked about having some extra API query methods: I dont see where the type element youre mentioning is present in this query. if Media points to Catalog, thats the end of the story - what is telling it about a CatalogChannel ? look at it this way. Suppose you have CatalogA(Catalog), CatalogB(Catalog), CatalogChannel(Catalog), CatalogQ(Catalog). all four of those classes have an attribute called channel. Catalog does not. Media references Catalog; therefore, the catalog attribute on Media can be any of: CatalogA, CatalogB, CatalogChannel, Catalog, or CatalogQ. Now I say: Media.query.join([catalog, channel]) Whats the join is has to produce ? what table does it join to ? catalog_channel, catalog_a, catalog_b, catalog_c, catalog_q ? or does it try to make a UNION out of joins for all of those (clearly we arent going to guess that deeply) ? --~--~-~--~~~---~--~~ 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: filter() on inherited class doesn't point to the correct table
svilen wrote: Here is the syntax followed by the generated query: query.filter(Catalog.c.id==CatalogChannel.c.id) WHERE catalogs.id = catalogs.id why u need such a query? that's exactly what (inheritance) join does, and automaticaly - just query( CatalogChannel).all() would give u the above query. I have hidden the full concept I'm working on and only focused my problem. Here's my full setup the query is involved with: Channel - Playlist - Media Channel - CatalogChannel(Catalog) - Media (Media has a fk to Catalog, not CatalogChannel) The only element I have, is playlist (instance of Playlist). At this point, I need to find out the available Media of the Playlist's Channel's catalog so I can attach them to the Playlist. At first, I tryied out: Media.query.join([catalog, channel]).filter(Channel.c.id==playlist.id_channel).all() But then it complains that channel is not part of the Catalog mapper. Catalog ? I want it to be looking at CatalogChannel, this is the one having the channel relation, not Catalog. as of the relations, they are quite automatic BUT magic does not always work, so u have to explicitly specify some things manualy. So right now, I'm building that awfully long query to explicitly tell it to look at CatalogChannel: Media.query.filter(Media.c.id_catalog==Catalog.c.id).filter(Catalog.c.id==CatalogChannel.c.id).filter(CatalogChannel.c.id_channel==c.playlist.id_channel).all() # Pheeww... But even this doesn't work well. The part where .filter(Catalog.c.id==CatalogChannel.c.id) wrongly generates: catalogs.id = catalogs.id So I need to use the table itself: .filter(Catalog.c.id==catalog_channel_table.c.id) correctly generates: catalogs.id = catalog_channels.id This works. Regards, -- Alexandre CONRAD - TLV FRANCE Research Development --~--~-~--~~~---~--~~ 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: filter() on inherited class doesn't point to the correct table
I think I understand what you trying to do
in fact polymorphic objects are load correctly in my test, I think it is
an approach to your case.
follow the code I used before to ask about polymorphic inheritance, note
to the Catalog class, this class have a resource list (catalog_id on
Resource), and I am inserting different types of resources, than when
select,
each resource is loaded correctly with their type
---code--8--code-
from sqlalchemy import create_engine, MetaData, Table, Column, types,
ForeignKey
from sqlalchemy.orm import mapper, relation, backref, create_session
from sqlalchemy.sql.expression import outerjoin, join
from sqlalchemy import String, Unicode, Integer, DateTime, Numeric,
Boolean, UnicodeText
db = create_engine('sqlite:///:memory:')
metadata = MetaData()
metadata = MetaData(db)
metadata.bind = db
session = create_session(bind=db)
resource_table = Table('resource', metadata,
Column('id',Integer, primary_key=True),
Column('name', String(30)),
Column('catalog_id', Integer, ForeignKey('catalog.id')),
Column('poly', String(31), nullable=True)
)
person_table = Table('person', metadata,
Column('id',Integer, ForeignKey('resource.id'), primary_key=True,),
)
material_table = Table('material', metadata,
Column('id',Integer, ForeignKey('resource.id'), primary_key=True,),
)
employee_table = Table('employee', metadata,
Column('id',Integer, ForeignKey('person.id'), primary_key=True),
)
technical_table = Table('technical', metadata,
Column('id',Integer, ForeignKey('person.id'), primary_key=True),
)
catalog_table = Table('catalog', metadata,
Column('id',Integer, primary_key=True),
)
catalog_resources = Table('catalog_resources', metadata,
Column('id', Integer, primary_key=True),
Column('resource_id',Integer, ForeignKey('resource.id')),
)
class Resource(object):
def __init__(self, name):
self.name = name
def __repr__(self):
return Resource id=%d ,name=%s % (self.id,self.name)
class Person(Resource):
def __repr__(self):
return Person id=%d ,name=%s % (self.id,self.name)
class Material(Resource):
def __repr__(self):
return Material id=%d ,name=%s % (self.id,self.name)
class Employee(Person):
def __repr__(self):
return Employee id=%d ,name=%s % (self.id,self.name)
class Technical(Person):
def __repr__(self):
return Technical id=%d ,name=%s % (self.id,self.name)
class Catalog(object):
def __repr__(self):
return catalog id=%d resources=%s %
(self.id,str([str(r)+',' for r in self.resources]))
mapper(Resource, resource_table,
polymorphic_on=resource_table.c.poly,
polymorphic_identity='resource',
)
mapper(Person, person_table,
inherits=Resource, polymorphic_identity='person',
polymorphic_on= resource_table.c.poly,
)
mapper(Material, material_table,
inherit_condition= material_table.c.id == resource_table.c.id,
inherits=Resource, polymorphic_identity='material'
)
mapper(Employee, employee_table,
inherit_condition= employee_table.c.id == person_table.c.id,
inherits=Person, polymorphic_identity='employee',
)
mapper(Technical, technical_table,
inherit_condition= technical_table.c.id == person_table.c.id,
inherits=Person, polymorphic_identity='technical',
)
mapper(Catalog, catalog_table,
properties={
'resources':relation(Resource)
})
metadata.create_all(bind=db)
r = Resource('resource name')
p = Person('person name')
m = Material('material name')
e = Employee('employee name')
t = Technical('technical name')
x = Catalog()
x.resources = [p,m,e,t]
session.save(r)
session.save(p)
session.save(m)
session.save(e)
session.save(t)
session.save(x)
session.flush()
session.clear()
print LIST FROM RESOURCES #
for o in session.query(Resource).all():
print o, o.poly
print LIST FROM PERSONS #
for o in session.query(Person).all():
print o, o.poly
print Catalog #
y= session.query(Catalog).one()
print y
---code--8--code-
I Hope it helps you
Att
--
Alexandre da Silva
Analista de Sistemas - Bacharel em Sistemas de Informação (2003-2007)
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
