Geek Answers Handbook

Django model query for nearest integer match

I have some Django model objects with different rating field values:

puzzles_rating = [0, 123, 245, 398, 412, 445, 556, 654, 875, 1000]
    for rating in puzzles_rating:
        puzzle = Puzzle(rating=rating)
        puzzle.save()

Now, for a user_rating = 500, I want to choose a puzzle with the closest ranking match. In the above case, it should be puzzle number 6 with a rating of 445.

The problem is that I cannot just do:

puzzle = Puzzle.objects.filter(rating__lte=user_rating).order_by('-rating')[0]

since, as a rule, my closest rating may be greater than the target rating.

Is there a convenient way to request the closest match from both directions?

+4
source share
2 answers

You can use the method extra:

puzzle = Puzzle.objects.extra(select={
    'abs_diff': 'ABS(`rating` - %s)',
}, select_params=(rating,)).order_by('abs_diff').first()

Django 1.8, raw SQL, Func:

from django.db.models import Func, F

    puzzle = Puzzle.objects.annotate(abs_diff=Func(F('rating') - rating, function='ABS')).order_by('abs_diff').first()
+8

Puzzle Python, :

# Note, be sure to check that puzzle_lower and puzzle_higher are not None
puzzle_lower = Puzzle.objects.filter(rating__lte=user_rating).order_by('-rating').first()
puzzle_higher = Puzzle.objects.filter(rating__gte=user_rating).order_by('rating').first()

# Note that in a tie, this chooses the lower rated puzzle
if (puzzle_higher.rating - user_rating) < abs(puzzle_lower.rating - user_rating):
    puzzle = puzzle_higher
else:
    puzzle = puzzle_lower
+2

More articles:


All Articles