Django JOIN query without foreign key

Just reposting some related answer, so everyone could see it. Taken from here: Most efficient way to use the django ORM when comparing elements from two lists

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:

1. 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.

2. 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:

1. You can enumerate a query set in Python using the built-in enumerate function:

   enumerate(Model1.objects.all())
   

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

The Django ForeignKey is different from SQL ForeignKey. Django ForeignKey just represent a relation, it can specify whether to use database constraints.

Try this:

request_url = models.ForeignKey(UserActivityLink, to_field='url_description', null=True, on_delete=models.SET_NULL, db_constraint=False)

Note that the db_constraint=False is required, without it Django will build a SQL like:

ALTER TABLE `user_activity` ADD CONSTRAINT `xxx` FOREIGN KEY (`request_url`) REFERENCES `user_activity_link` (`url_description`);" 

I met the same problem, after a lot of research, I found the above method.

Hope it helps.

No, there isn't an effective way unfortunately.

The .raw() is there for this exact thing. Even if it could it probably would be a lot slower than raw SQL.

There is a blogpost here detailing how to do it with query.join() but as they themselves point out. It's not best practice.