How to compose Django Model Filter for relation existence?-CodePudding

There is a many-to-many relationship between A and B. There are 3 tables to represent that relationship. TableA, TableB, TableAB

Now I have another table TableC where it has a foreign key to TableA, and I want to filter objects from TableC where it has a relationship with TableB.

The following is high-level code to give you the idea how the models look like.

class A:
  value = models.CharField(max_length=255)

class B:
  As = models.ManyToManyField('A', related_name='as')

class C:
   object_a = models.ForeignKey('A')

The SQL query looks like

SELECT 
  * 
FROM 
  TABLE_C 
WHERE (
  SELECT 
    COUNT(*) 
  FROM 
    TABLE_AB 
  WHERE 
    TABLEAB.A_id = TABLE_C.A_id
) > 0

CodePudding user response：

I found a solution

C.objects.\
 .annotate(num=Count('object_a_As'))\
 .filter(num__gt=0)

It runs the following query

SELECT 
  *, COUNT(TABLE_AB.A_id) as "num"
FROM
  TABLE_C
LEFT OUTER JOIN
  TABLE_AB
ON
  TABLE_C.A_id = TABLE_AB.A_id
GROUP BY
  TABLE_C.id
HAVING
  COUNT(TABLE_AB.B_id) > 0