CODEWITHSUNDEEP
pythonsqlalchemy
JACK M
JACK M

Reputation: 2851

Conditionally adding several filters to a SQLAlchemy query without duplicating code

I have a SQLAlchemy model:

class Ticket(db.Model):

 __tablename__ = 'ticket'

 id = db.Column(INTEGER(unsigned=True), primary_key=True, nullable=False,
 autoincrement=True)
 cluster = db.Column(db.VARCHAR(128))

 @classmethod
 def get(cls, cluster=None):
     query = db.session.query(Ticket)

     if cluster is not None:
         query = query.filter(Ticket.cluster==cluster)

     return query.one()

If I add a new column and would like to extend the get method, I have to add one if xxx is not None like this below:

 @classmethod
 def get(cls, cluster=None, user=None):
     query = db.session.query(Ticket)

     if cluster is not None:
         query = query.filter(Ticket.cluster==cluster)
     if user is not None:
         query = query.filter(Ticket.user==user)

     return query.one()

Is there any way I could make this more efficient? If I have too many columns, the get method would become so ugly.

Upvotes: 0

Views: 148

Answers (1)

Charles Duffy
Charles Duffy

Reputation: 295815

As always, if you don't want to write something repetitive, use a loop:

@classmethod
def get(cls, **kwargs):
    query = db.session.query(Ticket)
    for k, v in kwargs.items():
        query = query.filter(getattr(table, k) == v)
    return query.one()

Because we're no longer setting the cluster=None/user=None as defaults (but instead depending on things that weren't specified by the caller simply never being added to kwargs), we no longer need to prevent filters for null values from being added: The only way a null value will end up in the argument list is if the user actually asked to search for a value of None; so this new code is able to honor that request should it ever take place.

If you prefer to retain the calling convention where cluster and user can be passed positionally (but the user can't search for a value of None), see the initial version of this answer.

Upvotes: 3

Related Questions