On Monday, November 26, 2012 5:39:47 PM UTC-8, Michael Bayer wrote:
>
>
> On Nov 26, 2012, at 8:25 PM, robert wrote:
>
> Hello,
>
> Consider the following class and its hybrid property:
>
> class Interval(Base):
>> __tablename__ = 'interval'
>> id = Column(Integer, primary_key=True)
>> start = Column(Integer, nullable=False)
>> end = Column(Integer, nullable=False)
>
>
>
> @hybrid_property
>> def contains_one(self):
>> return (self.start <= 1) & (self.end > 1)
>
>
> Querying for all objects that verify the property contains_one can be
> conveniently written using a filter_by:
>
>> print session.query(Interval).filter_by(contains_one=True)
>> # SELECT interval.id AS interval_id, interval.start AS interval_start,
>> interval."end" AS interval_end
>> # FROM interval
>> # WHERE (interval.start <= :start_1 AND interval."end" > :end_1) =
>> :param_1
>>
>
> However, note that the resulting SQL compares the property's underlying
> expression to param_1 ('1', in our case). This does not play well with some
> database engines, and results in potentially suboptimal queries missing
> possible indices. We have noticed this problem with mySQL 5.0.
>
> Note that using a filter would result in a better query:
>
>> print session.query(Interval).filter(Interval.contains_one)
>> # SELECT interval.id AS interval_id, interval.start AS interval_start,
>> interval."end" AS interval_end
>> # FROM interval
>> # WHERE interval.start <= :start_1 AND interval."end" > :end_1
>
>
> I believe the problem comes from the algebraic operators implemented by
> BinaryExpression (and BooleanClauseList), which do not perform Boolean
> logic when compared to a Boolean value:
>
>> print Interval.contains_one == True
>> # (interval.start <= :start_1 AND interval."end" > :end_1) = :param_1
>
>
> Any chance having this fixed?
>
>
> Interesting, the suggestion that a boolean expression when compared to
> True should implicitly omit the True (and I suppose when compared to False
> should drop the False and negate itself also). Though I'm hesitant to add
> "folding" rules like these to the expression system as they often produce
> other unexpected surprises. Generally we like to keep the expression
> system as wysiwig as possible.
>
> True is also a tricky one because it really should be producing "is True",
> but then not every database even supports boolean types, including mysql.
>
> In this case, there's a super easy solution requiring no changes to
> SQLAlchemy which is to just not say "x == True". "contains_one" is
> already a boolean, so just use it in place:
>
> query(Interval).filter(Interval.contains_one)
>
>
My concern is that it is really easy and tempting to use filter_by.
Especially if one wants to verify the negation of a
property: query(Interval).filter(not_(Interval.contains_one))
VS query(Interval).filter_by(contains_one=False). I have seen occurrences
of this syntax used a lot in our code and unless the resulting SQL query is
well inspected it is really easy to miss the potential problem it creates.
> if you really really want it to work exactly the way you're asking, that
> one would type the expression "== True" which is then dropped, you can use
> @comparator for now and override __eq__()/__ne__() etc. to implicitly drop
> True and negate on False.
>
>
I will try to play around with the idea of specializing how hybrid
properties compare to Boolean and see how it works. It sounds like it could
be a reasonable and general pattern.
--
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/-/qKV6RhQfMA8J.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
