Why is this Django raw SQL query not returning output?

Question

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

If you format your code lines to prevent horizontal scrolling, it will be easier to help. — Endre Both
– Endre Both, Commented Mar 26, 2019 at 15:21
I think the output of RawQuerySet.__repr__ is misleading. If you inspect the actual queries made (using django.db.connection.queries), you should find that contrary to RawQuerySet output, string paramters are quoted in the SQL sent to the database. Not sure what this means in your case though; it seems that if your hardcoded query works, so should the parametrized one. — Endre Both
– Endre Both, Commented Mar 26, 2019 at 15:45
So I found the actual query being executed through django debug toolbar. It's this: select id FROM recommender_item WHERE id in (select item_id from recommender_item_likes where user_id = '1') and '''color''' = '''"Black"''' I don't know if this will help though. — jaimish11
– jaimish11, Commented Mar 26, 2019 at 17:09
I've tried with Sqlite now, and it works just fine, with string parameters being single-quoted, e.g. WHERE color = 'black' (DDT shows triple instead of single quotes though). I'm using Django 2.1.7; maybe there were problems in earlier versions. What is your version? — Endre Both
– Endre Both, Commented Mar 27, 2019 at 11:19

Endre Both · Accepted Answer · 2019-03-27 11:01:55Z

1

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)

edited Mar 27, 2019 at 11:01

answered Mar 27, 2019 at 10:36

Endre Both

5,8101 gold badge29 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

jaimish11 Over a year ago

I will update the field name to likers. It makes sense. How would you supply color and 'Black' as variables though?

jaimish11 Over a year ago

Does this mean I could also do: filter_field = variable_that_holds_color and the same for filter_string?

Endre Both Over a year ago

Python doesn't care what the variables are called. You can also run Item.objects.filter(**{variable_that_holds_field_name: variable_that_holds_color}) directly.

jaimish11 Over a year ago

Thank you! This method works and is definitely more robust than my raw SQL queries.

schillingt · Accepted Answer · 2019-03-27 13:07:32Z

0

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

edited Mar 27, 2019 at 13:07

schillingt

13.8k3 gold badges34 silver badges36 bronze badges

answered Mar 26, 2019 at 18:23

jaimish11

5645 silver badges18 bronze badges

6 Comments

schillingt Over a year ago

While I don't have the answer you want, this can't be the best way to do it. You should be able to do f'select ... {str(request.user.id)}) and {user_pref} ...' to avoid creating multiple strings. Why are you trying to do raw sql? The query looks pretty simple and you should be able to accomplish that with the ORM.

jaimish11 Over a year ago

Yes this is definitely not the best way. I was having trouble constructing the same dynamic query with ORM. Couldn't figure out how to use variables for my field names.

schillingt Over a year ago

If you post your models, I can help with that.

jaimish11 Over a year ago

@schillingt Added models.py in the question.

jaimish11 Over a year ago

@schillingt I didn't try fstrings. It's worth a shot.

|

Collectives™ on Stack Overflow

Why is this Django raw SQL query not returning output?

2 Answers 2

4 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related