mardi 1 septembre 2015

SQLAlchemy query for distinct records using hyrid_propery expression

I'm having some trouble querying for distinct records on a class that uses hybrid_propery.expression.

Querying for distinct records on fields that are in the class work fine:

session.query(foo).distinct(foo.some_field).group_by(foo.some_field)

As well as performing a simple select using the expression propery:

session.query(foo).filter_by(expression_field=something)

But if I try finding distinct records using the expression:

session.query(foo).distinct(foo.expression_field).group_by(foo.expression_field)

It always returns just the first record it finds. Querying using only distinct or group_by causes it to return all the records.

This is my class setup, I'm using sqlite for my database:

class foo(Base):
    __tablename__ = 'foo'
    id = Column(Integer, primary_key=True)
    some_field = Column(Text(100))
    bar_id = Column(Integer, ForeignKey('bar.id'))

    bar = relationship('bar')

    @hybrid_property
    def expression_field(self):
        return self.bar.expression_field

    @expression_field.expression
    def expression_field(cls):
        return select([bar.expression_field]).where(cls.bar_id == bar.id).as_scalar()

class bar(Base)
    __tablename__ = 'bar'
    id = Column(Integer, primary_key=True)
    expression_field = Column(Text(100))

Is there a problem with the expression, or the query? What would be the solution to getting distinct values for expression_field.

Aucun commentaire:

Enregistrer un commentaire