CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
Andrea
Andrea

Reputation: 658

How to create a column 'mapping' that still works in SQL queries?

I have a very simple model as below:

class User(UserMixin, db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), index=True)
    role = db.Column(db.String(64))

and then a simple query that I'm using in my view to filter users by roles:

roles = list(map(str, query_params.get('roles', [])))
User.query.filter(User.role.in_(roles))

Roles start with a capital letter ('Director', 'Manager', etc...) and I want to add a mapping to the column which returns it in lower case. I thought column_property() or @hybrid_property would do the job but I'm either wrong, or I'm making a mistake in the implementation.

I tried these changes:

@hybrid_property
def role_tag(self):
    return self.role.lower()

User.query.filter(User.role_tag.in_(roles))

The property indeed returns the lower case value, but it doesn't work in the query raising this exception:

AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with User.profile_role has an attribute 'lower'

I then tried to change the model as such:

role_tag = column_property(role.lower())

which instead raise another exception on the Model directly.

AttributeError: Neither 'Column' object nor 'Comparator' object has an attribute 'lower'

I understand the issue is related to how they operate, if at class or instance level... But in the end I haven't been able to find the correct implementation for my use case.

Upvotes: 0

Views: 482

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52937

The solution is to define the behaviour separately for instance and class context, or Python and SQL context:

class User(UserMixin, db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), index=True)
    role = db.Column(db.String(64))

    @hybrid_property
    def role_tag(self):
        return self.role.lower()

    @role_tag.expression
    def role_tag(cls):
        return db.func.lower(cls.role)

Upvotes: 1

Related Questions