support functions "as binary comparison"

Added new feature :meth:`.FunctionElement.as_comparison` which allows a SQL
function to act as a binary comparison operation that can work within the
ORM.

Change-Id: I07018e2065d09775c0406cabdd35fc38cc0da699
Fixes: #3831

1 files changed, 104 insertions, 1 deletions

diff --git a/lib/sqlalchemy/sql/functions.py b/lib/sqlalchemy/sql/functions.py
index 78aeb3a01..27d030d4f 100644
--- a/lib/sqlalchemy/sql/functions.py
+++ b/lib/sqlalchemy/sql/functions.py
@@ -12,7 +12,8 @@ from . import sqltypes, schema
 from .base import Executable, ColumnCollection
 from .elements import ClauseList, Cast, Extract, _literal_as_binds, \
     literal_column, _type_from_args, ColumnElement, _clone,\
-    Over, BindParameter, FunctionFilter, Grouping, WithinGroup
+    Over, BindParameter, FunctionFilter, Grouping, WithinGroup, \
+    BinaryExpression
 from .selectable import FromClause, Select, Alias
 from . import util as sqlutil
 from . import operators
@@ -166,6 +167,73 @@ class FunctionElement(Executable, ColumnElement, FromClause):
             return self
         return FunctionFilter(self, *criterion)
 
+    def as_comparison(self, left_index, right_index):
+        """Interpret this expression as a boolean comparison between two values.
+
+        A hypothetical SQL function "is_equal()" which compares to values
+        for equality would be written in the Core expression language as::
+
+            expr = func.is_equal("a", "b")
+
+        If "is_equal()" above is comparing "a" and "b" for equality, the
+        :meth:`.FunctionElement.as_comparison` method would be invoked as::
+
+            expr = func.is_equal("a", "b").as_comparison(1, 2)
+
+        Where above, the integer value "1" refers to the first argument of the
+        "is_equal()" function and the integer value "2" refers to the second.
+
+        This would create a :class:`.BinaryExpression` that is equivalent to::
+
+            BinaryExpression("a", "b", operator=op.eq)
+
+        However, at the SQL level it would still render as
+        "is_equal('a', 'b')".
+
+        The ORM, when it loads a related object or collection, needs to be able
+        to manipulate the "left" and "right" sides of the ON clause of a JOIN
+        expression. The purpose of this method is to provide a SQL function
+        construct that can also supply this information to the ORM, when used
+        with the :paramref:`.relationship.primaryjoin` parameter.  The return
+        value is a containment object called :class:`.FunctionAsBinary`.
+
+        An ORM example is as follows::
+
+            class Venue(Base):
+                __tablename__ = 'venue'
+                id = Column(Integer, primary_key=True)
+                name = Column(String)
+
+                descendants = relationship(
+                    "Venue",
+                    primaryjoin=func.instr(
+                        remote(foreign(name)), name + "/"
+                    ).as_comparison(1, 2) == 1,
+                    viewonly=True,
+                    order_by=name
+                )
+
+        Above, the "Venue" class can load descendant "Venue" objects by
+        determining if the name of the parent Venue is contained within the
+        start of the hypothetical descendant value's name, e.g. "parent1" would
+        match up to "parent1/child1", but not to "parent2/child1".
+
+        Possible use cases include the "materialized path" example given above,
+        as well as making use of special SQL functions such as geometric
+        functions to create join conditions.
+
+        :param left_index: the integer 1-based index of the function argument
+         that serves as the "left" side of the expression.
+        :param right_index: the integer 1-based index of the function argument
+         that serves as the "right" side of the expression.
+
+        .. versionadded:: 1.3
+
+        """
+        return FunctionAsBinary(
+            self, left_index, right_index
+        )
+
     @property
     def _from_objects(self):
         return self.clauses._from_objects
@@ -281,6 +349,41 @@ class FunctionElement(Executable, ColumnElement, FromClause):
             return super(FunctionElement, self).self_group(against=against)
 
 
+class FunctionAsBinary(BinaryExpression):
+
+    def __init__(self, fn, left_index, right_index):
+        left = fn.clauses.clauses[left_index - 1]
+        right = fn.clauses.clauses[right_index - 1]
+        self.sql_function = fn
+        self.left_index = left_index
+        self.right_index = right_index
+
+        super(FunctionAsBinary, self).__init__(
+            left, right, operators.function_as_comparison_op,
+            type_=sqltypes.BOOLEANTYPE)
+
+    @property
+    def left(self):
+        return self.sql_function.clauses.clauses[self.left_index - 1]
+
+    @left.setter
+    def left(self, value):
+        self.sql_function.clauses.clauses[self.left_index - 1] = value
+
+    @property
+    def right(self):
+        return self.sql_function.clauses.clauses[self.right_index - 1]
+
+    @right.setter
+    def right(self, value):
+        self.sql_function.clauses.clauses[self.right_index - 1] = value
+
+    def _copy_internals(self, **kw):
+        clone = kw.pop('clone')
+        self.sql_function = clone(self.sql_function, **kw)
+        super(FunctionAsBinary, self)._copy_internals(**kw)
+
+
 class _FunctionGenerator(object):
     """Generate :class:`.Function` objects based on getattr calls."""