CODEWITHSUNDEEP
mysqldjangodjango-models
swe
swe

Reputation: 153

what is the equivalent ORM query in Django for sql join

I have two django models and both have no relation to each other but have JID in common(I have not made it foreign key):

class result(models.Model):
  rid = models.IntegerField(primary_key=True, db_column='RID') 
  jid = models.IntegerField(null=True, db_column='JID', blank=True)
  test_case = models.CharField(max_length=135, blank=True)

class job(models.Model):
  jid = models.IntegerField(primary_key = True, db_column='JID') 
  client_build = models.IntegerField(max_length=135,null=True, blank=True)

I want to achieve this sql query in ORM: SELECT * FROM result JOIN job ON job.JID = result.JID

Basically I want to join two tables and then perform a filter query on that table.

I am new to ORM and Django.

Upvotes: 0

Views: 1527

Answers (3)

Cyrille Ka
Cyrille Ka

Reputation: 15538

You can represent a Foreign Key in Django models by modifying like this you result class:

class result(models.Model):
  rid = models.IntegerField(primary_key=True, db_column='RID') 
  # jid = models.IntegerField(null=True, db_column='JID', blank=True)
  job = models.ForeignKey(job, db_column='JID', blank=True, null=True, related_name="results")
  test_case = models.CharField(max_length=135, blank=True)

(I've read somewhere you need to add both blank=True and null=True to make a foreign key optional in Django, you may try different options).

Now you can access the job of a result simply by writing:

myresult.job # assuming myresult is an instance of class result

With the parameter related_name="results", a new field will automatically be added to the class job by Django, so you will be able to write:

myjob.results

And obtain the results for the job myjob.

It does not mean it will necessarilly be fetched by Django ORM with a JOIN query (it will probably be another query instead), but the effect will be the same from your code's point of view (performance considerations aside).

You can find more information about models.ForeignKey in Django documentation.

Upvotes: 0

catherine
catherine

Reputation: 22808

jobs = job.objects.filter(jid__in=result.objects.values('jid').distinct()
   ).select_related()

Upvotes: 3

François Constant
François Constant

Reputation: 5496

I don't know how to do that in Django ORM but here are my 2 cents:

  • any ORM makes 99% of your queries super easy to write (without any SQL). For the 1% left, you've got 2 options: understand the core of the ORM and add custom code OR simply write pure SQL. I'd suggest you to write the SQL query for it.

  • if both table result and job have a JID, why won't you make it a foreign key? I find that odd.

  • a class name starts with an uppercase, class *R*esult, class *J*ob.

Upvotes: 0

Related Questions