How to properly use a property for self._value or self.parent.value

[Moritz Schlarb]

Hi at all,

I haven't found something by googling, but I'm also not sure about the right search terms, so I hope some can help me here.

I have a model like this:

class Parent(DeclarativeBase):

    id = Column(Integer, primary_key=True)

    time = Column(Float)

    another_id = Column(Integer)

class Child(DeclarativeBase):

    id = Column(Integer, primary_key=True)

    parent_id = Column(Integer, ForeignKey('parent.id'), nullable=False)

    parent = relationship('Parent')

    _time = Column(Float, nullable=True)

    @property

    def time(self):

        return self._time or self.parent.time

So basically, a Child object may set it's own value for time, but if it's not set, it uses the value from its parent.

This works, but I would also like to be able to do something like:

session.query(Child).filter(Child.parent.another_id==2)

(Which would throw Neither 'InstrumentedAttribute' object nor 'Comparator' object has an attribute 'another_id')

And I wondered if this would be somewhat possible by using some of sqlalchemy's fancy extensions! ;)

Thanks in advance,

Moritz

[Michael Bayer]

On Apr 21, 2012, at 7:06 AM, Moritz Schlarb wrote:

So basically, a Child object may set it's own value for time, but if it's not set, it uses the value from its parent.

This works, but I would also like to be able to do something like:

session.query(Child).filter(Child.parent.another_id==2)

(Which would throw Neither 'InstrumentedAttribute' object nor 'Comparator' object has an attribute 'another_id')

SQLAlchemy doesn't implicitly generate joins when you call attributes from relationship-bound attribute (here, Child.parent is a relationship-bound attribute and "another_id" is an attribute you're trying to call from it).

instead, you need to think in SQL and tell SQLAlchemy that you'd like to join from Child to Parent:

query(Child).join(Child.parent).filter(Parent.another_id==2)

joining is covered in the tutorial at http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#querying-with-joins .

Also when you do a query from inside of a @property, you might want to use object_session() to get at the Session.  A similar example to what you're doing is here:

http://docs.sqlalchemy.org/en/latest/orm/relationships.html#building-query-enabled-properties

[Moritz Schlarb]

Thank you, Michael!

But could I achieve it somehow using hybrid properties and a case statement?

I tried something like:

    @hybrid_property

    def time(self):

        return self._time or self.event.time

    

    @time.expression

    def time(cls):

        return case([(cls._time != None, cls._time)], else_=cls.parent.time)

but that doesn't work:

AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object has an attribute 'time'

Now again I would have to join the corresponding Parent class and filter then, but I can't do that as part of the case statement (http://stackoverflow.com/a/6994915/1175818), so I wondered of there could be any other trick that sqlalchemy offers.

I have no problem in joining to the Parent class, I just don't know how I could do it in the hybrid expression.

Regards,

Moritz

[Michael Bayer]

On May 2, 2012, at 8:34 AM, Moritz Schlarb wrote:

Thank you, Michael!

But could I achieve it somehow using hybrid properties and a case statement?

I tried something like:

    @hybrid_property

    def time(self):

        return self._time or self.event.time

    

    @time.expression

    def time(cls):

        return case([(cls._time != None, cls._time)], else_=cls.parent.time)

but that doesn't work:

AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object has an attribute 'time'

you say "else_=Parent.time" and the "time" property is only usable at the SQL level when an appropriate join to Parent is present in the query (outside of the hybrid).

sess.query(Child).join(Child.parent).filter(Child.time == xyz)

Now again I would have to join the corresponding Parent class and filter then, but I can't do that as part of the case statement (http://stackoverflow.com/a/6994915/1175818), so I wondered of there could be any other trick that sqlalchemy offers.

I have no problem in joining to the Parent class, I just don't know how I could do it in the hybrid expression.

Regards,

Moritz

Am Samstag, 21. April 2012 16:15:50 UTC+2 schrieb Michael Bayer:

On Apr 21, 2012, at 7:06 AM, Moritz Schlarb wrote:

So basically, a Child object may set it's own value for time, but if it's not set, it uses the value from its parent.

This works, but I would also like to be able to do something like:

session.query(Child).filter(Child.parent.another_id==2)

(Which would throw Neither 'InstrumentedAttribute' object nor 'Comparator' object has an attribute 'another_id')

SQLAlchemy doesn't implicitly generate joins when you call attributes from relationship-bound attribute (here, Child.parent is a relationship-bound attribute and "another_id" is an attribute you're trying to call from it).

instead, you need to think in SQL and tell SQLAlchemy that you'd like to join from Child to Parent:

query(Child).join(Child.parent).filter(Parent.another_id==2)

joining is covered in the tutorial at http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#querying-with-joins .

Also when you do a query from inside of a @property, you might want to use object_session() to get at the Session.  A similar example to what you're doing is here:

http://docs.sqlalchemy.org/en/latest/orm/relationships.html#building-query-enabled-properties

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/oouab8xG8K4J.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.