SQL LIKE query inside array in Django python

Question

I need to use like query in array i have array in keywords_array : ["rk.keywords LIKE '%%Donut%%'", "OR rk.keywords LIKE '%%Pizza%%'"]

I need to append this in raw_query at last in where condition but it is being returning single array (OR rk.keywords LIKE '%%Pizza%%'):

Views.py

                keywords_array=[]
                for i in keyword:
                    keywords={}
                    if keyword[0]==i:
                        keywords="rk.keywords LIKE '%%"+i+"%%'"
                    else:
                        keywords="OR rk.keywords LIKE '%%"+i+"%%'"
                    keywords_array.append(keywords)
                for t in keywords_array:
                    raw_query="SELECT r.id,( 3959 * Acos(Cos(Radians(" +lat +")) * Cos(Radians(lat)) * Cos( Radians(lng) - Radians("+lng+")) + Sin (Radians("+lat+")) * Sin(Radians(lat)))) AS distance FROM backend_restaurant r LEFT JOIN backend_restaurant_keywords m2m ON m2m.restaurant_id = r.id LEFT JOIN backend_restaurantkeyword rk ON m2m.id = rk.id WHERE "+ t +""

print(raw_query)

SELECT r.id,( 3959 * Acos(Cos(Radians(30.704649)) * Cos(Radians(lat)) * Cos( Radians(lng) - Radians(76.717873)) + Sin (Radians(30.704649)) * Sin(Radians(lat)))) AS distance FROM backend_restaurant r LEFT JOIN backend_restaurant_keywords m2m ON m2m.restaurant_id = r.id LEFT JOIN backend_restaurantkeyword rk ON m2m.id = rk.id WHERE OR rk.keywords LIKE '%%Pizza%%'

your raw queries are in if else condition, so only one is inside your array — Vaibhav Vishal, Sep 28 '18 at 06:29
This condition is to append OR in query is it returning me array ["rk.keywords LIKE '%%Donut%%'", "OR rk.keywords LIKE '%%Pizza%%'"] — TARUN SHARMA, Sep 28 '18 at 06:54
my question like [https://stackoverflow.com/questions/9416062/sql-like-inside-array] — TARUN SHARMA, Sep 28 '18 at 07:01

score 2 · Accepted Answer · answered Sep 28 '18 at 07:12

See comments in code for explanation of what were you doing wrong.

keywords_array=[]
for i in keyword:
    # keywords={} # unnecessary i think
    if keyword[0]==i:
        keywords="rk.keywords LIKE '%%"+i+"%%'"
    else:
        keywords="OR rk.keywords LIKE '%%"+i+"%%'"
    keywords_array.append(keywords)

# your variable raw_query was inside for loop and so its value was getting overwritten every loop,
# therefore it contained the last element of keywords_array only
# so define the contents of string which you always want to keep outside for loop
raw_query="SELECT r.id,( 3959 * Acos(Cos(Radians(" +lat +")) * Cos(Radians(lat)) * Cos( Radians(lng) - Radians("+lng+")) + Sin (Radians("+lat+")) * Sin(Radians(lat)))) AS distance FROM backend_restaurant r LEFT JOIN backend_restaurant_keywords m2m ON m2m.restaurant_id = r.id LEFT JOIN backend_restaurantkeyword rk ON m2m.id = rk.id WHERE "

# Now use '+=' to append new text to end of string the string instead of '=' which replaces the string
for t in keywords_array:
    raw_query += t + " "  # update the string instead of overwriting it

print(raw_query)

score 1 · Answer 2 · answered Sep 28 '18 at 07:12

1

Your question is something like this :

You have an array Keywords=["Donut", "Pizza", "Burger"]

and you want this in a loop

SELECT * FROM items WHERE name LIKE '%Pizza%' 
                       OR name LIKE '%Donut%' 
                       OR name LIKE '%Burger%';

answered Sep 28 '18 at 07:12

tarun sharma

57
7

1

same name, different guy? – Vaibhav Vishal Sep 28 '18 at 07:19

SQL LIKE query inside array in Django python

2 Answers2