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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
