Hi group, my question is about Query.select_entity_from().
I have a table that contains a small subset of rows copied from a pretty large table. The two tables have the same definition. The large table is mapped on a Python class, say it Item(). I would like to query the small table the same way I can query the large one, receving Item() objects as result. Is this possible or is my expectation wrong? I had success with where clauses, but not with joins on relations defined on the large table (say it, the "original" one). An example of what I did follows. I work with PostgreSQL 9.6 and SqlAlchemy 1.2.10. Thank you very much for any clarification, Sara # Given: engine = sqlalchemy.create_engine('postgresql+psycopg2://.....') session = .... # Suppose that the following are the production tables: # t_items is the pretty large table # t_items has a relation to t_groups metadata = sqlalchemy.MetaData() t_groups = sqlalchemy.Table('*t_groups*', metadata, sqlalchemy.Column('id', sqlalchemy.Integer, primary_key = True), sqlalchemy.Column('code', sqlalchemy.String(50), nullable = False), ) t_items = sqlalchemy.Table('*t_items*', metadata, sqlalchemy.Column('id', sqlalchemy.Integer, primary_key = True), sqlalchemy.Column('group_id', sqlalchemy.Integer, sqlalchemy. *ForeignKey*('t_groups.id'), nullable = False), ) metadata.drop_all(bind=engine) metadata.create_all(bind=engine) class Group(object): pass class Item(object): pass sqlalchemy.orm.mapper(Group, t_groups) sqlalchemy.orm.mapper(Item, t_items, properties = { 'group' : sqlalchemy.orm.relationship(Group), }) # Some data into the production tables: group = Group() group.id = 1 group.code = u'group code' session.add(group) session.flush() item1 = Item() item1.id = 100 item1.group = group item2 = Item() item2.id = 101 item2.group = group session.add_all([item1, item2]) session.flush() # Now create the subset table (used only to speed up some computations); same definition of t_items stmtDrop = u"DROP TABLE IF EXISTS t_items_subset" session.execute(stmtDrop) session.commit() stmtCreate = u""" CREATE TABLE t_items_subset WITH OIDS AS SELECT * FROM t_items WITH NO DATA """ session.execute(stmtCreate) session.commit() t_items_subset = sqlalchemy.Table('t_items_subset', metadata, *autoload_with*=engine) # Now try to query the t_items_subset table receiving Item() objects as result itemAlias = sqlalchemy.orm.aliased(Item, t_items_subset, adapt_on_names=True) query = session.query(Item).\ *select_entity_from*(itemAlias) print query #SELECT t_items_subset.id AS t_items_subset_id, t_items_subset.group_id AS t_items_subset_group_id #FROM t_items_subset query = query.filter(Item.id==100) print query.statement #SELECT t_items_subset.id, t_items_subset.group_id #FROM t_items_subset #WHERE t_items_subset.id = :id_1 query = query.join('group').\ filter(Group.code=='group code') print query.statement # ################################## # *Here SQL does NOT appears as (I) expected*: the ON clause of JOIN refers t_items instead of t_items_subset # ################################## #SELECT t_items_subset.id, t_items_subset.group_id #FROM t_items_subset JOIN t_groups ON t_groups.id = t_items.group_id #WHERE t_items_subset.id = :id_1 AND t_groups.code = :code_1 -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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.