column_property and class Mixin problem

[sector119]

Hello.

Can't get this to work, I want to get users who is online - users where last_read column <= now() - 30 minutes  

With DBSession.query(User).filter(User.is_online) query

But get the following error:

  File "/home/eps/devel/tourclub/pbb/pbb/models/__init__.py", line 147, in <module>

    class User(UserMixin, Base):

  File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/ext/declarative.py", line 1273, in __init__

    _as_declarative(cls, classname, cls.__dict__)

  File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/ext/declarative.py", line 1078, in _as_declarative

    column_copies[obj] = getattr(cls, name)

  File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/ext/declarative.py", line 1480, in __get__

    return desc.fget(cls)

  File "/home/eps/devel/tourclub/pbb/pbb/models/__init__.py", line 143, in is_online

    return column_property(case([(cls.last_read is not None, cls.last_read <= func.now() - datetime.timedelta(minutes=30))], else_=False))

  File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/sql/expression.py", line 607, in case

    return _Case(whens, value=value, else_=else_)

  File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/sql/expression.py", line 3016, in __init__

    _literal_as_binds(r)) for (c, r) in whens

  File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/sql/expression.py", line 1410, in _no_literals

    "bound value." % element)

sqlalchemy.exc.ArgumentError: Ambiguous literal: True.  Use the 'text()' function to indicate a SQL expression literal, or 'literal()' to indicate a bound value.

I want to get something like this but in SA...

SELECT * FROM "user" u WHERE (CASE WHEN u.last_read IS NULL THEN False ELSE u.last_read <= now() - 30 * INTERVAL '1 minute' END) IS True;

I use next UserMixin class and User declarative model:

class UserMixin(object):

    id = Column(Integer, primary_key=True)

    username = Column(String(255), unique=True, nullable=False)

    ...

    last_read = Column(DateTime)

    @declared_attr

    def is_online(cls):

        return column_property(case([(cls.last_read is not None, cls.last_read <= func.now() - datetime.timedelta(minutes=30))], else_=False))

class User(UserMixin, Base):

    __tablename__ = 'user'

[Michael Bayer]

you'd need to say "cls.last_read != None" to produce "IS NOT NULL". You might need else_=literal(False) too.

[sector119]

Now I get

 

  File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/sql/visitors.py", line 59, in _compiler_dispatch

    return getter(visitor)(self, **kw)

  File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/sql/compiler.py", line 370, in visit_column

    raise exc.CompileError("Cannot compile Column object until "

CompileError: Cannot compile Column object until it's 'name' is assigned.

With

case([(cls.last_read != None, cls.last_read <= func.now() - datetime.timedelta(minutes=30))], else_=literal(False)) 

case([(cls.last_read != None, literal(cls.last_read <= func.now() - datetime.timedelta(minutes=30)))], else_=literal(False)) 

case([(cls.last_read != None, literal(cls.last_read <= func.now() - datetime.timedelta(minutes=30)).label('foo'))], else_=literal(False))

[Michael Bayer]

please forego the usage of column_property() here in favor of a hybrid.  An example is attached, and the SQLAlchemy documentation is being updated right now to stress that hybrids should be used unless there's a specific performance advantage to column_property().

[sector119]

Thanks a lot, Michael! Works like a charm!