Changing mySQL Query to Django

Question

I am having difficulties changing the following MySQL Query to a Django Query

SELECT * FROM dashboard_db.dashboard_system ds 
JOIN dashboard_db.dashboard_userpreference up 
ON ds.id = up.system_id
WHERE username = "<user>"

Here are the models in my Django application

class System(models.Model):
   system_name = models.CharField(max_length = 40)
   LOCATION = (('0', 'Inernal'),('1','External'))
   location = models.CharField(max_length=1, choices=LOCATION)

   def __str__(self):
      return self.system_name

class UserPreference(models.Model):
   username = models.CharField(max_length = 40)
   system = models.ForeignKey(System, on_delete = models.CASCADE)

   def __str__(self):
      return self.username

I have been trying to use unions and a few other options. I am aware that Performing a raw SQL query is an option. However I was wondering if it is possible to perform the join using a Django styled query.

So I assume you want the Systems for which the corresponding UserPreference username is a given username? — willeM_ Van Onsem
– willeM_ Van Onsem, Commented May 29, 2018 at 19:33

willeM_ Van Onsem · Accepted Answer · 2018-05-29 19:39:10Z

2

I assume you want the Systems for which at least one of the corresponding UserPreferences username is a given <user>.

We can do this by querying through the reversed queryset, by using the name of that model, and two consecutive underscores:

System.objects.filter(userpreference__username='<user>')

where you of course replace '<user>' with the string that matches the username (or a Python expression that "calculates" the username you wish to obtain).

This will produce a QuerySet that will contain all Systems for which the condition holds. Note however that some Systems might occur multiple times in case multiple UserProfiles exist with the given username that link to that System (perhaps that is here impossible, but since username is not unique=True in your models, at least from a theoretical perspective, that is possible).

In case you want a System to occur at most once, you can use .distinct() on the QuerySet:

# systems occur *at most* once
System.objects.filter(userpreference__username='<user>').distinct()

edited May 29, 2018 at 19:39

answered May 29, 2018 at 19:35

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

willeM_ Van Onsem Over a year ago

@Haytes: I added a note about the fact that some Systems migt occur multiple times, and how to avoid that (that might perhaps be of interest to you).

Haytes Over a year ago

That was also helpful thanks again for the fast responses.

Collectives™ on Stack Overflow

Changing mySQL Query to Django

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related