Python sqlalchemy order_by math operation (divide) does not order results

Question

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?

Answer 1

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 is 1 or 0

In the view function I displayed the results as:

<p> {{ i.times_answered_correctly/i.times_seen }} </p>

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 the Integer elements to Float in the order_by() function:

db_session.query(QuizItems).filter(QuizItems.times_seen >= 100).order_by(cast(QuizItems.times_answered_correctly, Float) / cast(QuizItems.times_seen, Float)).all()