CODEWITHSUNDEEP
djangodjango-rest-frameworkdjango-orm
Bergutov Ruslan
Bergutov Ruslan

Reputation: 157

Filtering order in django

I'm interested to filter table by django orm in specific order. Basically, it's simple search by two fields - name and description. I want to sort the result first by the "name" field concurrencies, and then by the "description" field concurrencies. Here is the example of raw sql:

WITH checkpoints AS (SELECT * FROM checkpoints_view WHERE ...) SELECT * FROM checkpoints WHERE name ILIKE '%KEYWORD%' UNION ALL SELECT * FROM checkpoints WHERE description ILIKE '%KEYWORD%' AND name NOT ILIKE '%KEYWORD%';

How can i generate sql like this in the django orm?

Thanks to all

P.S.: sorry for my english if i wasn't correct, i'm from not english-speaking country

class Checkpoint(models.Model, ReadOnlyModel):
    id = models.IntegerField(primary_key=True) 
    name = models.CharField(max_length=255, verbose_name='Наименование')
    code_number = models.CharField(max_length=255, verbose_name="Код остановки")
    description = models.CharField(max_length=255, verbose_name="Описание")
    coordinate = models.PointField(verbose_name="Координата")
    routes = ArrayField(models.IntegerField())
    dates = ArrayField(models.DateField()) 

    class Meta: 
        db_table = 'checkpoints_view' 
        managed = False

Almost all of you right, but i want to result was ordered at start by rows, which passes filter of name field, and then, after all this - rows, which passed filter of description field

Upvotes: 1

Views: 1748

Answers (3)

Eugene Morozov
Eugene Morozov

Reputation: 15806

I think your code can be written without CTEs:

Checkpoint.objects.filter(
    Q(name__icontains='keyword') |
    (~Q(name__icontains='keyword') & Q(description__icontains='keyword')))

Upvotes: 2

aliva
aliva

Reputation: 5720

results = Checkpoint.objects.filter(
    name__icontains='str_1',
) | Checkpoint.objects.filter(
    description__icontains='str_2',
).exclude(
    name__icontains='str_3',
)

Upvotes: 1

e4c5
e4c5

Reputation: 53734

AFAIK, the Django ORM does not use CTEs in the queries that it generates. That's because they have different behaviours across different RDBMS and not supported at all in mysql and sqlite.

So if you want to use a CTE in your query your only option is a raw query. Something like

queryset = MyModel.objects.raw('WITH checkpoints AS (SEL ....')

Upvotes: 1

Related Questions