From 6b3ecd14eae1a557cffd19da6c82d967586a6d74 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Fri, 31 Jan 2014 19:14:08 -0500 Subject: [PATCH] - Added a new parameter :paramref:`.Operators.op.is_comparison`. This flag allows a custom op from :meth:`.Operators.op` to be considered as a "comparison" operator, thus usable for custom :paramref:`.relationship.primaryjoin` conditions. --- doc/build/changelog/changelog_09.rst | 13 +++++++ doc/build/orm/relationships.rst | 54 ++++++++++++++++++++++++++++ lib/sqlalchemy/sql/operators.py | 21 ++++++++--- test/orm/test_relationships.py | 39 ++++++++++++++++++++ test/sql/test_operators.py | 10 ++++++ 5 files changed, 133 insertions(+), 4 deletions(-) diff --git a/doc/build/changelog/changelog_09.rst b/doc/build/changelog/changelog_09.rst index fbb6db3355..4a4ec10089 100644 --- a/doc/build/changelog/changelog_09.rst +++ b/doc/build/changelog/changelog_09.rst @@ -14,6 +14,19 @@ .. changelog:: :version: 0.9.2 + .. change:: + :tags: feature, orm + + Added a new parameter :paramref:`.Operators.op.is_comparison`. This + flag allows a custom op from :meth:`.Operators.op` to be considered + as a "comparison" operator, thus usable for custom + :paramref:`.relationship.primaryjoin` conditions. + + .. seealso:: + + :ref:`relationship_custom_operator` + + .. change:: :tags: bug, sqlite diff --git a/doc/build/orm/relationships.rst b/doc/build/orm/relationships.rst index 98a6e1becb..238493faa1 100644 --- a/doc/build/orm/relationships.rst +++ b/doc/build/orm/relationships.rst @@ -1077,6 +1077,60 @@ of these features on its own:: ) +.. _relationship_custom_operator: + +Using custom operators in join conditions +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + +Another use case for relationships is the use of custom operators, such +as Postgresql's "is contained within" ``<<`` operator when joining with +types such as :class:`.postgresql.INET` and :class:`.postgresql.CIDR`. +For custom operators we use the :meth:`.Operators.op` function:: + + inet_column.op("<<")(cidr_column) + +However, if we construct a :paramref:`.relationship.primaryjoin` using this +operator, :func:`.relationship` will still need more information. This is because +when it examines our primaryjoin condition, it specifically looks for operators +used for **comparisons**, and this is typically a fixed list containing known +comparison operators such as ``==``, ``<``, etc. So for our custom operator +to participate in this system, we need it to register as a comparison operator +using the :paramref:`.Operators.op.is_comparison` parameter:: + + inet_column.op("<<", is_comparison=True)(cidr_column) + +A complete example:: + + class IPA(Base): + __tablename__ = 'ip_address' + + id = Column(Integer, primary_key=True) + v4address = Column(INET) + + network = relationship("Network", + primaryjoin="IPA.v4address.op('<<', is_comparison=True)" + "(foreign(Network.v4representation))", + viewonly=True + ) + class Network(Base): + __tablename__ = 'network' + + id = Column(Integer, primary_key=True) + v4representation = Column(CIDR) + +Above, a query such as:: + + session.query(IPA).join(IPA.network) + +Will render as:: + + SELECT ip_address.id AS ip_address_id, ip_address.v4address AS ip_address_v4address + FROM ip_address JOIN network ON ip_address.v4address << network.v4representation + +.. versionadded:: 0.9.2 - Added the :paramref:`.Operators.op.is_comparison` + flag to assist in the creation of :func:`.relationship` constructs using + custom operators. + .. _self_referential_many_to_many: Self-Referential Many-to-Many Relationship diff --git a/lib/sqlalchemy/sql/operators.py b/lib/sqlalchemy/sql/operators.py index d7ec977aa3..91301c78cf 100644 --- a/lib/sqlalchemy/sql/operators.py +++ b/lib/sqlalchemy/sql/operators.py @@ -102,7 +102,7 @@ class Operators(object): """ return self.operate(inv) - def op(self, opstring, precedence=0): + def op(self, opstring, precedence=0, is_comparison=False): """produce a generic operator function. e.g.:: @@ -134,12 +134,23 @@ class Operators(object): .. versionadded:: 0.8 - added the 'precedence' argument. + :param is_comparison: if True, the operator will be considered as a + "comparison" operator, that is which evaulates to a boolean true/false + value, like ``==``, ``>``, etc. This flag should be set so that + ORM relationships can establish that the operator is a comparison + operator when used in a custom join condition. + + .. versionadded:: 0.9.2 - added the :paramref:`.Operators.op.is_comparison` + flag. + .. seealso:: :ref:`types_operators` + :ref:`relationship_custom_operator` + """ - operator = custom_op(opstring, precedence) + operator = custom_op(opstring, precedence, is_comparison) def against(other): return operator(self, other) @@ -200,9 +211,10 @@ class custom_op(object): """ __name__ = 'custom_op' - def __init__(self, opstring, precedence=0): + def __init__(self, opstring, precedence=0, is_comparison=False): self.opstring = opstring self.precedence = precedence + self.is_comparison = is_comparison def __eq__(self, other): return isinstance(other, custom_op) and \ @@ -769,7 +781,8 @@ _comparison = set([eq, ne, lt, gt, ge, le, between_op]) def is_comparison(op): - return op in _comparison + return op in _comparison or \ + isinstance(op, custom_op) and op.is_comparison def is_commutative(op): diff --git a/test/orm/test_relationships.py b/test/orm/test_relationships.py index 8f7e2bd557..ccd54284ac 100644 --- a/test/orm/test_relationships.py +++ b/test/orm/test_relationships.py @@ -1517,6 +1517,45 @@ class TypedAssociationTable(fixtures.MappedTest): assert t3.count().scalar() == 1 +class CustomOperatorTest(fixtures.MappedTest, AssertsCompiledSQL): + """test op() in conjunction with join conditions""" + + run_create_tables = run_deletes = None + + __dialect__ = 'default' + + @classmethod + def define_tables(cls, metadata): + Table('a', metadata, + Column('id', Integer, primary_key=True), + Column('foo', String(50)) + ) + Table('b', metadata, + Column('id', Integer, primary_key=True), + Column('foo', String(50)) + ) + + def test_join_on_custom_op(self): + class A(fixtures.BasicEntity): + pass + class B(fixtures.BasicEntity): + pass + + mapper(A, self.tables.a, properties={ + 'bs': relationship(B, + primaryjoin=self.tables.a.c.foo.op( + '&*', is_comparison=True + )(foreign(self.tables.b.c.foo)), + viewonly=True + ) + }) + mapper(B, self.tables.b) + self.assert_compile( + Session().query(A).join(A.bs), + "SELECT a.id AS a_id, a.foo AS a_foo FROM a JOIN b ON a.foo &* b.foo" + ) + + class ViewOnlyHistoryTest(fixtures.MappedTest): @classmethod def define_tables(cls, metadata): diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py index 670d088d23..79b0a717b4 100644 --- a/test/sql/test_operators.py +++ b/test/sql/test_operators.py @@ -1585,3 +1585,13 @@ class ComposedLikeOperatorsTest(fixtures.TestBase, testing.AssertsCompiledSQL): dialect=mysql.dialect() ) +class CustomOpTest(fixtures.TestBase): + def test_is_comparison(self): + c = column('x') + c2 = column('y') + op1 = c.op('$', is_comparison=True)(c2).operator + op2 = c.op('$', is_comparison=False)(c2).operator + + assert operators.is_comparison(op1) + assert not operators.is_comparison(op2) + -- 2.47.3