It's better to allow using tables parameter with custom alias in extra query

[Wonlay]

Sometimes we need specify tables that are already used in the query (In my case INNER JOIN on the save model).
Currently, django don't allow such queries, the extra table won't create another alias.
I find it's better, if we allow specifying a explicit alias, so that we can use the extra query like this:

MyModel.objects.extra(tables['app_table1 alias1'], where=['app_table1.col1=alias1.col1', 'alias1.name like ...'])

And the patch attached.

[James Bennett]

Django is in feature freeze for 1.0, so this change -- if it goes in -- can't be targeted for the 1.0 milestone.

[Malcolm Tredinnick]

Although encouraging yet more use of extra() is something we should avoid, since it's very fragile when combined with other things in querysets, if a reasonable API can be found, including this functionality (after 1.0) is a possibility. I don't like this syntax, however. Any change like this should also be leveraging the join() method on the Query class so that the alias is managed correctly.

Right now, the correct way to do this is to construct the queryset up to where the extra() call is required, then call qs.query.join(...) with the right parameters to get a join alias and then use that alias in the call to extra() on the queryset. It becomes three lines of code, which isn't too onerous. You'll have to read the docstring for join() and play around a bit, since this isn't public API at the moment, but it will remain stable, so you can rely on things working that way.

[Wonlay]

I totally agree with mtredinnick.
And the join solution do solve my problem.

Maybe this kind of staff should be solved by something like (qs1 + qs2) , (qs1 - qs2) , (qs1 LEFT JOIN qs2 ON ('qs1__model_field1', 'qs2__model_field2')) , and other cross QuerySet operations.

Should I close this ticket now?

[Malcolm Tredinnick]

Leave the ticket open so that we have a reference ticket for creating a decent API after 1.0 sometime. I don't know what that API will look like, but it's always been in the back of my mind that something would be nice. Fortunately, I don't need this behaviour very much, so I'm leaving it up to somebody with the motivation to solve the problem to come up with some API ideas for us to consider.

[Phillip Temple]

I think it's an excellent patch, and useful if you want to implement a tree branch totals query for django-mptt. It's backward compatible so I can't see any problem incorporating it. The only thing I could also suggest is having the syntax:

table = ['newtable as newalias']

This will allow additional arguments to be added, giving some future-proofing, with the added bonus you can simply cut and paste from your tried and tested SQL statement.

Phillip.

[Russell Keith-Magee]

A design decision has already been made. That decision was "Someday, maybe". Reverting ticket state.

[(none)]

Milestone post-1.0 deleted

[Aymeric Augustin]

Change UI/UX from NULL to False.

[Aymeric Augustin]

Change Easy pickings from NULL to False.

[Tim Graham]

We are no longer fixing bugs with QuerySet.extra() per ​discussion on django-developers.