Complex query, queryset.query.alias_map and query changes

[Felipe Prenholato]

Hello folks!

I have a complex queryset here that do joins in at least 8 tables. The query is fast, but has use of Postgresql `concat` method, and to make it have correct aliases I'm using `qs.query.alias_map` where qs is my queryset instance:

def my_complex_queryset(self):
    qs = self.filter(*lot_of_stuff_with_lot_of_joins)
    alias_map = {
        j.table_name: a for a, j in qs.query.alias_map.items() 
            if j.table_name in ['table_a', 'table_b']
    }
    concat_str = (
        'concat("{table_a}"."city", "{table_a}"."state") '
        '!= concat("{table_b}"."city", "{table_b}"."state")'
    ).format(
        table_a=alias_map['table_a'],
        table_b=alias_map['table_b']
    )
    qs = qs.extra(where=[concat_str])
    return qs

The problem is when the query is re-executed with some other table in the joins because it will change the alias_map but won't run my method again to  update the concat_str:

my_qs = MyModel.objects.my_complex_queryset()
print my_qs
# now it will fail because alias changed:
print my_qs.filter(another_table_1__another_table_2__id__in=[1, 2, 3])

The error I receive is:

 ProgrammingError: invalid reference to FROM-clause entry for table "table_a"

LINE 1: ... AND U0."some_id" IN (13361, 9820) AND (concat("table_a...

                                                             ^

HINT:  Perhaps you meant to reference the table alias "u9".

The great question is: Is possible to do the query with dynamic aliases to be used in concat string? Maybe the `tables` argument to extra? I not saw much doc about it :\.

My Django version is 1.6.x, not because my choice but because software here still a bit legacy about Django versions and we won't change until January.

Thanks for your attention!

Felipe Prenholato.

Home page: http://devwithpassion.com | http://chronosbox.org/blog
GitHub: http://github.com/chronossc/ | Twitter: http://twitter.com/chronossc

LinkedIn: http://br.linkedin.com/in/felipeprenholato/

[Tim Graham]

Not sure, this is using lots of private API and QuerySet.extra() which is discourage these days. I'm afraid you won't get much help for unsupported Django versions.

After you upgrade to Django 1.9+, hopefully you can construct your query using the new expressions API!

https://docs.djangoproject.com/en/stable/ref/models/expressions/

[Felipe Prenholato]

Yeah, I know :\, but still maybe someone have a good idea :), lot of people still using old Django versions.

I tried the expressions, the `F()` but it not worked well and I had no time to debug why, but in next weeks I'll probably create a slug field to compare directly.

I post here if have updates.

Thx Tim.

Felipe Prenholato.

Home page: http://devwithpassion.com | http://chronosbox.org/blog
GitHub: http://github.com/chronossc/ | Twitter: http://twitter.com/chronossc

LinkedIn: http://br.linkedin.com/in/felipeprenholato/

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/77febd31-5b71-4ce2-a794-dab2f7c5e3e7%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.