Django 3.2 - querie multiple Models in view (SQL comparison)

Question

I'm quite new to django and have problems with the ORM and the view-queries. We have already migrated and synched some Models from our productive DB to the django DB, but I have problems with connecting two Models to each other.

e.g. models.py:

class SsdgSendung(models.Model):
    sdg_sdgid = models.CharField(primary_key=True, max_length=30)
    sdg_konz = models.CharField(max_length=12, blank=True, null=True)

    class Meta:
        db_table = 'ssdg_sendung'

class SsdaSdgadr(models.Model):
    sda_sdgid = models.ForeignKey(SsdgSendung, on_delete=models.CASCADE, blank=True, null=True)
    sda_satid = models.CharField(max_length=4, blank=True, null=True)

    class Meta:
        db_table = 'ssda_sdgadr'
        unique_together = (('sda_sdgid', 'sda_satid'),)

Sample Data

SsdgSendung:

sdg_sdgid = BL-1237781-BL-1
sdg_konz = 009874

SsdaSdgadr:

sdg_sdgid = BL-1237781-BL-1
sda_satid = IV
sdg_sdgid = BL-1237781-BL-1
sda_satid = CN

How should the correct "django"-query look for this equivalent SQL:

SELECT * 
FROM 
   SsdgSendung 
   inner join SsdaSdgadr on sdg_sdgid = sda_sdgid and sda_satid = 'IV' 
WHERE 
   sdg_konz = '1234'

I tried this, but I don't get any result on the template:

Sendungen = SsdgSendung.objects.filter(sdg_konz = current_user_konz).order_by('-sdg_datum').prefetch_related('sda_sdgid')

template

{% for la_item in SDG_Overview %}
<tr>
    <td>{{ la_item.sdg_sdgid }}</td> <!-- works well -->
    <td>{{ la_item.sda_satid }}</td> <!-- don't work -->
</tr>

score 1 · Accepted Answer · answered Dec 04 '21 at 19:01

1

Sendungen=SsdaSdgadr.objects.filter(sda_sdgid__sdg_konz=current_user_konz).order_by('-sdg_datum')

template

    {% for la_item in SDG_Overview %}
<tr>
    <td>{{ la_item.sdg_sdgid.sdg_sdgid }}</td> <!-- works well -->
    <td>{{ la_item.sda_satid }}</td> <!-- will work -->
</tr>

This should work Documentation

answered Dec 04 '21 at 19:01

Piyush Bali

103
6

So far I got it, in my interface-job we go always from the top → down: From this point of view the Master-Model would be "SsdgSendung.sdg_sdgid" and the related Model "SsdaSdgadr" From django view, it seems only to work from bottom to up. Like: SsdaSdgadr (which is Children from SsdgSendung) related to SsdgSendung Is this is this assumption correct? – Andreas Hauser Dec 04 '21 at 23:07
1

I am not able to understand your argument perfectly. But as you need sda_satid field you need an object of that table so yes from children you can fetch the parent. I don't know to way to fetch children from parents. But the above answer should work. please upvote if it did. – Piyush Bali Dec 05 '21 at 13:30
This solution works well, when the querie is build from child-model I used this complex-lookup to get the desired result: `Sendungen = SsdaSdgadr.objects.filter(Q(sda_sdgid__sdg_konz=current_user_konz)&Q(sda_satid="IV")).order_by('-sda_sdgid__sdg_datum')` – Andreas Hauser Dec 05 '21 at 17:19

Django 3.2 - querie multiple Models in view (SQL comparison)

Sample Data

1 Answers1