I have two integer columns in my DB times_seen
and times_answered_correctly
.
Now I want to select all elements, which have been seen more than 100 times and sort them by a math operation (times_answered_correctly / times_seen
).
I am using following code, which works and does not give any errors:
top_10_hardest = db_session.query(QuizItems).filter(QuizItems.times_seen >= 100).order_by(QuizItems.times_answered_correctly / QuizItems.times_seen).all()
I also tried .order_by(asc(QuizItems.times_answered_correctly / QuizItems.times_seen))
and desc()
. The results change, but it is still ordered randomly.
{% for i in top_10_hardest %}
<p> {{ i.times_answered_correctly/i.times_seen }} </p>
{% endfor %}
gives:
0.9858490566037735
0.8082788671023965
0.7952941176470588
0.9202127659574468
0.7591623036649214
0.9950980392156863
0.9907621247113164
0.9905660377358491
0.39420935412026725
0.9931506849315068
Why it does not work?
The problem was that the columns were
Integer
columns and eventhough there were no errors in the.order_by(asc(QuizItems.times_answered_correctly / QuizItems.times_seen)
all values have been rounded to an integer, which is1
or0
In the view function I displayed the results as:
That was the first time I have done correct math, BUT before the items were ordered, remember the order function did not work because it treated the elements as integers.
SOLUTION:
I fixed the issue with the
cast()
function. I casted theInteger
elements toFloat
in theorder_by()
function: