1

All I want is to write the following SQL query in Python Django syntax. I researched a lot but could not get the result I want. Could you please help me.

    SELECT * FROM thread
    LEFT JOIN user
    ON (thread.user1_id = user.id OR thread.user2_id = user.id)
    WHERE user.id = 9

My model:

 class Thread(models.Model):
        last_message = models.TextField(max_length=100)
        user1_id = models.IntegerField()
        user2_id = models.IntegerField()
EducateYourself
  • 971
  • 2
  • 13
  • 32
  • Possible duplicate of [Django Filters - or?](http://stackoverflow.com/questions/739776/django-filters-or) – Sayse Apr 29 '16 at 11:17
  • the question is about join – EducateYourself Apr 29 '16 at 11:19
  • The question is unclear, you should show what you have tried/researched so far to try to solve it. As well as the relevant parts of your models. – Sayse Apr 29 '16 at 11:20
  • the question is clear and the answer will help thousands of beginners in the future. If you are an experienced Django developer, you can help me to find the solution, else you can wait till other developers will do it. Thanks. – EducateYourself Apr 29 '16 at 11:24
  • 1
    @EducateYourself Can you post your user model and why you are not using straight-forward `ForeignKey` fields for `user1` and `user2`? – user2390182 Apr 29 '16 at 11:37

1 Answers1

1

The exact query cannot be created using the Django ORM without resorting to some raw SQL. The problem is the OR in the join condition.

But, since your not selecting any rows from the user table, you can create a different query with the same results.

Start by defining your models using ForeignKeys.

from django.db import models
from django.contrib.auth.models import User

class Thread(models.Model):
    last_message = models.TextField(max_length=100)
    user1 = models.ForeignKey(User)
    user2 = models.ForeignKey(User)

    class Meta:
        db_table = 'thread'  # otherwise it will be: appname_thread

Then you can preform the query.

from django.db.models import Q

threads = Thread.objects.filter(Q(user1__id=9) | Q(user2__id=9))

The resulting SQL will be something like this:

SELECT * FROM thread
LEFT JOIN user AS user1
ON (thread.user1_id = user1.id)
LEFT JOIN user AS user2
ON (thread.user2_id = user2.id)
WHERE (user1.id = 9 OR user2.id = 9)

You can improve this by avoiding the join altogether.

# note the single underscore!
threads = Thread.objects.filter(Q(user1_id=9) | Q(user2_id=9))

Generating the following SQL.

SELECT * FROM thread
WHERE (user1_id = 9 OR user2_id = 9)

If you need to also obtain the user instances in the same query, in which case you really need the join, you have two options.

  1. Either use the first query that includes joins and afterwards select the correct user based in the id in Python.

    for thread in threads:
    if thread.user1.id == 9:
        user = thread.user1
    else:
        user = thread.user2

  2. Or use raw SQL to get the exact query you want. In this case, you can use the models without the ForeignKeys as you defined them.

    threads = Thread.objects.raw('''
           SELECT *, user.id AS user_id, user.name as user_name FROM thread
           LEFT JOIN user
           ON (thread.user1_id = user.id OR thread.user2_id = user.id)
           WHERE user.id = 9''')
for thread in threads:
    # threads use the ORM
    thread.id
    # user fields are extra attributes
    thread.user_id
    thread.user_name
Alvra
  • 353
  • 2
  • 10