I want to show in an Html the name of the city, state, and country of a publication. But they are in different tables.
Here is my models.py
class country(models.Model): country_name = models.CharField(max_length=200, null=True) country_subdomain = models.CharField(max_length=3, null=True) def __str__(self): return self.country_name class countrystate(models.Model): state_name = models.CharField(max_length=200, null=True) country = models.ForeignKey(country, on_delete=models.CASCADE, null=True) importance = models.IntegerField(null=True) def __str__(self): return self.state_name class city(models.Model): city_name = models.CharField(max_length=200, null=True) countrystate = models.ForeignKey(countrystate, on_delete=models.CASCADE, null=True) def __str__(self): return self.city_name class publication(models.Model): user = ForeignKey(users, on_delete=models.CASCADE, null=False) title= models.CharField(max_length=300, null=True) country=models.ForeignKey(country, on_delete=models.CASCADE, null=True) countrystate=models.ForeignKey(countrystate, on_delete=models.CASCADE, null=True) city=models.ForeignKey(city, on_delete=models.CASCADE, null=True) def __str__(self): return self.title
Here is my views.py
def publications(request): mypublications = publication.objects.filter(user_id=request.session['account_id']) dic.update({"plist": mypublications }) return render(request, 'blog/mypublications.html', dic)
In a django view, what is the equivalent of the next sql query?
SELECT p.user_id, p.title, c.cuntry_id, c.country_name, s.state_id, s.state_name, y.city_id, y.city_name FROM publication AS p INNER JOIN country AS c ON c.id = p.country_id INNER JOIN countrystate AS s ON s.id = p.countrystate_id INNER JOIN city AS y ON y.id = p.city_id
In Django, there is a method called select_related that will allow us to make inner-join. Here our goal is to select and display the name of the city, state, and country of a publication but all of them reside at different tables.
10. Join Queries. Join can be done with select_related method: Django defines this function as Returns a QuerySet that will “follow” foreign-key relationships, selecting additional related-object data when it executes its query.
1 Answer. Show activity on this post. In your models Device and History models are related with a foreign key from History to DeviceModel, this mean when you have a History object you can retrieve the Device model related to it, and viceversa (if you have a Device you can get its History).
Use union operator for queryset | to take union of two queryset. If both queryset belongs to same model / single model than it is possible to combine querysets by using union operator. One other way to achieve combine operation between two queryset is to use itertools chain function.
You are probably looking for select_related
, which is the natural way to achieve this:
pubs = publication.objects.select_related('country', 'country_state', 'city')
You can check the resulting SQL via str(pubs.query)
, which should result in output along the following lines (the example is from a postgres backend):
SELECT "publication"."id", "publication"."title", ..., "country"."country_name", ... FROM "publication" INNER JOIN "country" ON ( "publication"."country_id" = "country"."id" ) INNER JOIN "countrystate" ON ( "publication"."countrystate_id" = "countrystate"."id" ) INNER JOIN "city" ON ( "publication"."city_id" = "city"."id" )
The returned cursor values are then translated into the appropriate ORM model instances, so that when you loop over these publications, you access the related tables' values via their own objects. However, these accesses along the pre-selected forward relations will not cause extra db hits:
{% for p in pubs %} {{ p.city.city_name}} # p.city has been populated in the initial query # ... {% endfor %}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With