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
32449	Allow specifying tables in RawSQL().	João Carneiro Haas	nobody	"We have in our application a RawSQL annotation which depends on a internal join to work:

{{{
#!python
# accounts/models.py

class Org(models.Model):
    name = models.TextField()

class Account(models.Model):
    org = models.ForeignKey(Org)

query = Account.objects.select_related(""org"").annotate(org_name=RawSQL(""SELECT custom_name(accounts_org.name)""))
}}}

Well, sometimes Django tries to optimize the queries and removes unneded JOINs, such as when calling count:

{{{
#!python
query.count()
# SELECT COUNT(*) FROM (
#     SELECT ""accounts_account"".""id"" AS Col1, (SELECT ""accounts_org"".""name"") AS ""org_name""
#     FROM ""accounts_org""
#     GROUP BY ""devices_device"".""id"", (SELECT ""accounts_org"".""name"")
# )
}}}

Which results in an 'missing FROM-clause' error.

Since (I believe) it's impossible for Django to analyze the passed RawSQL query, I would like to request some way to specify which joins/select_related the annotated RawSQL depends on.

My suggestion would be to pass a list of table dependencies to the RawSQL:

{{{
#!python
raw = RawSQL(""SELECT custom_name(accounts_org.name)"", tables=[""org""])
}}}

If the ticket gets 'confirmed' and an API gets specified I can try tackling this issue.

For now I'm using the following workaround, which forces a join:

{{{
#!python
query = Accounts.objects.annotate(org_id=F('org__id'), custom_name=...)
}}}

Obs.: Fixing this before https://code.djangoproject.com/ticket/28477 would result in queries with unused annotations (such as the ones above) to always perform the join. I believe it won't impact the development of this ticket, but who knows ¯\_(ツ)_/¯"	New feature	closed	Database layer (models, ORM)	3.1	Normal	wontfix	annotate, count, RawSQL, select_related, JOIN		Unreviewed	0	0	0	0	0	0