I’m trying to filter by dates a data table from an SQLite database in Flask using ajax and javascript, but I’m having problems retrieving the filtered data into the HTML.
I think that the major issue is that the python backend is not receiving the dates that I’m sending from the frontend and so, is not filtering the data, that is JSON format.
Here is my database model:
class Payouts(db.Model):
payout_id = db.Column(db.Integer, primary_key=True)
batch_id = db.Column(db.Integer, db.ForeignKey('batch.batch_id'))
amount = db.Column(db.Float) #in cents
date = db.Column(db.Date)
transaction_id = db.Column(db.String(100))
batchs = db.relationship('Batch', lazy=True)
def to_dict(self):
return {
'payout_id': self.payout_id,
'batch_id': self.batch_id,
'amount': self.amount,
'date': self.date.strftime("%d/%m/%Y") ,
'transaction_id': self.transaction_id
}
Here is the code that gets all the data from the database:
@app.route('/admin/payouts/data', methods=['GET', 'POST'])
def payouts_data():
return {'data': [payout.to_dict() for payout in Payouts.query]}
Here is the code that must filter the data from the database
class dates_filter(FlaskForm):
start_date = DateField('start_date')
end_date = DateField('end_date')
@app.route("/admin/pay/range",methods=["POST","GET"])
def range():
form = dates_filter()
if form.validate_on_submit:
start_date = form.start_date.data
end_date = form.end_date.data
else:
start_date = datetime.strptime('1/11/2021', '%d/%m/%Y')
end_date = datetime.strptime('10/11/2021', '%d/%m/%Y')
return {'data': [payout.to_dict() for payout in Payouts.query.filter(Payouts.date.between(start_date, end_date))]}
Here is the page that displays the data table:
@app.route('/admin/payouts', methods=['GET', 'POST'])
def payouts():
form = dates_filter()
return render_template('admin/payouts.html,form = form)
here is the html code:
<head>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.12.1/jquery-ui.js"></script>
</head>
<br>
<div class="container">
<div class="row">
<div class="form-group">
<label class="col-md-12">Start Date</label>
<div class="col-md-12">
{{form.start_date}}
</div>
</div>
<div class="form-group">
<label class="col-md-12"> End Date</label>
<div class="col-md-12">
{{form.end_date}}
</div>
</div>
<div class="form-group">
<div class="col-sm-12">
<br>
<input type="button" name="range" id="range" value="Range" class="btn btn-success" onclick="change()" style="background-color: #4a88f4 !important;"/>
</div>
</div>
</div>
<div class="row">
<div class="col-md-12 mb-5">
<div class="card h-100" style="background-color:rgba(242, 242, 242, 1); border: none;">
<div class="card-body pt-0">
<div class="mx-n3" style="max-height: 30em">
<div class="table-responsive" style="width: 100%; size-adjust: auto; max-height: 33em; overflow-y: scroll;">
<table class="table table-striped table-bordered" id="data">
<thead>
<tr style="text-align: center; position: sticky; position: -webkit-sticky; top: 0; background-color: white;"">
<th class="table_cell">
Actions</th>
<th class="table_cell">
ID</th>
<th class="table_cell">
Batch</th>
<th class="table_cell">
Date</th>
<th class="table_cell">
Amount BTC</th>
<th class="table_cell">
Transaction ID</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
And here is the javascript code, which is divided into two parts, the first retrieve all the data from (without filtering), and the second part, cleans the table section and must retrieve the data filtered by dates, but whenever I click the button to filter, the query just stays empty.
<script type="text/javascript">
$(document).ready(function () {
$.ajax({
url: "{{ url_for('payouts_data')}}",
dataType: 'json',
type: 'get',
cache: true,
success: function (data) {
var event_data = '';
$.each(data.data, function (index, value) {
event_data += '<tr>';
event_data += '<td style="align-content: center; width: 100px; min-width: 100px;>
<i class="fas"></i>
<a href = "/admin/payouts/' + value.payout_id + ' " type="submit" class="btn btn-primary btn-sm" style="background-color: #4a88f4 !important;">
<i class="fas fa-eye"></i></a>
<a href = "/admin/payouts/' + value.payout_id + ' " type="submit" class="btn btn-primary btn-sm" style="background-color: #4a88f4 !important;">
<i class="fas fa-edit"></i></a>
</td>';
event_data += '<td style="text-align:center;">' + value.payout_id + '</td>';
event_data += '<td style="text-align:center;">' + value.batch_id + '</td>';
event_data += '<td>' + value.date + '</td>';
event_data += '<td>' + value.amount + '</td>';
event_data += '<td>' + value.transaction_id + '</td>';
event_data += '</tr>';
});
$("#data").append(event_data);
},
error: function (d) {
alert("404. Please wait until the File is Loaded.");
}
});
change = function () {
$("#data").empty();
$.ajax({
url: "{{ url_for('range')}}",
dataType: 'json',
type: 'get',
cache: true,
success: function (data) {
var event_data = '';
$.each(data.data, function (index, value) {
event_data += '<tr>';
event_data += '<td style="align-content: center; width: 100px; min-width: 100px;>
<i class="fas"></i>
<a href = "/admin/payouts/' + value.payout_id + ' " type="submit" class="btn btn-primary btn-sm" style="background-color: #4a88f4 !important;">
<i class="fas fa-eye"></i></a>
<a href = "/admin/payouts/' + value.payout_id + ' " type="submit" class="btn btn-primary btn-sm" style="background-color: #4a88f4 !important;">
<i class="fas fa-edit"></i></a>
</td>';
event_data += '<td style="text-align:center;">' + value.payout_id + '</td>';
event_data += '<td style="text-align:center;">' + value.batch_id + '</td>';
event_data += '<td>' + value.date + '</td>';
event_data += '<td>' + value.amount + '</td>';
event_data += '<td>' + value.transaction_id + '</td>';
event_data += '</tr>';
});
$("#data").append(event_data);
},
error: function (d) {
alert("404. Please wait until the File is Loaded.");
}
});
}
});
</script>
Attached there are some images of the front, before and after clicking the button.
I hope that someone can help me!! regards.