Add support for conditions involving joins with aliases.

[henriquebastos]

Some queries relabel the table name with an alias, but aggregate-if can't follow it properly.

>>> qs = Book.objects.annotate(
...    mean_age=Avg('authors__age'))
...    mean_age2=Avg('authors__age', only=Q(authors__age__gte=0))
... )
>>> print qs
*** DatabaseError: no such column: aggregation_author.age

The generated query obtained by print books.query is:

SELECT
    "aggregation_book"."id", "aggregation_book"."isbn", 
    "aggregation_book"."name", "aggregation_book"."pages",
    "aggregation_book"."rating", "aggregation_book"."price",
    "aggregation_book"."contact_id", "aggregation_book"."publisher_id", 
    "aggregation_book"."pubdate", AVG(T5."age") AS "mean_age", 
    AVG(CASE WHEN "aggregation_author"."age" >= 0  
             THEN T5."age" ELSE null END) AS "mean_age2" 
FROM 
    "aggregation_book" 
    LEFT OUTER JOIN "aggregation_book_authors" 
        ON ("aggregation_book"."id" = "aggregation_book_authors"."book_id") 
    LEFT OUTER JOIN "aggregation_author" T5 
        ON ("aggregation_book_authors"."author_id" = T5."id") 
GROUP BY 
    "aggregation_book"."id", "aggregation_book"."isbn",
    "aggregation_book"."name", "aggregation_book"."pages", 
    "aggregation_book"."rating", "aggregation_book"."price", 
    "aggregation_book"."contact_id", "aggregation_book"."publisher_id", 
    "aggregation_book"."pubdate";

The problem is that the WHEN expression generated by the only argument is using the table name aggregation_author instead of T5.

[decko]

Hi everyone. This issue is a big deal for me, so I'm willing to pay USD 40,00 for it. This offer is registered on FreedomSponsors (http://www.freedomsponsors.org/core/issue/363/add-support-for-conditions-involving-joins-with-aliases). If you solve it (according to the acceptance criteria described there), please register on FreedomSponsors and mark it as resolved there I'll then check it out and gladly pay up!

Oh, and if anyone else also wants throw in a few bucks on this, you should check out FreedomSponsors!

[insolite]

I've spent a few hours on it and seems like I've found some kind of solution of this issue, that works for me (Django 1.7). henriquebastos, could you please post more complete use case (Book model code, etc.), that i can test out on my own to ensure that I'm doing all the stuff correctly and that it works fine not only for my example? In my case I have this code:

# current_user is defined above as User instance and in this example current_user.id is 1.
users = User.objects.annotate(
    messages_from_count=Count('chat_message_from_set', only=Q(chat_message_from_set__user_to=current_user)),
    messages_to_count=Count('chat_message_to_set', only=Q(chat_message_to_set__user_from=current_user))
)

which resolves into:

SELECT
    "cons_user"."id",
    "cons_user"."auth_user_id",
    "cons_user"."name",
    "cons_user"."avatar",
    COUNT (
        CASE
        WHEN "cons_chatmessage"."user_to_id" = 1 THEN
            "cons_chatmessage"."id"
        ELSE
            NULL
        END
    ) AS "messages_from_count",
    COUNT (
        CASE
        WHEN "cons_chatmessage"."user_from_id" = 1 THEN
            T5."id"
        ELSE
            NULL
        END
    ) AS "messages_to_count"
FROM
    "cons_user"
LEFT OUTER JOIN "cons_chatmessage" ON (
    "cons_user"."id" = "cons_chatmessage"."user_from_id"
)
LEFT OUTER JOIN "cons_chatmessage" T5 ON (
    "cons_user"."id" = T5."user_to_id"
)
GROUP BY
    "cons_user"."id",
    "cons_user"."auth_user_id",
    "cons_user"."name",
    "cons_user"."avatar";

which is not correct here:

        WHEN "cons_chatmessage"."user_from_id" = 1 THEN
            T5."id"

After fixing it, I've ended up with the following SQL code:

SELECT
    "cons_user"."id",
    "cons_user"."auth_user_id",
    "cons_user"."name",
    "cons_user"."avatar",
    COUNT (
        CASE
        WHEN "cons_chatmessage"."user_to_id" = 1 THEN
            "cons_chatmessage"."id"
        ELSE
            NULL
        END
    ) AS "messages_from_count",
    COUNT (
        CASE
        WHEN T5."user_from_id" = 1 THEN
            T5."id"
        ELSE
            NULL
        END
    ) AS "messages_to_count"
FROM
    "cons_user"
LEFT OUTER JOIN "cons_chatmessage" ON (
    "cons_user"."id" = "cons_chatmessage"."user_from_id"
)
LEFT OUTER JOIN "cons_chatmessage" T5 ON (
    "cons_user"."id" = T5."user_to_id"
)
GROUP BY
    "cons_user"."id",
    "cons_user"."auth_user_id",
    "cons_user"."name",
    "cons_user"."avatar";

which is exactly what I want to get. Is there something similar in your case? My models looks like the following (unnecessary fields omitted):

class User(models.Model):
    auth_user = models.OneToOneField(AuthUser, on_delete=models.CASCADE)
    name = models.CharField(max_length=10)
    avatar = models.ImageField(null=True, default=None, storage=AvatarStorage('user'))

class ChatMessage(models.Model):
    user_from = models.ForeignKey(User, on_delete=models.CASCADE, related_name='chat_message_from_set')
    user_to = models.ForeignKey(User, on_delete=models.CASCADE, related_name='chat_message_to_set')