CODEWITHSUNDEEP
pythonsqlalchemy
smallpants
smallpants

Reputation: 490

Variable filter for SQLAlchemy Query

I'm adding a search feature to my application (created using PyQt5) that will allow the user to search an archive table in the database. I've provided applicable fields for the user to choose to match rows with. I'm having some trouble with the query filter use only what was provided by the user, given that the other fields would be empty strings.

Here's what I have so far:

 def search_for_order(pierre):
    fields = {'archive.pat.firstname': pierre.search_firstname.text(),
              'archive.pat.lastname': pierre.search_lastname.text(),
              'archive.pat.address': pierre.search_address.text(),
              'archive.pat.phone': pierre.search_phone.text(),
              'archive.compound.compname': pierre.search_compname.text(),
              'archive.compound.compstrength': pierre.search_compstrength.text(),
              'archive.compound.compform': pierre.search_compform.currentText(),
              'archive.doc.lastname': pierre.search_doctor.text(),
              'archive.clinic.clinicname': pierre.search_clinic.text()
             }

    filters = {}

    for field, value in fields.items():
        if value is not '':
            filters[field] = value

     query = session.query(Archive).join(Patient, Prescribers, Clinic, Compound)\
             .filter(and_(field == value for field, value in filters.items())).all()

The fields dictionary collects the values of all the fields in the search form. Some of them will be blank, resulting in empty strings. filters is intended to be a dictionary of the object names and the value to match that.

Upvotes: 1

Views: 5737

Answers (2)

le_affan
le_affan

Reputation: 314

The problem lies in your definition of the expressions within your and_ conjunction. As of now you're comparing each field with the corresponding value which of course returns false for each comparison.

To properly populate the and_ conjunction you have to create a list of what sqlalchemy calls BinaryExpression objects.

In order to do so I'd change your code like this:

1) First use actual references to your table classes in your definition of fields:

fields = {
    (Patient, 'firstname'): pierre.search_firstname.text(),
    (Patient, 'lastname'): pierre.search_lastname.text(),
    (Patient, 'address'): pierre.search_address.text(),
    (Patient, 'phone'): pierre.search_phone.text(),
    (Compound, 'compname'): pierre.search_compname.text(),
    (Compound, 'compstrength'): pierre.search_compstrength.text(),
    (Compound, 'compform'): pierre.search_compform.currentText(),
    (Prescribers, 'lastname'): pierre.search_doctor.text(),
    (Clinic, 'clinicname'): pierre.search_clinic.text()
}

2) Define filters as a list instead of a dictionary:

filters = list()

3) To populate the filters list explode the tuple of table and fieldname used as key in the fields dictionary and add the value to again create tuples but now with three elements. Append each of the newly created tuples to the list of filters:

for table_field, value in fields.items():
    table, field = table_field
    if value:
        filters.append((table, field, value))

4) Now transform the created list of filter definitions to a list of BinaryExpression objects usable by sqlalchemy:

binary_expressions = [getattr(table, attribute) == value for table, attribute, value in filters]

5) Finally apply the binary expressions to your query, make sure it's presented to the and_ conjunction in a consumable form:

query = session.query(Archive).join(Patient, Prescribers, Clinic, Compound)\
         .filter(and_(*binary_expressions)).all()

I'm not able to test that solution within your configuration, but a similar test using my environment was successful.

Upvotes: 3

jsbueno
jsbueno

Reputation: 110706

Once you get a query object bound to a table in SqlAlquemy - that is, what is returned by session.query(Archive) in the code above -, calling some methods on that object will return a new, modified query, where that filter is already applied.

So, my preferred way of combining several and filters is to start from the bare query, iterate over the filters to be used, and for each, add a new .filter call and reassign the query:

query = session.query(Archive).join(Patient, Prescribers, Clinic, Compound)
for field, value in filters.items():
    query = query.filter(field == value)
results = query.all()

Using and_ or or_ as you intend can also work - in the case of your example, the only thing missing was an *. Without an * preceeding the generator expression, it is passed as the first (and sole) parameter to and_. With a prefixed *, all elements in the iterator are unpacked in place, each one passed as an argument:

...
.filter(and_(*(field == value for field, value in filters.items()))).all()

Upvotes: 0

Related Questions