Annotate after filter joins - is this a bug?

[Ole Laursen]

Hi!

I was reading the ORM documentation about annotate and aliases. Given

  class Paper(models.Model): pass

  class Author(models.Model):

      paper = models.ForeignKey(Paper, on_delete=models.CASCADE)

      name = models.CharField(max_length=100)

and the query

  Paper.objects.filter(author__name__contains="Foo").filter(author__name__contains="Bar").annotate(bar=F("author__pk")).filter(bar=123)

Django 2.0.3 generates the query

SELECT ... FROM "x_paper" INNER JOIN "x_author" ON ... INNER JOIN "x_author" T3 ON ...

 WHERE (

  "x_author"."name" LIKE %Foo% ESCAPE '\'

  AND T3."name" LIKE %Bar% ESCAPE '\'

  AND "x_author"."id" = 123)

In other words, the annotate(bar=F("author__pk")) binds bar to the first join and not the second T3 join. Is this a bug?

I need to find all papers with one author fulfilling a set of criteria and another author (another as in author1.pk != author2.pk) fulfilling a different set of criteria.

The only way I can think of to do that is by somehow naming something from the join, like this

  Paper.objects

   .filter(author__name__contains="Foo").annotate(foo_pk=F("author__pk"))

   .filter(author__name__contains="Bar").annotate(bar_pk=F("author__pk"))

   .exclude(foo_pk=F("bar_pk"))

It could also be used to solve related join queries, like "find papers with authors with the same last name".

The only way I can see out at the moment is generating a query without the exclude part, then parsing it to find the Tx alias names and putting them in a RawSQL expression to form the final query.

Ole