ok, I think I found the solution. Thought I'd post what I did and what I tried; partly because it might help someone else and partly because someone may have suggestions for a better way.
This is what worked: my_function = func.my_function(...args...) func_subquery = select(['id'], from_obj=[my_function]) results = session.query(Table1).join(Table2).filter(Table1.id.in_(func_subquery)) and results in a query that looks like this: SELECT table1.id, table1.col1, table1.col2... FROM table1 JOIN table2 ON table1.t2_id = table2.id WHERE table1.id IN( SELECT id FROM my_function(...args...) ) This differs somewhat from what I was initially thinking: SELECT table1.id, table1.col1, table1.col2... FROM (SELECT * FROM my_function(...args...)) as table1 JOIN table2 ON table1.t2_id = table2.id When I run EXPLAIN ANALYZE in PostgreSQL the IN() version seems to be slightly more efficient according to the planner (and real run times are more or less the same) IN: "Nested Loop (cost=12.75..889.97 rows=35432 width=222) (actual time=42.200..42.209 rows=2 loops=1)" JOIN: "Nested Loop (cost=0.25..4386.37 rows=1000 width=226) (actual time=41.052..41.061 rows=2 loops=1)" ----------------------------------------------------------------------------- Things I tried on the path to getting my answer: ----------------------------------------------------------------------------- I attempted to use a combination of a label and an alias with my function (modelled after this: http://docs.sqlalchemy.org/en/rel_0_7/orm/query.html?highlight=aliased#sqlalchemy.orm.aliased): my_function = func.my_function(...args...).label(Table1.__tablename__) my_function_alias = aliased(Table1, alias=my_function, adapt_on_names=True) but that failed miserably. Using the labeled function in a regular core select resulted in an AttributeError exception, so I think that might have been part of the problem: >>> print select('*', from_obj=[my_function]) Traceback (most recent call last): File "<stdin>", line 1, in <module> File "/Library/Python/2.7/site-packages/sqlalchemy/sql/expression.py", line 1790, in __str__ return unicode(self.compile()).encode('ascii', 'backslashreplace') File "/Library/Python/2.7/site-packages/sqlalchemy/sql/expression.py", line 1778, in compile return self._compiler(dialect, bind=bind, **kw) File "/Library/Python/2.7/site-packages/sqlalchemy/sql/expression.py", line 1784, in _compiler return dialect.statement_compiler(dialect, self, **kw) File "/Library/Python/2.7/site-packages/sqlalchemy/sql/compiler.py", line 277, in __init__ engine.Compiled.__init__(self, dialect, statement, **kwargs) File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 705, in __init__ self.string = self.process(self.statement) File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 724, in process return obj._compiler_dispatch(self, **kwargs) File "/Library/Python/2.7/site-packages/sqlalchemy/sql/visitors.py", line 72, in _compiler_dispatch return getter(visitor)(self, **kw) File "/Library/Python/2.7/site-packages/sqlalchemy/sql/compiler.py", line 873, in visit_select froms = select._get_display_froms(existingfroms) File "/Library/Python/2.7/site-packages/sqlalchemy/sql/expression.py", line 4807, in _get_display_froms toremove = set(itertools.chain(*[f._hide_froms for f in froms])) AttributeError: '_Label' object has no attribute '_hide_froms' Using the aliased function would include the original table as well as the aliased functioned, and without a join condition it would just do a cartesian product: >>> results = session.query(my_function_alias).first() SELECT table1.id, table1.col1, table1.col2... FROM table1, (SELECT * FROM my_function(...args...)) So that didn't work either. After doing this I realized that if I have to include the table and the function sub-select I might as well attempt it as an IN(), and that is what brought me to my final answer. Thanks again for your suggestion Mike, it definitely put me on the right path to the solution. On Thursday, September 20, 2012 7:10:52 PM UTC+2, Michael Bayer wrote: > > you can make a function act like a table by creating a select() out of it, > check out the fifth example at > http://docs.sqlalchemy.org/en/rel_0_7/core/tutorial.html#functions > > On Sep 20, 2012, at 1:09 PM, David McKeone wrote: > > Hi M, > > Is creating something in the database the only way to do it? How would I > coerce the view's return type into my object? How do I substitute the view > in the FROM part of my clause instead? > > > On Thursday, September 20, 2012 5:52:28 PM UTC+2, A.M. wrote: >> >> >> On Sep 20, 2012, at 11:49 AM, David McKeone wrote: >> >> > I've googled around can't seem to find an answer to this, so hopefully >> someone knows how to do it here. >> > >> > I'm using PostgreSQL and I have a PL/PGSQL function that filters and >> modifies a particular table based on a number of conditions and then >> returns a set of rows as the result. This pattern has allowed the system >> to use the functions as if they were tables so that joins can still be done >> on the resulting values. >> > >> > So instead of: >> > >> > SELECT * FROM table1 INNER JOIN table2 ON table1.t2_id = table2.id >> > >> > I do: >> > >> > SELECT * FROM my_function( ...args... ) as table1 INNER JOIN table2 ON >> table1.t2_id = table2.id >> > >> > That part works ok in plain SQL (and as well in the system I'm >> converting from) >> > >> > So now with SQLAlchemy I have my declarative definitions for those >> tables: >> > >> > class Table1(Base): >> > __tablename__ = 'table1' >> > >> > id = Column() >> > t2_id = Column(ForeignKey()) >> > >> > table2 = Relationship( ... ) # Join condition is specified >> explicitly >> > >> > class Table2(Base); >> > __tablename__ = 'table2' >> > >> > id = Column() >> > >> > and I'm trying to figure out how I would execute a query that looks >> like this: >> > >> > result = >> session.query(Table1).join(Table1.table2).options(contains_eager(Table1.table2)) >> >> >> > >> > but using a function to 'fake' Table1 instead. So basically I'm >> attempting to get SQLAlchemy to treat the result of my function as if it >> was the normal Table1 object. I've tried using select_from() to inject my >> call to func.my_function() but that doesn't seem to work and since what I'm >> doing seems like it might be tricky (or not portable across SQL) I thought >> I'd ask if it's even possible. >> > >> > Thanks for any help! >> > >> >> Perhaps the easiest way is to create a view: >> >> CREATE VIEW table1 AS SELECT * FROM my_function(...); >> >> Cheers, >> M >> >> >> >> > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To view this discussion on the web visit > https://groups.google.com/d/msg/sqlalchemy/-/4EQ3O5IWOX0J. > To post to this group, send email to sqlal...@googlegroups.com<javascript:> > . > To unsubscribe from this group, send email to > sqlalchemy+...@googlegroups.com <javascript:>. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/L5URn6ryHWUJ. 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.
