SQL to DjangoORM query I've model,

class ModelA(models.Model):
    device_id = models.IntegerField()
    deal_sent = ArrayField(models.IntegerField)


For given device_id, we can have a list of repeating deal_sent. For eg:- Consider the following table

device_id         deals_sent
    1       [4,5,1]
    2       [1,2,3,4,2,3,4,3,4,5,1]
    3       [1,4,2,3,4,3,4,5,1]
    4       [1,4,3,4,5,1]
    5       [4,5]
    6       [1,3,4,2,3,4,3,4,5,1]


I want to get top N device_id, who have minimum count of 1 in deal_sent ArrayField. For eg:- If I want top 2 device_id who have minimum count of 1 in deals_sent, then device_id 1 and 5 will be returned since the count of 1 in deals_sent was least in device_id 1 and 5.

I've followed this question which seems to be a close solution what I'M expecting. 

with elements (element) as (
   select unnest(ARRAY['a','b','c','a','a'])
)
select count(*)
from elements
where element = 'a';


How can I convert the above query to DjangoORM query? OR What can the alternate perfect query at the DB level itself without using a for-loop at the application level?
One way to do the query would be:

with norm (device_id, element) as (
    select device_id, unnest(deals_sent) from modelA
)
select device_id
from norm·
group by device_id
order by count(element = 1)
limit 2;


The same query cannot be produced by Django ORM. You can only use raw. However there are some options to still keep the benefits of the ORM.

1) Using a view

You can create a VIEW and an unmanaged model in your django app pointing to that view.

CREATE VIEW modelA_norm AS (
   select device_id, unnest(deals_sent) as element from modelA
);


Then in Django:

class ModelA_norm(models.Model):
    device_id = models.IntegerField()
    element = models.IntegerField()

     class Meta:
         managed = False


The query would look like this:

ModelA_norm.objects.extra(
    select={'counter': 'COUNT(element=%s)'}, 
    select_params=(1, )
).order_by('counter')[:2]


2) Using subselect in where

ModelA.objects.extra(
    where=[
        'device_id IN ( ... here comes the select to get the device ids ...)'
    ]
)


Be aware in this case you will get the results back in arbitrary order not necessary the order of ids produced by the subselect.

3) Run the select directly

You can get the ids to a list from the database and use them to query from your model.

from django.db import connection
_c = connection.cursor() 
_c.execute('... the query above with placeholders ...', [1])
_ids = [_d[0] for _d in _c.fetchall()]
objects = modalA.objects.in_bulk(_ids)
sorted_objects = [objects[id] for id in _ids]




I tend to use both 1) and 3) depending on the actual situation. 

1) for situation where I can either inherit the model from the original or I don't actually need the original model I can just have a new one.

3) when I need the original model. I tend to put the queries into files in SQL directory in the app (just like templates). So I have a clear indication that I'm doing something database dependent and I can easily switch/rewrite them later if necessary.

I hope this helps.