Aha, thank you, I was quite close and did not think of also adding the
remote() to Item.parent_id!
On Friday, August 7, 2015 at 11:18:33 PM UTC+2, Michael Bayer wrote:
>
>
>
> On 8/7/15 5:05 PM, SElsner wrote:
> > Hello,
> >
> > I am trying to create a declarative calss, which is self referential
> > and uses a custom primaryjoin condition, involving other columns like
> > a "is_deleted" column:
> >
> >
> > class Item(Base):
> >
> > __tablename__ = "items"
> >
> > uuid = Column(UUID(), primary_key=True)
> > is_deleted = Column(Boolean, default=False)
> > parent_id = Column(UUID(), ForeignKey('items.uuid'))
> > parent = relationship('Item', remote_side=[uuid],
> > back_populates="children")
> > children = relationship('Item',
> > primaryjoin="and_(Item.parent_id ==
> > Item.uuid, Item.is_deleted == False)",
> > back_populates="parent")
> >
> >
> > Right now when asking for all non-deleted children (any_item.children)
> > I get all all Items, not matter their "is_deleted" value. I found,
> > this is due to the query rendering as:
> >
> > SELECT ..... FROM items WHERE items.parent_id = 'abc33424dsfsdf' AND 0
> > = false;
> >
> > It seems like the "Item.is_deleted == False" is actually evaluated to
> > "0 = false". How can I make SQLA use "items.is_deleted = 0" instead? I
> > tried to use remote() and foreign() but they seem to be meant for
> > something else.
> mmm nope, this is exactly the reason remote() was created,
> self-referential with fine-grained rules inside the primaryjoin, since
> you are on MySQL w/ a custom uuid type I did a full test to make sure
> nothing weird going on, works fine see below and note remote():
>
> from sqlalchemy import *
> from sqlalchemy.orm import *
> from sqlalchemy.ext.declarative import declarative_base
> import uuid
>
>
> class UUID(TypeDecorator):
> """Platform-independent GUID type.
>
> Uses Postgresql's UUID type, otherwise uses
> CHAR(32), storing as stringified hex values.
>
> """
> impl = CHAR
>
> def load_dialect_impl(self, dialect):
> if dialect.name == 'postgresql':
> return dialect.type_descriptor(UUID())
> else:
> return dialect.type_descriptor(CHAR(32))
>
> def process_bind_param(self, value, dialect):
> if value is None:
> return value
> elif dialect.name == 'postgresql':
> return str(value)
> else:
> if not isinstance(value, uuid.UUID):
> return "%.32x" % uuid.UUID(value)
> else:
> # hexstring
> return "%.32x" % value
>
> def process_result_value(self, value, dialect):
> if value is None:
> return value
> else:
> return uuid.UUID(value)
>
> Base = declarative_base()
>
>
> class Item(Base):
>
> __tablename__ = "items"
>
> uuid = Column(UUID(), default=uuid.uuid4, primary_key=True)
> is_deleted = Column(Boolean, default=False)
> parent_id = Column(UUID(), ForeignKey('items.uuid'))
> parent = relationship(
> 'Item', remote_side=[uuid],
> back_populates="children")
> children = relationship(
> 'Item',
> primaryjoin="and_(remote(Item.parent_id) == Item.uuid, "
> "remote(Item.is_deleted) == False)",
> back_populates="parent")
>
> e = create_engine("mysql://scott:tiger@localhost/test", echo=True)
> Base.metadata.drop_all(e)
> Base.metadata.create_all(e)
>
> s = Session(e)
>
> i1id = uuid.uuid4()
>
> i1, i2, i3, i4 = Item(uuid=i1id), Item(), Item(), Item(is_deleted=True)
>
> i1.children = [i2, i3, i4]
> s.add(i1)
> s.commit()
> s.close()
>
> i1 = s.query(Item).filter_by(uuid=i1id).one()
>
> print i1.children
>
>
>
> query at the end is:
>
> SELECT items.uuid AS items_uuid, items.is_deleted AS items_is_deleted,
> items.parent_id AS items_parent_id
> FROM items
> WHERE items.parent_id = %s AND items.is_deleted = 0
>
>
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
