CODEWITHSUNDEEP
sqldjangosqlitedjango-modelsdjango-orm
jaimish11
jaimish11

Reputation: 564

Why is this Django raw SQL query not returning output?

I have a raw SQL query that I am trying to run in Django. When I display the RawQuerySet object, it's showing the correct query but it isn't returning any output.

I have tried converting the parameters to string and have tried appending quotes to the parameters but that didn't work.

I have also tried the same query but I hardcoded the parameters. That worked.

I opened the dbshell as well to try and see if the query returns an output. It works fine too.

This is what I ran in my dbshell:

select id FROM recommender_item WHERE 
id in (select item_id from 
recommender_item_likes where user_id = 1) 
and color = 'Black';

Note, that the below query did not work:

select id FROM recommender_item WHERE 
id in (select item_id from 
recommender_item_likes where user_id = 1) 
and color = Black;

This is the actual query I want to run:

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,])

This is the same query with hardcoded parameters which is working:

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])

The output in my template should be just this list of ids: 1, 64, 437, 1507, 1685

However, right now it just returns []

This is the RawQuerySet object in both cases, respectively:

<RawQuerySet: select id FROM recommender_item WHERE 
id in (select item_id from recommender_item_likes where user_id = 1) 
and color = Black>

and

<RawQuerySet: select id FROM recommender_item WHERE 
id in (select item_id from recommender_item_likes where user_id = 1) 
and color = "Black">

Actual SQL query being executed, retrieved from Django debug toolbar:

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')

Upvotes: 2

Views: 2226

Answers (2)

jaimish11
jaimish11

Reputation: 564

Okay, after a lot of playing around with the shell and the debug toolbar, I have found a somewhat redundant way to achieve this. Create six separate strings:

str1 = 'select id FROM recommender_item WHERE id in (select item_id from recommender_item_likes where user_id ='
str2 = str(request.user.id)
str3 = ') and '
str4 = user_pref
str5 = ' = '
str6 = "'"+pref_choice+"'"
q = str1 + str2+ str3 + str4 + str5 + str6

Then I pass this variable as such: Item.objects.raw(q) This gives me the required output.

Given the model changes, you should be able to do this:

Item.objects.filter(likes=request.user)

Or

request.user.item_likes.all()

Upvotes: 0

Endre Both
Endre Both

Reputation: 5740

This query should get you all black items liked by the user:

Item.objects.filter(likes=request.user, color='Black')

Add .values('id') if you only need the ids like in the raw query.

But I still find your original problem more interesting. I have no problems issuing raw queries with string parameters to Postgresql. I'll have to try with Sqlite.

BTW, the ORM query highlights that likes is a misnomer; likers or similar would appear to be a more fitting name.

You can expand a dictionary in filter():

filter_field = 'color'
filter_string = 'black'
filter_dict = {filter_field: filter_string}
Item.objects.filter(**filter_dict)

Upvotes: 1

Related Questions