On 04/20/2012 21:38, Michael Bayer wrote:
On Apr 20, 2012, at 9:32 AM, Julien Cigar wrote:Hello, I'm using joined load inheritance in one of my project, and I have the following query: q = Content.query.outerjoin((File, File.content_id == Content.id), File.mime, Mime.major) SQLAlchemy generates a subquery for the outer join. Any idea how to ask SQLAlchemy to generate a shorter FROM clause (no subquery) ? This is what I get: FROM content LEFT OUTER JOIN (SELECT content.id AS content_id, content.added AS content_added, content.updated AS content_updated, content.title AS content_title, content.description AS content_description, content.effective AS content_effective, content.expiration AS content_expiration, content.exclude_nav AS content_exclude_nav, content.weight AS content_weight, content.customized AS content_customized, content.content_type_id AS content_content_type_id, content.icon_content_id AS content_icon_content_id, content.container_id AS content_container_id, content.owner_id AS content_owner_id, content.polymorphic_loading AS content_polymorphic_loading, content.state_id AS content_state_id, data.content_id AS data_content_id, data.mime_id AS data_mime_id, data.original_name AS data_original_name, data.file_size AS data_file_size FROM content JOIN data ON content.id = data.content_id) AS anon_1 ON anon_1.data_content_id = content.id LEFT OUTER JOIN mime ON mime.id = anon_1.data_mime_id LEFT OUTER JOIN mime_major ON mime_major.id = mime.major_id This is what I want: FROM content LEFT OUTER JOIN data ON content.id = data.content_id LEFT OUTER JOIN mime ON mime.id = anon_1.data_mime_id LEFT OUTER JOIN mime_major ON mime_major.id = mime.major_id My mappers looks like: orm.mapper(Content, table['content'], polymorphic_on = table['content'].c.content_type_id, ...) orm.mapper(File, table['data'], inherits = Content, polymorphic_identity = _get_type_id('file'), ...)right this is the behavior of when you join to a joined inh structure. It represents essentially "A JOIN B", and not every database backend can seamlessly handle the idea of "C JOIN (A JOIN B)", though most can these days except for SQLite. There's some other nasty scoping problems on the SQLA expression side which come into play, however, and changing the mechanics of query.join()/outerjoin() to try to "optimize" this is not something likely to ever happen, as far as I can see (I've looked into seeing how it could be done). In particular here, File is already a composite of Content, so joining from Content-> File is pretty awkward. in this particular case, you can probably get what you want just by loading Content with the "with_polymorphic" option that will outer join to the related "file" table: query(Content).with_polymorphic([File]).outerjoin(File.mime, Mime.major)
Thanks, it worked with the with_polymorphic() way
Otherwise, if you want to create a join among the components of a joined inheritance structure while maintaining explicit control over those components, you use the Table objects directly, that is File.__table__, and such. There's a detailed description of this at http://docs.sqlalchemy.org/en/latest/orm/inheritance.html#basic-control-of-which-tables-are-queried and http://docs.sqlalchemy.org/en/latest/orm/inheritance.html#advanced-control-of-which-tables-are-queried .
-- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
<<attachment: jcigar.vcf>>
