CODEWITHSUNDEEP
djangodjango-modelsdjango-viewsdjango-ormdjango-database
Matvii Kurdiukov
Matvii Kurdiukov

Reputation: 43

Django ORM order by filters

Help me please with my problem. I want to get objects sorted first by one filter and then by another filter.

How can I get objects with this ordering in 1 query to the DB (need for pagination)?

This example shows queryset without ordering:

rooms = Room.objects.filter(Q(name__icontains=search) | Q(owner__username__icontains=search))

I have the room model:

models.py

from django.db import models
from django.contrib.auth.models import User

class Room(models.Model):
    name = models.CharField(max_length=150)
    owner = models.ForeignKey(User, on_delete=models.CASCADE)

views.py (my bad code)

class RoomListView(ListAPIView):
    def get_queryset(self):
        search = 'test' #  for example
        rooms1 = Room.objects.filter(owner__username__icontains=search)
        rooms2 = Room.objects.filter(name__icontains=search)
        return list(rooms1) + list(rooms2)

The wrong result: search = "test"

[
  {
    "id":1,
    "name":"test",
    "owner":{
      "username":"user1"
    }
  },
  {
    "id":2,
    "name":"room1",
    "owner":{
      "username":"test"
    }
  },
  {
    "id":3,
    "name":"test2",
    "owner":{
      "username":"user2"
    }
  }
]

The correct result: search = "test"

[
  {
    "id":2,
    "name":"room1",
    "owner":{
      "username":"test"
    }
  },
  {
    "id":1,
    "name":"test",
    "owner":{
      "username":"user1"
    }
  },
  {
    "id":3,
    "name":"test2",
    "owner":{
      "username":"user2"
    }
  }
]

How can I get objects with this ordering in 1 query to the DB (need for pagination)?

Upvotes: 2

Views: 2195

Answers (3)

Борис Алексеев
Борис Алексеев

Reputation: 31

Use CASE WHEN expression and mark correct filter condition by integer. Pseudocode: case when username = test then 1 when room = test then 2 ... END "CASECOL" order by "CASECOL" ASC

Upvotes: 0

Chandu Arepalli
Chandu Arepalli

Reputation: 101

  • You can sort the columns by using order_by()

  • Example

    # Sorting columns with id, name, username in dynamically 
order_by_list = []
# sort by name
order_by_list.append('name')
# And second username sort along with name
order_by_list.append('owner__username')

    Room.objects.filter(.....).order_by(*order_by_list)

  • Here, we can append the column names dynamically to order_by_list

Upvotes: 0

Chandu Arepalli
Chandu Arepalli

Reputation: 101

As I understand you want to filter and order the rows with multi columns

For single column sorting

rooms = Room.objects.filter(Q(name__icontains=search) | Q(owner__username__icontains=search)).order_by('name')

For multi columns sorting

Here, we can order the columns with owner_username and same owner_username rows will be order_by with name

rooms = Room.objects.filter(Q(name__icontains=search) | Q(owner__username__icontains=search)).order_by('owner__username', 'name')

Note

  • You can sort the columns by using order_by()
  • Example 
    # Sorting order will in the order of column_1, column_2, column_3
Room.objects.filter(.....).order_by('column_1', 'column_2', 'column_3)
  • You can change the order of columns according to the requirements

Upvotes: 2

Related Questions