Django queryset select where concat

Question

i have an sql query like this:

select *
from sys_user_promotion_record
where user_id=102 and concat(',' ,vendor_id, ',') like '%,1,%'

how can i make that query into django queryset? so far i manage to make this django queryset:

SysUserPromotionRecord.objects.filter(user_id=102, vendor_id__contains=','+'1'+',')

how can i make concat(',' ,vendor_id, ',') into django queryset? i avoid to use raw sql query on my code. Thank you.

This is the vendor_id value sample:

1,11,14
2,1,5
3,5,11

When the sql executed, row 1 and 2 will appear.

why on earth are you doing this? ` concat(',' ,vendor_id, ',') like '%,1,%'` — e4c5, Aug 15 '16 at 08:14
@e4c5 it's a little hard to explain. the idea is to distinguish 1 from 11 or 12 or 13 etc. so i make 3,5,11 into ,3,5,11, so when i query where vendor_id like '%,1,%', vendor_id 3,5,11 will not be appear. — Krisnadi, Aug 15 '16 at 08:42
so why can't you just query for 1? 1 and 11 are different you know :) — e4c5, Aug 15 '16 at 08:43
@e4c5 for example?? can you give me more simple query? my vendor_id is a string — Krisnadi, Aug 15 '16 at 08:44
your question in it's present form is actually a good candidate for 'unclear what you are asking' and 'non MVCE" type closures. You need to explain your problem better. Give you models. for example — e4c5, Aug 15 '16 at 08:46
Are you actually storing CSV values in feilds. Eg: is 1,11,14 a single unique vendor id or does that represent three vendords with ids of 1, another with 11 and another with 14 — e4c5, Aug 15 '16 at 08:46

Resley Rodrigues · Accepted Answer · 2016-08-16T11:58:45.137

1

What you are doing should work, but I would assume vendor_id to be an integer and therefore not have any commas. If this is true your query will always return an empty queryset.

If you want to query vendor_id IN (vendor_id1, vendor_id12, ...) then the query is .filter(vendor_id__in=list_of_vendor_ids)

But if your vendor id is indeed a string and can contain commas your query should work but you'll need to handle edge cases too, such as '...,11', '11', and '11,...'. Therefore I suggest something like this

from django.db.models import Q
vendor_query = Q(vendor_id__iregex=r'^{0},|,{0},|,{0}$|^{0}$'.format(vendor_id))
SysUserPromotionRecord.objects.filter(vendor_query, user_id=102)

Edit: Seeing your changes it looks like the first part of the answer can be ignored.

Edit: Updated the regex as the previous one r',?{},?' would return wrong results for cases like returning 11 while searching for 1

edited Aug 16 '16 at 11:58

answered Aug 15 '16 at 07:50

Resley Rodrigues

2,218
17
17

i'm not quite understand this part: `Q(vendor_id__iregex=r',?{},?'.format(vendor_id))`. where i suppose to get the `vendor_id` from? – Krisnadi Aug 15 '16 at 09:20
Sorry. `vendor_id` in your case is `11` so your regex can be `r',?11,?'` I had put it as a variable thinking that 11 was just an example and you could have different values for it – Resley Rodrigues Aug 15 '16 at 09:43
The regex will basically match 0 or 1 comma followed by 11 followed by 0 or 1 comma – Resley Rodrigues Aug 15 '16 at 09:44
i tried this code in django, but it shows different row count from mysql query console. – Krisnadi Aug 16 '16 at 08:26
You MySQL query only selects those rows that have **,11,**. It misses all those that start with 11, end with 11, or have only 11. For example 3,5,11 – Resley Rodrigues Aug 16 '16 at 08:28
i think my sql query is correct. because it shows record that have vendor_id `3,5,11` when i do this query `where concat(',' ,vendor_id, ',') like '%,11,%'` – Krisnadi Aug 16 '16 at 08:43
Ah... you're always concatenating commas on both ends. Can you tell me which query returns more results? Also which record gets skipped? – Resley Rodrigues Aug 16 '16 at 09:35
yes, that's right. when i do this `vendor_query = Q(vendor_id__iregex=r',?{},?'.format(1))` it return 2 records, record with vendor_id '1' and record with vendor_id '11'. when i use mysql query console, it return only record with vendor_id '1'. – Krisnadi Aug 16 '16 at 10:57
when i do this `vendor_query = Q(vendor_id__iregex=r‌',?{},?'.format(',1,'))` no record appear in django. – Krisnadi Aug 16 '16 at 11:05
This is a patch work solution at best. The only real solution is to normalize the database. This is going to become progressively slower – e4c5 Aug 17 '16 at 05:44
thank you, it works. is this Q query safe from sql injection? or should i escape the parameter? – Krisnadi Aug 18 '16 at 04:29
should i add `vendor_id__icontains=vendor_query` ? – Krisnadi Aug 18 '16 at 04:53
Krisnadi you should normalize your database as @e4c5 said. I'm not sure if the query is safe from SQL injection. And you don't need to do `vendor_id__icontains` as `vendor_id__iregex` does a similar thing with regular expressions instead. `vendor_query` is the entire query expression (along with the equal to sign) – Resley Rodrigues Aug 18 '16 at 07:42
@ResleyRodrigues thanks. i will tell the db concept maker later. – Krisnadi Aug 18 '16 at 08:09
generally queries that use the ORM functions are safe from sql injection. You need to worry only when you use raw sql – e4c5 Aug 18 '16 at 08:13

score 1 · Answer 2 · edited May 23 '17 at 12:08

In mysql queries of the type like '%,1,%' are database killers.

A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators. The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character.

The fundamental problem here is that you have not normalized your database. The reason why you shouldn't store your data like this is explained very well here: Is storing a delimited list in a database column really that bad? Your first priority is to normalize your database.

As a temporary patch look at the django Concat function.

http://dev.mysql.com/doc/refman/5.7/en/index-btree-hash.html

thank you for your suggestion. i now realize this db structure is bad. i'm not the one who made the db. but, later i will consult this with him. thank you — Krisnadi, Aug 18 '16 at 04:55

Django queryset select where concat

2 Answers2