Hi all,
When you use a "tables" kwarg in a database query method (get_list,
etc), you provide a list of strings which correspond to the database
names of the tables you want to have joined. You can then specify a
"where" clause to narrow the join.
When the provided table name is used in the generated SQL query, it is
quoted. This preserves case, etc, in the table name, and all is good
with the world..
However, this quoting behaviour does prevent some other tricks. If the
table name was unquoted, you could nominate a subselect query as a join
point, rather than just naming a table. To give an artificial example
using the Django tutorial database:
polls.get_list(
tables=[
"(SELECT * FROM polls_choices
WHERE polls_choices.votes > 10) AS popular_choices"
]
where=[
"polls_polls.id=popular_choices.poll_id"
]
)
As Django stands, the quoting process puts the entire subselect inside
quotes, which turns into a syntax error in SQL. If the quoting was
removed from 'tables' strings, this would be a valid SQL query
operating over an inner join with a subselect.
I can see why the quoting is beneficial, as it removes leakage of SQL
quoting syntax from Python code space. However, you already have to
nominate SQL names ('polls_choices', not just 'choices') in order to
use the tables and where clause, so there is already partial leakage.
1) Is the quoting behaviour a feature or a bug? (I suspect feature, but
I thought I'd check)
2) Is there room for a smarter quoting scheme? - e.g., don't quote a
table name if the first character is a '('
3) Is there room to add a distinct 'subselect' kwarg to queries? This
kwarg would be identical to 'tables', but wouldn't quote its contents
4) Is there some other way to construct a subselect join that I am
missing (including, ideally, a left outer join)?
Many thanks,
Russ Magee
