Yesterday I asked about a complex (to me) MySQL query that I needed to use as a column_property. A kind user (who doesn't do SQLAlchemy so I won't bother to link him) answered the SQL part of it:
select *
from (
select t.*,
case when count(*) over(partition by firstname, lastname) > 1
then row_number() over(partition by firstname, lastname order by startdate)
end as identifier
from person t
) t
where id = ?
After far too long a struggle, I was able to convert this to a function in SQLA:
def get_identifier(id):
mycase = db.case([
(
db.func.count().over(partition_by=[Person.firstname, Person.lastname]) > 1,
db.func.row_number().over(partition_by=[Person.firstname, Person.lastname], order_by=Person.startdate)
)
]).label('identifier')
subq = db.session.query(Person.id, mycase).subquery('subq')
identifier = db.session.query(subq.c.identifier).\
filter(subq.c.id == id).scalar()
return identifier
However, I am stuck on how to make this work as a column_property, and there aren't that many examples of that I can find. Even the simple part--if I'm in the Person class, then I don't have access to Person.firstname etc.--is confusing. And a further complication is that the actual query (the above was simplified) uses a complex order_by clause that is itself generated from another column_property in Person; that clause actually looks like order_by=[*Person.person_order].
If column_property isn't right, is there some other way to attach this function as an extra column, or anything equivalent (e.g. a method that can be called) in a Person object?