Django-queryset join without foreignkey

Question

model.py

class Tdzien(models.Model):
  dziens = models.SmallIntegerField(primary_key=True, db_column='DZIENS')
  dzienrok = models.SmallIntegerField(unique=True, db_column='ROK')


class Tnogahist(models.Model):
  id_noga = models.ForeignKey(Tenerg, primary_key=True, db_column='ID_ENERG')
  dziens = models.SmallIntegerField(db_column='DZIENS')

What I want is to get id_noga where dzienrok=1234. I know that dziens should be

dziens = models.ForeignKey(Tdzien)

but it isn't and I can't change that. Normally I would use something like

Tnogahist.objects.filter(dziens__dzienrok=1234)

but I don't know how to join and filter those tables without foreignkey.

score 24 · Answer 1 · answered Oct 25 '13 at 12:57

24

No joins without a foreign key as far as I know, but you could use two queries:

Tnogahist.objects.filter(dziens__in=Tdzien.objects.filter(dzienrok=1234))

answered Oct 25 '13 at 12:57

RemcoGerlich

30,470
6
61
79

6

this will do one query with a subquery. – Florian Apr 23 '16 at 16:56
3

This is translated to an `IN` clause, which usually has a size limit. (~1000 items in most databases I'm familiar with) – Daniel Dror Nov 10 '16 at 15:45
1

@DanielDubovski that is not true https://stackoverflow.com/questions/1009706/postgresql-max-number-of-parameters-in-in-clause – jperelli Aug 15 '17 at 03:21
1

@jperelli you proved that postgres doesn't have a limit, that's just one case..https://stackoverflow.com/questions/4722220/sql-in-clause-1000-item-limit All I said is that this is something that people need to be aware of.. – Daniel Dror Aug 15 '17 at 07:33
1

@DanielDubovski The above should not create an item based `IN` clause, but a sub-select, i.e. the id list from `Tdzien.objects.filter(dzienrok=1234)` does not get materialized first. It generates SQL as the answer in your linked post suggest, which does not fall under the `IN` item limit – Arne Claassen May 28 '20 at 17:19

score 22 · Accepted Answer · answered Oct 25 '13 at 13:50

It's possible to join two tables by performing a raw sql query. But for this case it's quite nasty, so I recommend you to rewrite your models.py.

You can check how to do this here

It would be something like this:

from django.db import connection

def my_custom_sql(self):
    cursor = connection.cursor()    
    cursor.execute("select id_noga
                    from myapp_Tnogahist a
                    inner join myapp_Tdzien b on a.dziens=b.dziens
                    where b.dzienrok = 1234")
    row = cursor.fetchone()
    return row

score 7 · Answer 3 · answered Apr 02 '14 at 18:22

Could you do this with .extra? From https://docs.djangoproject.com/en/dev/ref/models/querysets/#extra:

where / tables

You can define explicit SQL WHERE clauses — perhaps to perform non-explicit joins — by using where. You can manually add tables to the SQL FROM clause by using tables.

score 6 · Answer 4 · answered Feb 25 '21 at 18:56

To provide a little more context around @paul-tomblin's answer,

It's worth mentioning that for the vast majority of django users; the best course of action is to implement a conventional foreign key. Django strongly recommends avoiding the use of extra() saying "use this method as a last resort". However, extra() is still preferable to raw queries using Manager.raw() or executing custom SQL directly using django.db.connection

Here's an example of how you would achieve this using django's .extra() method:

Tnogahist.objects.extra(
    tables = ['myapp_tdzien'],
    where = [
        'myapp_tnogahist.dziens=myapp_tdzien.dziens',
        'myapp_tdzien.dzienrok=%s',
        ],
    params = [1234],
    )

The primary appeal for using extra() over other approaches is that it plays nicely with the rest of django's queryset stack, like filter, exclude, defer, values, and slicing. So you can probably plug it in alongside traditional django query logic. For example: Tnogahist.objects.filter(...).extra(...).values('id_noga')[:10]

Django-queryset join without foreignkey

4 Answers4

Linked