text as from

simple patch, might want to clarify SelectBase in general, several methods don't apply to CompoundSelect either

diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 3ba3957..b088916 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -726,6 +726,9 @@ class SQLCompiler(Compiled):
     def function_argspec(self, func, **kwargs):
         return func.clause_expr._compiler_dispatch(self, **kwargs)

+    def visit_text_as_from(self, taf, asfrom=False, parens=True, **kw):
+        return self.process(taf.element, **kw)
+
     def visit_compound_select(self, cs, asfrom=False,
                             parens=True, compound_index=0, **kwargs):
         toplevel = not self.stack
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index f349923..80ff064 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -1023,6 +1023,31 @@ class TextClause(Executable, ClauseElement):
             for b in bindparams:
                 self.bindparams[b.key](b.key) = b

+    @util.dependencies('sqlalchemy.sql.selectable')
+    def as_fromclause(self, selectable, *cols):
+        """Turn this :class:`.Text` object into a :class:`.FromClause`
+        object that can be embedded into another statement.
+
+        This function essentially bridges the gap between an entirely
+        textual SELECT statement and the SQL expression language concept
+        of a "selectable"::
+
+            from sqlalchemy.sql import column, text
+
+            stmt = text("SELECT * FROM some_table")
+            stmt = stmt.as_fromclause(column('id'), column('name')).alias('st')
+
+            stmt = select([mytable](mytable)).\\
+                    select_from(
+                        mytable.join(stmt, mytable.c.name == stmt.c.name)
+                    ).where(stmt.c.id > 5)
+
+        .. versionadded:: 0.9.0
+
+        """
+
+        return selectable.TextAsFrom(self, cols)
+
     @property
     def type(self):
         if self.typemap is not None and len(self.typemap) == 1:
diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py
index 28c757a..7a4a0b7 100644
--- a/lib/sqlalchemy/sql/selectable.py
+++ b/lib/sqlalchemy/sql/selectable.py
@@ -2912,6 +2912,25 @@ class Exists(UnaryExpression):
         return e


+class TextAsFrom(SelectBase):
+    """Wrap a :class:`.Text` construct within a :class:`.FromClause`
+    interface.
+
+    This allows the :class:`.Text` object to gain a ``.c`` collection and
+    other FROM-like capabilities such as :meth:`.FromClause.alias`,
+    :meth:`.FromClause.cte`, etc.
+
+    """
+    __visit_name__ = "text_as_from"
+
+    def __init__(self, text, columns):
+        self.element = text
+        self.column_args = columns
+
+    def _populate_column_collection(self):
+        for c in self.column_args:
+            c._make_proxy(self)
+
 class AnnotatedFromClause(Annotated):
     def __init__(self, element, values):
         # force FromClause to generate their internal

demo:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import column

positions = text("""
select instrument_id, sum(quantity) as quantity
       from transaction where
           account_id = :account_id and
           timestamp < :dt and
       group by instrument_id
       having sum(quantity) != 0
""").as_fromclause(column("instrument_id"), column("quantity")).cte('positions')


Base = declarative_base()

class A(Base):
    __tablename__ = 'a'

    id = Column(Integer, primary_key=True)
    data = Column(String)

s = Session()

print s.query(A).join(
            positions, A.id == positions.c.instrument_id).\
            filter(positions.c.quantity > 5)

Comments (3)