我有一个尝试在Django中运行的原始SQL查询。当我显示RawQuerySet对象时,它会显示正确的查询,但不会返回任何输出。
我尝试将参数转换为字符串,并尝试将引号附加到参数上,但这没有用。
我也尝试过相同的查询,但是我对参数进行了硬编码。那行得通。
我也打开了dbshell,以尝试查看查询是否返回输出。它也很好。
这是我在dbshell中运行的内容:
select id FROM recommender_item WHERE
id in (select item_id from
recommender_item_likes where user_id = 1)
and color = 'Black';
请注意,以下查询无效:
select id FROM recommender_item WHERE
id in (select item_id from
recommender_item_likes where user_id = 1)
and color = Black;
这是我要运行的实际查询:
Item.objects.raw('select id FROM recommender_item WHERE
id in (select item_id from recommender_item_likes where
user_id = %s) and %s = %s', [request.user.id, user_pref, pref_choice,])
这是具有硬编码参数的相同查询,该查询有效:
Item.objects.raw('select id FROM recommender_item WHERE
id in (select item_id from recommender_item_likes where user_id = %s)
and color = "Black"', [request.user.id])
我的模板中的输出应仅为以下ID列表:1、64、437、1507、1685
但是,现在它只返回[]
在两种情况下,这都是RawQuerySet对象:
<RawQuerySet: select id FROM recommender_item WHERE
id in (select item_id from recommender_item_likes where user_id = 1)
and color = Black>
和
<RawQuerySet: select id FROM recommender_item WHERE
id in (select item_id from recommender_item_likes where user_id = 1)
and color = "Black">
从Django调试工具栏检索的实际SQL查询正在执行:
select id FROM recommender_item WHERE
id in (select item_id from recommender_item_likes where
user_id = '1') and '''color''' = '''"Black"'''
models.py
class Item(models.Model):
#id = models.UUIDField(primary_key = True, default = uuid.uuid4, help_text = 'Unique ID for this particular item')
item_type = models.CharField(max_length = 200, null = True, blank = True)
price = models.CharField(max_length = 200, null = True, blank = True)
color = models.CharField(max_length = 200, null = True, blank = True)
image_URL = models.CharField(max_length = 1000, null = True, blank = True)
fit = models.CharField(max_length = 200, null = True, blank = True)
occasion = models.CharField(max_length = 200, null = True, blank = True)
brand = models.CharField(max_length = 200, null = True, blank = True)
pattern = models.CharField(max_length = 200, null = True, blank = True)
fabric = models.CharField(max_length = 200, null = True, blank = True)
length = models.CharField(max_length = 200, null = True, blank = True)
likes = models.ManyToManyField(User, blank = True, related_name = 'item_likes')
此查询应为您提供用户喜欢的所有黑色物品:
Item.objects.filter(likes=request.user, color='Black')
.values('id')
如果仅需要原始查询中的ID,则添加。
但我仍然觉得您原来的问题更有趣。我将带有字符串参数的原始查询发布给Postgresql没有问题。我将不得不尝试使用Sqlite。
顺便说一句,ORM查询突出显示这likes
是一个错误的名称;likers
或类似名称似乎更合适。
您可以在中展开字典filter()
:
filter_field = 'color'
filter_string = 'black'
filter_dict = {filter_field: filter_string}
Item.objects.filter(**filter_dict)
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句