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
29021	Weird behavior of foreign key lookup and annotate F expression	Paul	nobody	"
{{{
class Sentence(Model):
    name = CharField()
    
class Tokens(Model):
    token = CharField()
    sentence = ForeignKey(Sentence, related_name='tokens')
}}}



{{{
Sentence.objects.annotate(n=Count('tokens', distinct=True)).filter(n=5).filter(tokens__name__in=['se']).annotate(n0=F('tokens')).filter(tokens__name__in=['faire']).annotate(n1=F('tokens')).filter(tokens__name__in=['faire']).annotate(n2=F('tokens')).filter(tokens__name__in=['un']).annotate(n3=F('tokens')).filter(tokens__name__in=['avoir']).annotate(n4=F('tokens'))
}}}


Above code generates the following query:


{{{
    SELECT ""sentence"".""id"", ""sentence"".""name"" COUNT(DISTINCT ""token"".""id"") AS ""n"", T3.""id"" AS ""n0"", T4.""id"" AS ""n1"", T4.""id"" AS ""n2"", T6.""id"" AS ""n3"", T6.""id"" AS ""n4"" FROM ""sentence"" LEFT OUTER JOIN ""token"" ON (""sentence"".""id"" = ""token"".""sentence_id"") INNER JOIN ""token"" T3 ON (""sentence"".""id"" = T3.""sentence_id"") INNER JOIN ""token"" T4 ON (""sentence"".""id"" = T4.""sentence_id"") INNER JOIN ""token"" T5 ON (""sentence"".""id"" = T5.""sentence_id"") INNER JOIN ""token"" T6 ON (""sentence"".""id"" = T6.""sentence_id"") INNER JOIN ""token"" T7 ON (""sentence"".""id"" = T7.""sentence_id"") WHERE (T3.""name"" IN (se) AND T4.""name"" IN (faire) AND T5.""name"" IN (un) AND T6.""name"" IN (avoir) AND T7.""name"" IN (faire)) GROUP BY ""word_frword"".""id"", T3.""id"", T4.""id"", T6.""id"" HAVING COUNT(DISTINCT ""token"".""id"") = 5
}}}


There is `T4.""id"" AS ""n2""` instead of `T5.""id"" AS ""n2""`, so chaining is working incorrectly."	Bug	closed	Database layer (models, ORM)	1.10	Normal	needsinfo	django-model, foreign-key		Unreviewed	0	0	0	0	0	0