Django: How to join columns without foreign key with ORM

user_10462490 0
Viewed 4421

I´m new to Django and Python and I can´t get a solution to this problem, so any help is appreciated!

I´m trying to join Database columns in Django, that do have a corresponding value but that value is not a foreign key.

Let´s say my models are:

class Order(models.Model):
   order_id = models.AutoField(primary_key=True)
   oOrder_number = models.CharField(max_length=50)
   ...

and

class Shipment(models.Model):
  dShipment_id = models.AutoField(primary_key=True)
  dTo_order = models.CharField(max_length=10)
  ...

What I do know is, that I can use Djangos select_related command to get the values from tables, that are connected with a foreign key.

But here is my problem: I can not use a foreign key in my model here, for reasons that are rooted in a third party software that is pushing the Data over my API. That software is incapable of storing a return value from the created order and use it as an identifier for the shipment (...).

So finally here is my question: Is it possible to do some join with the identical fields oOrder_number and dTo_order ?

My desired result is, that I can select a certain Order and get all connected shipments from the Shipment table, that will all have the same dTo_order.

I know how to do this in Larvel and MySQL Querie, but from what I heard it´s not "Djangoish" to use raw querie... .

Any Ideas on that?

Thanks for reading! If i left something out, I will be glad to provide all necessary information! :)

Edit: I know that I could try a workaround to get the Foreign of the order for the shipment by searching the Order table for the To_order field and get the primary key of it. However I am curious if there is a more elegant solution for this in Django.

Update:

Here is what I imagine.

  • I fetch order from the DB like orders=Order.objects.all().
  • I do some magic so that for every order object, there will be an array or what ever with all the shipments data that belong to the order

  • I pass the data to my template. Here I woul then like to do something like:

    {% for order in orders %}

    {{Order.oOrder_no}}

    {% for shipment in order.shipments %}

    {{shipment.trackingno}}

    {% endfor %}

    {% endfor %}

mysql django-models django python
1 Answers

You can use a Subquery expression with OuterRef:

from django.db.models import OuterRef, Subquery
shipments = Shipment.objects.filter(dTo_order=OuterRef('oOrder_number'))
Order.objects.annotate(shipments=Subquery(shipments.values('dShipment_id', 'trackingno')))
user_68909120
Related