To optimize a query I'm attempting to defer columns from joined tables I know I'm not going to need. The columns from joined tables to defer varies from query to query so I can't specify this when setting up my object/table mappings.
I'm able to do this just fine for columns of the primary table like this: query = query.option(defer("x")). It appears there are two ways to do the same for joined columns: 1] When specifying entities query = a_session.query(P, S1.x, S2.y, S1Alias.x)... 2] Using path to the joined column: query = query.option(defer("a.s1_relation.col1"), defer ("a.s1_alias_relation.col2"),...) 1 Seemed easy enough but i ran into problems when using this in conjunction with query.one(): it would raise exceptions that the result set had more than one row. I really just want to pull the entity object (preloaded with relevant column values, some from related tables) and then extract that to a dictionary. So pulling S1.x, S2.y, S1Alias.x into the result, making it a tuple, seems a little strange. 2 Simply doesn't work for me (i.e. the columns i'm deferring are clearly in the echoed sql). I also tried passing instrumented object attributes to defers like this: query = query.option(defer(S1.y), defer(S1Alias.x)) but that failed with an exception about e.g. S1 not being found in the mapper. I tried adding them to the query like this: query.add_entity(S1) but that didn't help. Apart from the unwanted columns the emitted SQL is what i expect and looks like: SELECT <bunch of columns> FROM parent LEFT OUTER JOIN parent_child AS series_child_1 ON series.id = series_child_1.series_id LEFT OUTER JOIN child ON series_child_1.child_id = child.id <-- primary child INNER JOIN child AS child_1 ON child_1.id = series.parent_child_id WHERE series.id = 1000 AND child.name LIKE 'prefix%' Am i missing something? Is there a way to specify joined column defers during query construction? Do i need to write a custom MapperProperty to do this? Thanks, M --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---