CODEWITHSUNDEEP
pythonmysqldjangodjango-models
errorinpersona
errorinpersona

Reputation: 410

Django: How to join columns without foreign key with ORM

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.

Upvotes: 6

Views: 7332

Answers (2)

Kirolos Nabil
Kirolos Nabil

Reputation: 1

You can pass the data to your template using this without nested loop anymore and this way will reduce processing time:

Using a Subquery expression with OuterRef:

from django.db.models import OuterRef, Subquery

shipments = Shipment.objects.filter(dTo_order=OuterRef('oOrder_number')).values('trackingno') 
orders = Order.objects.annotate(shipments=Subquery(shipments.values('trackingno')[:1]))

Then you can use single for loop like:

{% for order in orders %}

{{Order.oOrder_no}}

{{Order.trackingno}}

{% endfor %}

Upvotes: 0

blhsing
blhsing

Reputation: 106768

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')))

Upvotes: 5

Related Questions