Q:

Query next smaller and next bigger instance of model in one queryset in Django

For example I have a following model:
class Example(models.model):
    number = PositiveSmallIntegerField()

I have 10 model instances saved in DB  with number field like:
[1, 2, 5, 8,  4, 75, 11, 6, 55, 18]
And I would like to query 2 model instances with next smaller and next bigger number field to my exact instance.
Example. If I have an instance with number 11, than next lower one would be 8 and greater would be 18.
Is it possible to query these 2 closest instances by using Django ORM in one queryset?

A:

You can obtain these with:
qs1 = Example.objects.filter(
    number__gt=my_number
).order_by('number')[:1]
qs2 = Example.objects.filter(
    number__lt=my_number
).order_by('-number')[:1]

qs = qs1 | qs2
Since querysets are lazy, qs1 and qs2 will not be evaluated if you do not "consume" these. So if you work with qs, then this queryset will contain two, one or no elements (depending on whether there is a next and previous). If there is a next and previous, then both will be in the queryset qs.
This will make a query that looks like:
SELECT example.*
FROM example
WHERE example.id IN (
       SELECT U0.id FROM example U0
       WHERE U0.number > my_number
       ORDER BY U0.number ASC
       LIMIT 1
   )
   OR example.id IN (
       SELECT U0.id
       FROM example U0
       WHERE U0.number < my_number
       ORDER BY U0.number DESC
       LIMIT 1
   )

we can also use .union instead:
qs1 = Example.objects.filter(
    number__gt=my_number
).order_by('number')[:1]
qs2 = Example.objects.filter(
    number__lt=my_number
).order_by('-number')[:1]

qs = qs1.union(qs2)
Then the query looks like:
(
    SELECT example.*
    FROM example
    WHERE example.number > 5
    ORDER BY example.number ASC
    LIMIT 1
) UNION (
    SELECT example.*
    FROM example
    WHERE example.number < 5
    ORDER BY example.number DESC
    LIMIT 1
)

