I want to use expressions is ORM queries like this. Profile.query.filter(Profile.complete).all() Where complete is a calculated field Sqlalchemy docs shows the below So i've attempted to copy it with the following. But the output sql query is as below - the case isn't compared agaisnt anything. So my output is all profiles, instead of only complete ones. What am I

Case in Sqlalchemy hybrid_property / expression error

I want to use expressions is ORM queries like this.

Profile.query.filter(Profile.complete).all()

Where complete is a calculated field

Sqlalchemy docs shows the below

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    firstname = Column(String(50))
    lastname = Column(String(50))

    @hybrid_property
    def fullname(self):
        if self.firstname is not None:
            return self.firstname + " " + self.lastname
        else:
            return self.lastname

    @fullname.expression
    def fullname(cls):
        return case([
            (cls.firstname != None, cls.firstname + " " + cls.lastname),
        ], else_ = cls.lastname)

So i’ve attempted to copy it with the following.

@complete.expression
def complete(cls):
    cased = case([(and_(cls.about != None, cls.name != None, cls.lat != None, cls.confirmed_tc == True), True)],
                 else_=False)
    return cased

But the output sql query is as below – the case isn’t compared agaisnt anything.

SELECT profile.id AS profile_id, profile.telephone AS profile_telephone, profile.about AS profile_about, profile.name AS profile_name, profile.blurb AS profile_blurb, profile.organization AS profile_organization, profile.lat AS profile_lat, profile.long AS profile_long, profile.address AS profile_address, profile.website AS profile_website, profile.profile_image AS profile_profile_image, profile.crb_image AS profile_crb_image, profile.services AS profile_services, profile.price_to AS profile_price_to, profile.price_desc AS profile_price_desc, profile.usual_rate AS profile_usual_rate, profile.agerange AS profile_agerange, profile.charge_per_session AS profile_charge_per_session, profile.display_telephone AS profile_display_telephone, profile.display_email AS profile_display_email, profile.display_address AS profile_display_address, profile.display_website AS profile_display_website, profile.display_profile AS profile_display_profile, profile.allow_messages AS profile_allow_messages, profile.user_id AS profile_user_id, profile.confirmed_tc AS profile_confirmed_tc, profile.national AS profile_national, profile.festival_participant AS profile_festival_participant 
FROM profile 
WHERE CASE WHEN (profile.about IS NOT NULL AND profile.name IS NOT NULL AND profile.lat IS NOT NULL AND profile.confirmed_tc = true) THEN %(param_1)s ELSE %(param_2)s END

So my output is all profiles, instead of only complete ones.

What am I doing wrong? I guess I don’t need to output a case?

Is there an easier solution? The actual SQL would be like this, but I don’t want to filter each field, I want to use the ORM to calculate the complete field automatically.

select * from profile where profile.about IS NOT NULL AND profile.name IS NOT NULL AND profile.lat IS NOT NULL AND profile.confirmed_tc = true

Answer

I made it work with

and_(cls.blurb != None, cls.services != None, cls.about != None, cls.name != None, cls.lat != None, cls.confirmed_tc == True)

Rather than using case

Advertisement

Answer