djangopostgresqlherokutastypiedjango-comments

heroku, postgreSQL, django, comments, tastypie: No operator matches the given name and argument type(s). You might need to add explicit type casts


I have a simple query on django's built in comments model and getting the error below with heroku's postgreSQL database:

DatabaseError: operator does not exist: integer = text LINE 1: 
... INNER JOIN "django_comments" ON ("pi ns_pin"."id" = "django_...
                                                         ^
HINT:  No operator matches the given name and argument type(s). 
You might need to add explicit type casts.

After googling around it seems this error has been addressed many times before in django, but I'm still getting it (all related issues were closed 3-5 years ago) . I am using django version 1.4 and the latest build of tastypie.

The query is made under orm filters and works perfectly with my development database (sqlite3):

class MyResource(ModelResource):    

    comments = fields.ToManyField('my.api.api.CmntResource', 'comments', full=True, null=True)

    def build_filters(self, filters=None):
        if filters is None:
            filters = {}

        orm_filters = super(MyResource, self).build_filters(filters)

        if 'cmnts' in filters:
            orm_filters['comments__user__id__exact'] = filters['cmnts']

class CmntResource(ModelResource):
    user = fields.ToOneField('my.api.api.UserResource', 'user', full=True)
    site_id = fields.CharField(attribute = 'site_id')
    content_object = GenericForeignKeyField({
        My: MyResource,
    }, 'content_object')
    username = fields.CharField(attribute = 'user__username', null=True)
    user_id = fields.CharField(attribute = 'user__id', null=True)

Anybody have any experience with getting around this error without writing raw SQL?


Solution

  • Building on IMSoP's answer: This is a limitation of django's ORM layer when a Generic foreign key uses a text field for the object_id and the object's id field is not a text field. Django does not want to make any assumptions or cast the object's id as something it's not. I found an excellent article on this http://charlesleifer.com/blog/working-around-django-s-orm-to-do-interesting-things-with-gfks/.

    The author of the article, Charles Leifer came up with a very cool solution for query's that are affected by this and will be very useful in dealing with this issue moving forward.

    Alternatively, i managed to get my query to work as follows:

    if 'cmnts' in filters:
        comments = Comment.objects.filter(user__id=filters['cmnts'], content_type__name = 'my',   site_id=settings.SITE_ID ).values_list('object_pk', flat=True)
        comments = [int(c) for c in comments]
        orm_filters['pk__in'] = comments
    

    Originally i was searching for a way to modify the SQL similar to what Charles has done, but it turns out all i had to do was break the query out into two parts and convert the str(id)'s to int(id)'s.