heroku，postgreSQL，django，comments，tastypie：没有操作符匹配给定的名称和参数类型。您可能需要添加显式类型转换 [英] heroku, postgreSQL, django, comments, tastypie: No operator matches the given name and argument type(s). You might need to add explicit type casts

问题描述

我有一个简单的查询django的内置的注释模型，并得到以下错误以下的heroku的postgreSQL数据库：

  DatabaseError：运算符不存在：integer = text LINE 1：
 ... INNER JOINdjango_commentsON（pi ns_pin。id=django _... 
 ^ 
提示： 
您可能需要添加明确的类型转换
  

在googling之后似乎这个错误已经在django之前被多次处理，但我仍然得到它（所有相关的问题在3 - 5年前关闭）我使用django版本1.4和最新的

查询是在orm过滤器下创建的，并且与我的开发数据库（sqlite3）完美配合：

  class MyResource（ModelResource）：
 
 comments = fields.ToManyField（'my.api.api.CmntResource'，'comme nts'，full = True，null = True）
 
 def build_filters（self，filters = None）：
如果过滤器为None：
 filters = {} 
 
 orm_filters = super（MyResource，self）.build_filters（filters）
 
如果过滤器中的cmnts：
 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（{
我的MyResource，
}，'content_object'）
 username = fields.CharField（attribute ='user__username' null = True）
 user_id = fields.CharField（attribute ='user__id'，null = True）
  

任何人都有任何解决这个错误的经验，而无需编写原始SQL？

解决方案

Buil关于IMSoP的答案：当通用外键使用object_id的文本字段并且对象的id字段不是文本字段时，这是django的ORM层的限制。 Django不想做任何假设，或者将对象的id转换为不符合的东西。我发现了一个很好的文章，这个 http://charlesleifer.com/blog/working-around-django-s-orm-to-do-interesting-things-with-gfks/ 。

文章作者查尔斯·莱弗（Charles Leifer）提出了一个非常酷的查询解决方案，受到影响，对处理这个问题将非常有用。

或者，我设法让我的查询工作如下：

 如果过滤器中的cmnts：
 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'] =评论
  

正在寻找一种修改SQL的方法，类似于Charles所做的，但事实证明了这一切我不得不将查询分解成两部分，并将str（id）转换为int（id）。

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?

解决方案

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.

这篇关于heroku，postgreSQL，django，comments，tastypie：没有操作符匹配给定的名称和参数类型。您可能需要添加显式类型转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！