Re: Complex query, queryset.query.alias_map and query changes

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/

On Friday, September 23, 2016 at 11:40:04 AM UTC-4, Felipe 'chronos' Prenholato wrote:

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/

--
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-users@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.