Use checkboxes to filter a table (Flask and SQLAlchemy)-CodePudding

I'm creating a CRUD flask web app for scheduling truck appointments. I know how to create, update, and delete appointments. What I'm having trouble figuring out is how to retrieve specific data from the database.

I'd like to have 3 checkboxes (one for each type of material that can be shipped) that can be used to filter the table data, and a submit button would be used to initiate the query.

The code below runs, but I'd like to be able to select multiple materials and display those in the table. I've tried using filter_by(or_), jquery, and arrays but haven't had any luck.

app.py:

app = Flask (__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///test.db'
db = SQLAlchemy(app)

class appts_db(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    carrier = db.Column(db.String(100))
    material = db.Column(db.String(10))
    pickup_date = db.Column(db.String(10))

    def __repr__(self):
        return '<Appt %r>' % self.id

@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)

and index.html:

<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>

<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>

CodePudding user response：

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)