Re: [Sqlalchemy-tickets] [sqlalchemy] #1140: implement in_() for relations

[sqlalchemy <mi...@zz...>]

#1140: implement in_() for relations
----------------------------+-----------------------------------------------
    Reporter:  guest        |       Owner:  zzzeek           
        Type:  enhancement  |      Status:  new              
    Priority:  medium       |   Milestone:  0.5.xx           
   Component:  orm          |    Severity:  major - 1-3 hours
  Resolution:               |    Keywords:                   
Status_field:  in queue     |  
----------------------------+-----------------------------------------------
Changes (by zzzeek):

  * severity:  no triage selected yet => major - 1-3 hours
  * summary:  Maximum recursion depth exceeded using in_ operator =>
              implement in_() for relations
  * milestone:  0.5.0 => 0.5.xx
  * type:  defect => enhancement
  * status_field:  awaiting triage => in queue

Old description:

> The following code produces a !RuntimeError. Although `Child.parent ==
> p1` works fine `Child.parent.in_([p1, p2])`causes a traceback.
>
> {{{
> #!python
> from sqlalchemy import create_engine
> from sqlalchemy import Table, Column, Integer, String, MetaData,
> ForeignKey
> from sqlalchemy.orm import relation, mapper, sessionmaker, scoped_session
> from sqlalchemy.ext.declarative import declarative_base
>
> engine = create_engine('sqlite:///:memory:')
>
> sm = sessionmaker(autoflush=True, autocommit=True, bind=engine)
> Session = scoped_session(sm)
>
> Base = declarative_base(bind=engine)
>
> class Parent(Base):
>         __tablename__ = 'parent'
>         id = Column('id', Integer, primary_key=True)
>
>         def __init__(self, id):
>                 self.id = id
>
> class Child(Base):
>         __tablename__ = 'child'
>         id = Column('id', Integer, primary_key=True)
>         parent_id = Column('parent_id', Integer, ForeignKey('parent.id'))
>
>         def __init__(self, id, parent):
>                 self.id = id
>                 self.parent = parent
>
> Child.parent = relation(Parent, backref='children',
> primaryjoin=Child.parent_id==Parent.id)
>
> Base.metadata.create_all(engine)
>
> p1 = Parent(1)
> p2 = Parent(2)
> p3 = Parent(3)
> Session.add_all([p1, p2, p3])
>
> c1 = Child(1, p1)
> c2 = Child(2, p1)
> c3 = Child(3, p2)
> c4 = Child(4, p3)
> Session.add_all([c1, c2, c3, c4])
>
> q1 = Session.query(Child)
>
> # PASSED: result1 is a list of 2 children
> result1 = q1.filter(Child.parent == p1).all()
>
> # FAILED: result2 should be a list of 3 children
> result2 = q1.filter(Child.parent.in_([p1, p2])).all()
> }}}
>
> And the traceback:
>
> {{{
> #!python
>   File "/Library/Frameworks/Python.framework/Versions/2.5/lib/python2.5
> /site-
> packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/sql/expression.py",
> line 1244, in in_
>     return self.operate(operators.in_op, other)
>   File "/Library/Frameworks/Python.framework/Versions/2.5/lib/python2.5
> /site-
> packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/properties.py",
> line 319, in operate
>     return op(self, *other, **kwargs)
>   File "/Library/Frameworks/Python.framework/Versions/2.5/lib/python2.5
> /site-
> packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/sql/operators.py",
> line 49, in in_op
>     return a.in_(b)
>   File "/Library/Frameworks/Python.framework/Versions/2.5/lib/python2.5
> /site-
> packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/sql/expression.py",
> line 1244, in in_
>     return self.operate(operators.in_op, other)
>   File "/Library/Frameworks/Python.framework/Versions/2.5/lib/python2.5
> /site-
> packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/properties.py",
> line 319, in operate
>     return op(self, *other, **kwargs)
>   File "/Library/Frameworks/Python.framework/Versions/2.5/lib/python2.5
> /site-
> packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/sql/operators.py",
> line 49, in in_op
>     return a.in_(b)
> RuntimeError: maximum recursion depth exceeded
> }}}

New description:

 This would have to build upon the "optimized_compare" logic so that a JOIN
 would not be needed for the primary use case of a many-to-one check for a
 set of foreign key identifiers.

Comment:

 r5040 commits a `NotImplementedError` for this operator, which has not
 been implemented for many-to-one relations.

-- 
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/1140#comment:1>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python