I would like to use jQuery DataTables with my SQLAlchemy models. Datatables can be configured to use server-side processing for things like sorting, filtering, searching, etc.
I asked this question concerning reducing repetition in my code for the filtering since I have many models with various column names that will be filtered on.
The code that GG_Python provided works well so far.
Now I am having issues with filtering on model relationships. For example, I have a User model like below
class User(Model):
...
id = Column(Integer, Sequence('user_id_seq'), primary_key=True)
region_id = Column(Integer, ForeignKey('regions.id'), nullable=False)
firstname = Column(String(20))
...
and a Region model like below
class Region(Model):
...
id = Column(Integer, Sequence('region_id_seq'), primary_key=True)
name = Column(String(50), unique=True)
users = relationship('User', backref='region', order_by='User.id', lazy='joined')
I am struggling wrapping my head around how to query this in a way that will fit in a function. I am afraid I am looking at it in a far too complicated manner.
What am I missing to be able to search on the relationships that have been set up, ideally building off of GG_Python's answer here?
The type(mapper[col_name]) is either sqlalchemy.orm.properties.ColumnProperty or sqlalchemy.orm.properties.RelationshipProperty
q = User.query
rv = rv = request.values
q = filter_model_by_request(q, User, rv):
...
def filter_model_by_request(qry, model, rv):
mapper = inspect(model).attrs
col_names = list(set([c.key for c in mapper]) & set(rv.keys()))
for col_name in col_names:
if type(mapper[col_name]) == ColumnProperty:
col = mapper[col_name].columns[0]
col_type = type(col.type)
if col_type == String: # filter for String
qry = qry.filter(col.ilike('%{0}%'.format(rv.get(col_name))))
elif: ... # filter other types
elif type(mapper[col_name]) == RelationshipProperty:
# what the heck goes here?
How can I perform a query for users where user.region.name = SearchString,? Hopefully in a function that keeps it DRY
