SQLAlchemy: How to output a calculated field that takes in parameters from the user as input

Question

I have a model something like this:

class User:
    __tablename__ = "user"

    first_name = Column(String(), nullable=False)
    last_name = Column(String(), nullable=False)

I then have a query like this:

name = ["...", ...]
user_query = (
    db.session.query(User)
    .add_column(case([
        (and_(User.first_name).ilike(f'%{name}%'), User.last_name.ilike(f'%{name}%')), True),
        (or_(User.first_name).ilike(f'%{name}%'), User.last_name.ilike(f'%{name}%')), False)
    ]).label('perfect_match'))
    .order_by(literal_column("'perfect_match'"))
)

This is obviously an over-simplification, but essentially I'm trying to do a search across fields and sort the perfect matches separately from the imperfect matches.

This is giving me an error saying that the column "perfect_match" doesn't exist, so I think I'm using add_column incorrectly here.

I also tried using hybrid_method like so:

@hybrid_method
def perfect_match(self, terms):
    perfect_match = True
    matched = False
    for term in terms:
        if term.lower() in self.first_name.lower() or term.lower() in self.last_name.lower():
            matched = True
            continue
        perfect_match = False

    return perfect_match if matched else None

@perfect_match.expression
def perfect_match(self, terms):
    perfect_match = and_(...)
    imperfect_match = or_(...)

    return case(...)

And then my query looks like this:

name = [...]
user_query = (
    db.session.query(User)
    .filter(User.perfect_match(name) != None)
    .order_by(User.perfect_match(name))

I want perfect_match to be in the output. Here's my desired SQL:

SELECT
    first_name,
    last_name,
    case (
        ...
    ) as perfect_match
FROM user
WHERE perfect_match != NULL
ORDER BY perfect_match

The first using add_column gives me that SQL, but SQLAlchemy errors saying it can't column perfect_match does not exist. The second (using hybrid_method) puts the case in the WHERE and ORDER BY clauses (probably inefficient?) and doesn't include perfect_match in the output, but it does seem to be working properly. I need to use that output column to determine where the perfect matches end.

I've tried:

• adding perfect_match as a Column(Boolean)
• adding perfect_match as a column_property()
• adding perfect_match as .query(User, literal_column("'perfect_match'"))

Any thoughts? Most of the examples I've seen use hybrid_property, but I need to take in an argument here. I'm pretty new to SQLAlchemy, so I probably missed something.