How to build up a nested query using Django's Database API

Question

I need to create a queryset in Django of the following PostgreSQL statement.

SELECT * FROM 
(
SELECT DISTINCT ON("atech_cars".
    "model")
"atech_cars".
"make", "atech_cars".
"model", "atech_cars".
"trim"
FROM "atech_cars"
WHERE(UPPER("atech_cars".
        "make"::text) = UPPER('Ford') AND "atech_cars".
    "model"
    IN(SELECT U0.
        "char_value"
        AS Col1 FROM "clf_atech_filter_cache"
        U0 WHERE U0.
        "filter_id" = 7))
) Sub 
ORDER BY 
CASE WHEN ("model" = 'xD') THEN 0 
WHEN ("model" = 'Cayenne') THEN 1 
WHEN ("model" = 'Elantra') THEN 297 ELSE NULL END ASC

This is the query I want to generate at the end with queryset.

I've already created a query set of the following statement.

SELECT DISTINCT ON("atech_cars".
    "model")
"atech_cars".
"make", "atech_cars".
"model", "atech_cars".
"trim"
FROM "atech_cars"
WHERE(UPPER("atech_cars".
        "make"::text) = UPPER('Ford') AND "atech_cars".
    "model"
    IN(SELECT U0.
        "char_value"
        AS Col1 FROM "clf_atech_filter_cache"
        U0 WHERE U0.
        "filter_id" = 7))

so eventually, I'm looking for queryset for the following statement.

SELECT * FROM 
(
<Queryset>
) Sub 
ORDER BY 
CASE WHEN ("model" = 'xD') THEN 0 
WHEN ("model" = 'Cayenne') THEN 1 
WHEN ("model" = 'Elantra') THEN 297 ELSE NULL END ASC

Here is the django queryset I've created, but it's not working.

class ATechCarModelFilter(Filter):

    def filter(self, qs, value):
        # trim filter_id = 9 in atech_filter
        trims = ClfCacheModel.objects.filter(filter_id = 9).values_list('char_value', flat=True).order_by("counter")
        preserved = Case(*[When(trim=trim, then=pos) for pos, trim in enumerate(trims)])
        return qs.filter(model__iexact = value).filter(trim__in=trims).order_by(preserved)

here, qs refers to queryset of BModel

How do I build on the query object I already have?

What am I doing wrong?

Here is the models as requested.

class BModal(models.Model):
    char_value = models.CharField(max_length=2048, blank=True, null=True)
    counter = models.IntegerField()

    class Meta:
        managed = False
        db_table = 'filter_cache'

class AModel(models.Model):
    make = models.CharField(max_length=200, primary_key=True)
    model = models.CharField(max_length=200, primary_key=True)
    trim = models.CharField(max_length=200)

    class Meta:
        managed = False
        db_table = 'amodel'

Here is what I want.

1) AModel and BModel is not related, right now.

2) Get the list of models from AModel table and then based on the order of that list, I wanna order in the order of list in BModel.

3) After that, I wanna remove duplicated items with distint("model")

Updated ATechCarMakeFilter() with annotate:

class ATechCarMakeFilter(Filter):
    def filter(self, qs, value):
      # model filter_id = 7 in atech_filter
      models = ClfCacheModel.objects.filter(filter_id = 7).values_list('char_value', flat=True).order_by("counter")
      preserved = Case(*[When(model=model, then=pos) for pos, model in enumerate(models)], default=None, output_field=IntegerField())
      qs = qs.filter(make__iexact = value).filter(model__in=models).distinct("model")        
      return qs.annotate(ordering=preserved).order_by("-ordering")

Welcome to SO. I made a few edits to your question, hopefully they were helpful. Overall, though, your question was great b/c you listed in good detail everything you were trying to do. If it were me, I would try a much simpler query to see about getting this right. If you try that simpler query and still can't get nested queries to work in Django, you can edit your own question to put in that simpler query and maybe get more responses from the Django community. I'm not a Django user myself so I won't be any direct help. If you find the answer, you can answer your own question and accept it. — WattsInABox
– WattsInABox, Commented May 31, 2017 at 17:41
The Django ORM is there to create SQL from OOP models and their relations. So, SQL should be the result, not the starting point. In order to create the right ORM query, please provide your models and tell us what you want to get out of them. — Klaus D.
– Klaus D., Commented May 31, 2017 at 20:04
Thanks for your support @KlausD. I've updated the description with more details along with models. — Patrick Blind
– Patrick Blind, Commented May 31, 2017 at 20:13
Sorry, but I still have no idea what you are trying to archive. — Klaus D.
– Klaus D., Commented May 31, 2017 at 20:20

Brobin · Accepted Answer · 2017-06-05 15:43:06Z

1

Okay, I think I understand what you have going on here. You can annotate your queryset with the case statement you are looking for and then order by that

from django.db.models import Count, Case, When, IntegerField

class ATechCarModelFilter(Filter):
     def filter(self, qs, value):
        qs = ... 
        ...  # previous filters
        qs.annotate(
            ordering=Case(
                When(model='xD', then=0),
                When(model='Cayenne', then=1),
                When(model='Elantra', then=297),
                default=None,
                output_field=IntegerField()
            )
        ).order_by('-ordering')

edited Jun 5, 2017 at 15:43

answered May 31, 2017 at 21:53

Brobin

3,3362 gold badges21 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Patrick Blind Over a year ago

I've updated ATechCarModelFilter as following with annotate. NOTE: I've updated the description with updated ATechCarModelFilter But I've got the following error message. Cannot resolve expression type, unknown output_field. Thanks

Patrick Blind Over a year ago

I've updated the filter in ATechCarMakeFilter as you asked but got the same error. Updated the description.

Collectives™ on Stack Overflow

How to build up a nested query using Django's Database API

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related