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

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.

Solution

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 ...''')