How to show fields from different table through foreign key and natural key in django query statement

Viewed 418

In mysql if you want to get some fields in different tables the query qould be like this

`select tab1.field, tab2.field1, tab3.fieldA from CustomerOrder tab1, Food tab2, table3 tab3, table4 tab4 where tab1.id=tab2.tab1_id and tab2.id=tab4.tab3_id and tab3.id=tab4.tab3_idx`

This query would help you get the field stated above. I have tried to do the same thing in django but it is giving me the number instead of the field_name it e.g. instead of Burrito for tab2.field1 it is sending me id number 15

CustomerOrder is table1 Food is table2 CustomerAddress is table3

CustomerOrder is my entry point

CustomerOrder.objects.filter(customer=customername)

These are my models

class CustomerOrderManager(models.Manager):
    def get_by_natural_key(self, customer, food, customeraddress):
        return self.get( customer=customer, food=food, customeraddress=customeraddress, ) 
    
#this contains the subject a students registered for in a specific customeraddress
class CustomerOrder(models.Model):
    customer  = models.ForeignKey(User, on_delete=models.CASCADE)
    food = models.ForeignKey(Food, on_delete=models.CASCADE, null=True)
    customeraddress = models.ForeignKey(CustomerAddress, on_delete=models.CASCADE)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    
    objects = CustomerOrderManager()
    class Meta:
        unique_together = [['customer','food','customeraddress']]
        
    #**** when i use this it prevents the pk or id from showing when i save a customerorder
    def natural_key(self):
        return (self.customer, self.food, self.customeraddress)

        

class FoodManager(models.Manager):
    def get_by_natural_key(self, food_type, food_name, description):
        return self.get(food_type=food_type, food_name=food_name, 
                    description=description)
class Food(models.Model):
    food_type = models.CharField(max_length=100, null=True)
    food_name = models.CharField(max_length=100, null=True)
    description = models.TextField(null=True)
    created_by = models.ForeignKey(User, on_delete=models.CASCADE, null=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    
    objects = FoodManager()
    class Meta:
        unique_together = [['food_type', 'food_name', 'description']]
    
    def natural_key(self):
        return (self.food_type, self.food_name, self.description)
    

class CustomerManager(models.Manager):
    def get_by_natural_key(self,customer_name):
        return self.get(customer_name=customer_name)

class CustomerAddress(models.Model):
    food_type  = models.CharField(max_length=300, null=True)
    customer_name  = models.CharField(max_length=300, null=True)
    order_date = models.DateField(null=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    
    objects = CustomerManager()
    class Meta:
        unique_together = [['customer_name']]
    
    def natural_key(self):
        return (self.customer_name)
1 Answers

I think this is what you are trying to do?

It will give you all the fields of CustomerOrder plus two additional fields named 'customer_name' and 'food_name'. PS: make sure you import F. from django.db.models import F

CustomerOrder.objects.filter(customer=customername).all().annotate(customer_name=F('customer__name'), food_name=F('food__food_name'))

I'm not sure about customer_address, I dont see any 'address' field in your models. You can traverse it using double underscores '__' from FK field in your calling model (CustomerOrder in this example)

Related