CODEWITHSUNDEEP
sqlalchemyflask-sqlalchemy
Romeo Mihalcea
Romeo Mihalcea

Reputation: 10252

Selecting a record via column_property

I have 2 models simplified for the sake of this example (Domain, Visit) and I want to select the last visit timestamp for a given domain via column property

Domain.last_visit = db.column_property(
  db.select(
    [ db.func.ifnull( Visit.timestamp, 0 ) ],
    Visit.domain_id == Domain.id
  ).order_by(Visit.timestamp.desc()).limit(1).correlate(Visit.__table__).label('domain_last_visit'),
  deferred = True
)

My problem is that the value always returns NULL instead of 0. I'm pretty sure it's because I don't know how to select the record I want and I placed that limit over there.

Any ideas what am I doing wrong?

Upvotes: 0

Views: 478

Answers (1)

van
van

Reputation: 76962

Your IFNULL will only fallback to the 0 in case you have rows in Visit which have timestamp = NULL. But it does not cover the case when a Domain has no corresponding Visit records yet.

One way to achive that would be to use MAX aggregate instead of ORDER BY ... DESC and appy IFNULL on the result:

Domain.last_visit = column_property(
    select( [ func.ifnull(func.max( Visit.timestamp ), 0) ],
        Visit.domain_id == Domain.id
    ).correlate_except(Visit).label('domain_last_visit'),
    #deferred = True
)

Please note usage of correlate_except instead of correlate found in your code, which does not properly filter the results.

You might also consider Hybrid Attributes.

Upvotes: 1

Related Questions