Hi, Is it possible to repeatedly add column expressions to a select object, where the column expression involves outer joins? Ideally I'd like there to be a several transformation functions that operate on the same select object in a chain.
So far, the closest I've come is for the transformation functions to accept and return both a select object (to which columns are appended) and a join object (to which joins are appended), such that the joins are added to the select at the end. It would be nice to be able to operate on just the select object. I'd like to mention another attempt at the original goal, which did not raise an exception but generated invalid SQL (using SQLAlchemy 1.0.13). The relevant code snippet from the runnable example below is this: select_obj = select_obj.column(new_col).select_from(Join( table_obj, concept_alias, col == concept_alias.c.concept_id, isouter=True)) The resulting SQL contains a spurious `, main_table` fragment: SELECT main_table.foo_concept_id, main_table.bar_concept_id, concept_1.concept_name AS foo_concept_name, concept_2.concept_name AS bar_concept_name FROM main_table LEFT OUTER JOIN concept AS concept_1 ON foo_concept_id = concept_1.concept_id*, main_table* LEFT OUTER JOIN concept AS concept_2 ON bar_concept_id = concept_2.concept_id The full code follows. Note that this is a prototype of just one of a series of transformation functions that would operate on the same select object. from sqlalchemy import MetaData, Table, Column, Integer, String, select, ForeignKey from sqlalchemy.dialects import postgresql from sqlalchemy.sql.expression import Join def setup(): """ Create a `main_table` table, a query on it, and a related table that will later be joined to. """ metadata = MetaData() concept = Table('concept', metadata, Column('concept_id', Integer), Column('concept_name', String(512))) main_table = Table('main_table', metadata, Column('foo_concept_id', Integer, ForeignKey('concept.concept_id')), Column('bar_concept_id', Integer, ForeignKey('concept.concept_id'))) select_obj = select().column(main_table.c.foo_concept_id).column(main_table.c.bar_concept_id).select_from(main_table) return metadata, select_obj def transform(metadata, table_name, select_obj): """Transform and return a Select object query by adding column expressions populated using outer joins. This Select object may be later passed to different varieties of transformation function. """ table_obj = metadata.tables[table_name] concept_table_obj = metadata.tables['concept'] for col in select_obj.c: if col.name.endswith('_concept_id'): new_name = col.name.replace('_concept_id', '_concept_name') concept_alias = concept_table_obj.alias() new_col = concept_alias.c.concept_name.label(new_name) select_obj = select_obj.column(new_col).select_from(Join( table_obj, concept_alias, col == concept_alias.c.concept_id, isouter=True)) return select_obj metadata, select_obj = setup() select_obj = transform(metadata, 'main_table', select_obj) print("select_obj.compile(dialect=postgresql.dialect())) Thanks, Kevin -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.