Most efficient way to use the django ORM when comparing elements from two lists

Question

model_one_list = Model1.objects.all()
model_two_list = Model2.objects.filter(...).order_by(..)

for model in model_one_list:
    for model2 in model_two_list:
        if model.field == model2.field:
            another_list.append(model)

Some tips online for the django ORM specifically mentioned to not do something this as it uses too much memory and hurts performance, the docs recommend to stick with the ORM as much as possible.

I tried to solve the issue above with this:

Model1.objects.extra(
    where={
        'field = app_model2.field' 
        # Not sure if this works, I think the app_model2 may be wrong idk if i
        # can access that
    },
)

But for something like this, i'm not quite sure on how to handle it.

Also a final question that is a bit related. If I have a queryset that needs to be ranked simply by the count of the ordering, whats the recommended way to do this without iteration?

Model1.objects.order_by('something').extra(select={'rank':
"how do i get the rank in mysql??? like while its
 iterating, is this even possible"})

Thanks for the help.

score 6 · Accepted Answer · edited May 23 '17 at 12:14

First problem: joining unrelated models

I'm assuming that your Model1 and Model2 are not related, otherwise you'd be able to use Django's related objects interface. Here are two approaches you could take:

Use extra and a SQL subquery:
```
Model1.objects.extra(where = ['field in (SELECT field from myapp_model2 WHERE ...)'])
```
Subqueries are not handled very efficiently in some databases (notably MySQL) so this is probably not as good as #2 below.

Use a raw SQL query:

Model1.objects.raw('''SELECT * from myapp_model1
                   INNER JOIN myapp_model2
                   ON myapp_model1.field = myapp_model2.field
                   AND ...''')

Second problem: enumerating the result

Two approaches:

You can enumerate a query set in Python using the built-in enumerate function:
```
enumerate(Model1.objects.all())
```

You can use the technique described in this answer to do the enumeration in MySQL. Something like this:

Model1.objects.raw('''SELECT *, @row := @row + 1 AS row
                   FROM myapp_model1
                   JOIN (SELECT @row := 0) rowtable
                   INNER JOIN myapp_model2
                   ON myapp_model1.field = myapp_model2.field
                   AND ...''')

suppose a list of entries displayed in an ordered list, id like each to have a rank based on that ordering, this is purely for display in the template. like entry 1, entry 2, ... etc. — Lucas Ou-Yang, Oct 01 '12 at 23:07

Most efficient way to use the django ORM when comparing elements from two lists

1 Answers1

First problem: joining unrelated models

Second problem: enumerating the result

Linked