Django query certain attribute via foreign key

Question

I want to query certain data from a foreign key class. Is there a way to make it directly in the query?

I want to do the following query:

plant_list = Plant.objects.filter(plant_product = ikey).values('plant_number', 'plant_name', 'plant_city', 'plant_country', 'plant_product')

My Result is:

{'plant_number': '0001', 'plant_name': 'HoP1', 'plant_city': 3, 'plant_country': 1, 'plant_product': 1}

Now, for example at plant_city. I don´t want to have the ID I want to have the attribute city_name of the model City, which is the Foreign Key.

So here ist the result I want:

{'plant_number': '0001', 'plant_name': 'HoP1', 'plant_city': 'Homburg', 'plant_country': 'Germany', 'plant_product': 1}

Is there a elegant way?

That would be a very graet help!

Here are my Models:

class City(models.Model):
    city_name = models.CharField(max_length=100, unique=True)

    class Meta:
        ordering = ['city_name']
        # changes view in admin area
        verbose_name_plural = ('Cities')
    def __str__(self):
        return self.city_name

class Country(models.Model):
    country_name = models.CharField(max_length=100, unique=True)

    class Meta:
        ordering = ['country_name']
        verbose_name_plural = 'Countries'
    def __str__(self):
        return self.country_name    

class Plant(models.Model):
    plant_number = models.CharField(max_length=10, unique=True)
    plant_name = models.CharField(max_length=10, unique=True)
    plant_city = models.ForeignKey(City, on_delete=models.CASCADE)
    plant_country = models.ForeignKey(Country, on_delete=models.CASCADE)
    plant_product = models.ManyToManyField(TPZProductCluster1)
    updated_at = models.DateField(auto_now=True)

    class Meta:
        ordering = ['plant_number']
    def plant_produces(self):
        return ', '.join([p.pc1_product for p in self.plant_product.all()])
    def __str__(self):
        return self.plant_name + " (" + str(self.plant_number) +")"

Thank you in advance!!!

user_67579 · Answer

If you want to include the name of the city, you can work with an F object:

from django.db.models import F

plant_list = Plant.objects.filter(
    plant_product=ikey
).values(
    'plant_number',
    'plant_name',
    'plant_country',
    'plant_product'
    city=F('plant_city__city_name')
)

That being said, usually using .values() is not a good idea. If you want to serialize data to a JSON blob for example, it is better to work with serializers, for example with the Django REST framework.

user_14879692 · Answer

With help of Willem I found the answer.

Here my Query:

plant_list = Plant.objects.filter(plant_product__pc1_product=ikey).values(
        'plant_number',
        'plant_name',
        'plant_city__city_name',
        'plant_country__country_name',
        'plant_product__pc1_product'
    )

Here my result:

{'plant_number': '0001', 'plant_name': 'HoP1', 'plant_city__city_name': 'Homburg', 'plant_country__country_name': 'Germany', 'plant_product__pc1_product': 'CRI-MV'}

Thanks Willem that was a great help!!!!

Django query certain attribute via foreign key

2 Answers