CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
Tri
Tri

Reputation: 3039

Flask SQLAlchemy query with concatenated columns

I have a models like this:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    first_name = db.Column(db.String(64), index=True)
    last_name = db.Column(db.String(64), index=True)
    
    def full_name(self):
        return '%s %s' % (self.first_name, self.last_name)

I want to get the full_name method in a query, I try it like it:

user = db.session.query(User.full_name()).all()

But I get this error message:

TypeError: full_name() missing 1 required positional argument: 'self'

Then I try to call the function without brackets:

user = db.session.query(User.full_name).all()

Then I got this error message:

sqlalchemy.exc.InvalidRequestError: SQL expression, column, or mapped entity expected - got '<function User.full_name at 0x7f265960aae8>'

So, what is the correct way to query full_name() method in the User model..?

Upvotes: 3

Views: 3084

Answers (2)

snakecharmerb
snakecharmerb

Reputation: 55660

There are a number of ways to accomplish this. Firstly, a hybrid attribute, a property that defines full_name on an instance and on the class (for querying).

This example is pure SQLAlchemy, but the hybrid attributes should be the same in Flask-SQLAlchemy.

import sqlalchemy as sa
from sqlalchemy.ext import hybrid

class User(Base):
    __tablename__ = 'users' 
                                                                                                                                                                        
    id = sa.Column(sa.Integer, primary_key=True)
    first_name = sa.Column(sa.String)
    last_name = sa.Column(sa.String)

    @hybrid.hybrid_property
    def full_name(self):  
        return self.first_name + ' ' + self.last_name

users = session.query(User).filter_by(full_name='Joan Doe').all()

Credit to Ilja for pointing out that this can be done using a single method in this case. This works because SQLAlchemy maps the + operator to the database's CONCAT function. If this weren't the case, an additional property decorated with hybrid.expression would be necessary to implement the behaviour at the class level.

SQLAlchemy inserts the necessary expressions into SQL statements as required, for example:

SELECT users.id, users.first_name, users.last_name 
FROM users 
WHERE users.first_name || ? || users.last_name = ?

Another way to achieve this is by defining a column_property:

from sqlalchemy import orm

class User(Base):
    ...
    full_name = orm.column_property(first_name + " " + last_name)

This inserts an expression into all queries on the model, so

query = sa.select(User)

will generate this SQL:

SELECT users.first_name || ? || users.last_name AS anon_1,
       users.id, 
       users.first_name, 
       users.last_name 
FROM users

Finally, there are computed columns. These are conceptually similar to column properties, but implemented on the database side (provided the database supports the required syntax). The database may or may not physically store the computed value; it may be possible to condition this behaviour by passing the persisted boolean keyword argument to Computed.

class User(Base):
    ...
    full_name = sa.Column(sa.String, sa.Computed(first_name + ' ' + last_name))

Upvotes: 7

Dušan Maďar
Dušan Maďar

Reputation: 9869

You can use @classmethod.

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    first_name = db.Column(db.String(64), index=True)
    last_name = db.Column(db.String(64), index=True)

    @classmethod
    def full_name_filter(cls, fname, lname):
        return (cls.first_name == fname, cls.last_name == lname)

Then

user = db.session.query(User).filter(*User.full_name_filter("first", "last")).all()

Upvotes: 1

Related Questions