0

Let's imagine I have 2 models:

Class Tree(models.Model):
    title = models.CharField(max_length=255)

Class Apple(models.Model):
    tree = models.ForeignKey(Tree, related_name="apples")

How do I select all the Trees that have Apples.
I mean I want to select all the Trees that exist in Apple Model from an instance of Tree.

I think I want to execute this query:

SELECT DISTINCT tree.id, tree.title
FROM apple JOIN tree ON apple.tree = tree.id

Untill now i have written 2 queries and they are working but I think they are not the best practices to do it:

Tree.objects.filter(
    apples__tree__in=Apple.objects.all().values_list("tree")
).distinct()
 
Tree.objects.filter(apples__tree__isnull=False).distinct()
Mojtaba Arezoomand
  • 2,140
  • 8
  • 23
  • In this link you can find the best practice : [Django Query Related Field Count](https://stackoverflow.com/questions/6525771/django-query-related-field-count) – Navid salehi pour Jun 19 '21 at 12:37

2 Answers2

1

You can query the relation for 'NULL' directly

Trees.objects.filter(apples__isnull=False).distinct()

P.S. If you want the exact query, you can write it like this (but you'll only get the dictionaries, not a Tree object):

Apple.objects.order_by().values('tree__id', 'tree__title').distinct()
Alexandr Tatarinov
  • 3,946
  • 1
  • 15
  • 30
  • Thanks for your answer, The problem is that I don't want to use `isnull` because in my query it will add `WHERE tree.id IS NOT NULL`. I want a query that does this without this `WHERE` clause. – Mojtaba Arezoomand Jun 19 '21 at 12:46
  • Well, wha't the problems with the basic SQL construct if that achieves your goal? Any specific reason? – Alexandr Tatarinov Jun 19 '21 at 12:47
  • I think it's faster this way in larger queries because it doesn't check `WHERE` clause it's just simple `SELECT *`, your query will work fine, what I need is query optimization. – Mojtaba Arezoomand Jun 19 '21 at 12:50
  • Don't optimize unless you have a specific performance constraints and are violating them. Unless you can meaningfully measure it, it's non-existent – Alexandr Tatarinov Jun 19 '21 at 12:53
  • @MojixCoder I've added another query to the answer. Please **accept** if that was helpful – Alexandr Tatarinov Jun 19 '21 at 18:42
0

You can use django aggregates.

from django.db.models import Count
User.objects.annotate(page_count=Count('page')).filter(page_count__gte=2).count()
Navid salehi pour
  • 11
  • 1