Django JOIN query without foreign key

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.

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.

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