Q:

Make a Django query from raw SQL with Distinct over Django ManyToMany Relation

models.py
class FlagsModel(models.Model):
    name = models.CharField(max_length=64, verbose_name='Flag')

class ColorsModel(models.Model):
    name = models.CharField(max_length=64, verbose_name='Color')

class RelationModel(models.Model):
    flag = models.ForeignKey(FlagsModel, null=True, on_delete=models.SET_NULL)
    color = models.ForeignKey(ColorsModel, null=True, on_delete=models.SET_NULL)

SQL Tables:
FlagsModel
+----+--------------+
| id | name         |
+----+--------------+
|  1 | brazil       |
|  2 | canada       |
|  3 | china        |
|  4 | portugal     |
+----+--------------+

ColorsModel
+----+--------------+
| id | name         |
+----+--------------+
|  1 | red          |
|  2 | yellow       |
|  3 | green        |
+----+--------------+

RelationModel
+----+---------+----------+
| id | flag_id | color_id |
+----+---------+----------+
|  1 | 1       | 3        |
|  2 | 1       | 2        |
|  4 | 2       | 1        |
|  5 | 3       | 1        |
|  6 | 3       | 2        |
|  7 | 4       | 1        |
|  8 | 4       | 2        |
|  9 | 4       | 3        |
+----+---------+----------+

I am looking now for a Query which selects each flag containing the colors "yellow" and "red"
in raw SQL i can do something like this:
SELECT DISTINCT a1.flag_id from relationmodel a1, relationmodel a2 where a1.color_id=2 AND a2.color_id=1

but i havent found a solution that's gonna work in django
any ideas ?

A:

You can filter with:
FlagModel.objects.filter(
    relationmodel__color_id=1
).filter(
    relationmodel__color_id=2
)
This will thus create a query that looks like:
SELECT flagmodel.*
FROM flag
INNER JOIN relationmodel r1 ON r1.flag_id = flag.id
INNER JOIN relationmodel r2 ON r2.flag_id = flag.id
WHERE r1.flag_id = 1 AND r2.flag_id = 2
So for the given sample data, it will return the flags of China and Portugal.

