Flask web app: How to filter database records using multiple checkboxes?

I'm working on a CRUD Flask web app that allows scheduling truck appointments. I'm able to create, update, and delete appointments, but I'm struggling to retrieve specific data from the database.

What I want to achieve is to have three checkboxes, each representing a type of material that can be shipped. When a user selects one or more checkboxes and clicks on the submit button, I want the app to display only the appointments that correspond to the selected materials in a table.

I've implemented the filtering functionality by modifying the index() function in app.py. The code below runs, but it only works for one selected material at a time:

@app.route('/', methods=['GET', 'POST'])
def index():
    search_material = request.args.get('material_filter')
    appts = appts_db.query.filter_by(material=search_material) \
        .order_by(appts_db.pickup_date).all()

    return render_template('index.html', appts=appts)

In index.html, I have three checkboxes and a submit button:

<form action="/" method="GET">
    <label>Select a material:</label><br>
    <input type="checkbox" name="material_filter" id="HCl_filter" value="HCl">
    <label for="HCl">HCl</label><br>
    <input type="checkbox" name="material_filter" id="Caustic_filter" value="Caustic">
    <label for="Caustic">Caustic</label><br>
    <input type="checkbox" name="material_filter" id="Bleach_filter" value="Bleach">
    <label for="Bleach">Bleach</label><br>
    <input type="submit" value="Filter Appointments">
</form>

<h3>Appointment List:</h3>
<table>
    <tr>
        <th>Carrier</th>
        <th>Material</th>
        <th>Pickup Date [YYYY-MM-DD]</th>
    </tr>

    {% for appt in appts %}
        <tr>
            <td>{{ appt.carrier }}</td>
            <td>{{ appt.material }}</td>
            <td>{{ appt.pickup_date }}</td>
        </tr>
    {% endfor %}
</table>

What changes do I need to make to allow selecting multiple materials and display those in the table?

Solution

There are two changes needed to make this work.

First of all, request.args.get will only retrieve a single value from the form data. This is usually OK, but not if the form has multiple inputs with the same name attribute. In this case, we must use request.args.getlist to get all the values as a list.

Secondly, the filter_by method cannot express selecting from a list of values for the same attribute. In this case, we want to use .filter in combination with the attribute's .in_ method.

So the route should look like this:

@app.route('/', methods=['GET', 'POST'])
def index():
    # Get values for all of the material_filter inputs.
    search_materials = request.args.getlist('material_filter')
    # Select all rows that match the inputs.
    appts = appts_db.query.filter(appts_db.material.in_(search_materials)) \
        .order_by(appts_db.pickup_date).all()
    return render_template('index.html', appts=appts)