Re: [Django] #16055: Filtering over generic relations with TextField/CharField object_id breaks in postgres

[Django]

#16055: Filtering over generic relations with TextField/CharField object_id 
breaks
in postgres
--------------------------------------+------------------------------------
     Reporter:  anonymous             |                    Owner:  nobody
         Type:  Bug                   |                   Status:  new
    Component:  contrib.contenttypes  |                  Version:  1.3
     Severity:  Normal                |               Resolution:
     Keywords:                        |             Triage Stage:  Accepted
    Has patch:  0                     |      Needs documentation:  0
  Needs tests:  0                     |  Patch needs improvement:  0
Easy pickings:  0                     |                    UI/UX:  0
--------------------------------------+------------------------------------

Comment (by Marc DEBUREAUX):

 Manage to create something based on Simon Charette's suggestion.

 Creating a custom `GenericRelation` with the following:
 - `get_joining_columns` returning an empty tuple
 - `get_extra_restriction` building the whole where clause with content
 type **AND** primary key join with Cast

 Here's the result:

 {{{#!python
 class CustomGenericRelation(GenericRelation):
     def get_joining_columns(self, reverse_join=False):
         joining_columns = super().get_joining_columns(reverse_join)
         self.joining_columns = joining_columns
         return tuple()

     def get_extra_restriction(self, where_class, alias, remote_alias):
         cond = super().get_extra_restriction(where_class, alias,
 remote_alias)
         from_field = self.model._meta.pk
         to_field =
 self.remote_field.model._meta.get_field(self.object_id_field_name)
         lookup = from_field.get_lookup('exact')(
             Cast(from_field.get_col(alias),
 output_field=models.TextField()),
             to_field.get_col(remote_alias))
         cond.add(lookup, 'AND')
         return cond
 }}}

 Giving the following:
 - a model `common.History` having a `GenericForeignKey` with `object_id`
 as text
 - a model `generic.Twitter` having a `CustomGenericRelation` and a numeric
 primary key
 - and the queryset:

 {{{#!python
 q =
 Twitter.objects.values('id').filter(id=1).annotate(count=Count('histories'))
 print(q.query)
 }}}

 It gives the following SQL query:

 {{{#!sql
 SELECT
     "generic_twitter"."id",
     COUNT("common_history"."id") AS "count"
 FROM "generic_twitter"
 LEFT OUTER JOIN "common_history" ON
 (((
     "common_history"."content_type_id" = 29 AND
     CAST("generic_twitter"."id" AS text) = "common_history"."object_id"
 )))
 WHERE "generic_twitter"."id" = 1
 GROUP BY "generic_twitter"."id"
 }}}

 Instead of this one (normal behaviour):

 {{{#!sql
 SELECT
     "generic_twitter"."id",
     COUNT("common_history"."id") AS "count"
 FROM "generic_twitter"
 LEFT OUTER JOIN "common_history" ON
 (
     "generic_twitter"."id" = "common_history"."object_id" AND
     ("common_history"."content_type_id" = 29)
 )
 WHERE "generic_twitter"."id" = 1
 GROUP BY "generic_twitter"."id"
 }}}

-- 
Ticket URL: <https://code.djangoproject.com/ticket/16055#comment:14>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/067.95fe18f78f202c9d194c84ef5359e563%40djangoproject.com.