id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
30854	Selecting multiple FilteredRelation's returns only the last one.	Morteza PRK	Hasan Ramezani	"When using FilteredRelation multiple times in single query, results are wrong.
consider following example (with Django 2.2.6):

The models are:
{{{#!python
from django.db import models


class UserType(models.Model):
    pass


class Commodity(models.Model):
    pass


class CommodityCount(models.Model):
    commodity = models.ForeignKey(Commodity, on_delete=models.CASCADE)
    user_type = models.ForeignKey(UserType, on_delete=models.CASCADE)
    small_box = models.IntegerField(default=0)

    class Meta:
        unique_together = ('commodity', 'user_type')


class CommodityDiscount(models.Model):
    commodity = models.ForeignKey(Commodity, on_delete=models.CASCADE)
    user_type = models.ForeignKey(UserType, on_delete=models.CASCADE)
    small_box = models.IntegerField(default=0)

    class Meta:
        unique_together = ('commodity', 'user_type')

}}}

following query is what I want:

{{{#!python
query = Commodity.objects.annotate(
    cnt=FilteredRelation('commoditycount', condition=Q(commoditycount__user_type_id=1)), 
    dst=FilteredRelation('commoditydiscount', condition=Q(commoditydiscount__user_type_id=1))
).filter(
    cnt__isnull=False, 
    dst__isnull=False
).select_related('cnt','dst')
}}}

resulting query is ok:

{{{#!sql
SELECT
    ""ma_commodity"".""id"",
    cnt.""id"",
    cnt.""commodity_id"",
    cnt.""user_type_id"",
    cnt.""small_box"",
    dst.""id"",
    dst.""commodity_id"",
    dst.""user_type_id"",
    dst.""small_box""
FROM
    ""ma_commodity""
    INNER JOIN ""ma_commoditycount"" cnt ON (
        ""ma_commodity"".""id"" = cnt.""commodity_id""
        AND (cnt.""user_type_id"" = 1)
    )
    INNER JOIN ""ma_commoditydiscount"" dst ON (
        ""ma_commodity"".""id"" = dst.""commodity_id""
        AND (dst.""user_type_id"" = 1)
    )
WHERE
    (
        cnt.""id"" IS NOT NULL
        AND dst.""id"" IS NOT NULL
    )
}}}

but results are not ok:


{{{#!python
first_item = query.first()
print(dir(first_item))
>>> [ ... '_state', 'check', 'clean', 'clean_fields', 'commoditycount_set', 'commoditydiscount_set', 
    'date_error_message', 'delete', 'dst', 'from_db', 'full_clean', 'get_deferred_fields', 'id', 'objects', 
    'pk', 'prepare_database_save', 'refresh_from_db', 'save', 'save_base', 'serializable_value', 'unique_error_message', 'validate_unique'
    ]
}}}

as you can see, there is dst, but no cnt.
if I remove dst and its related FilteredRelation, cnt re appear.
"	Bug	closed	Database layer (models, ORM)	dev	Normal	fixed	FilteredRelation annotate	Morteza PRK	Accepted	1	0	0	0	0	0