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
– Vaibhav Vishal, Commented Sep 28, 2018 at 6: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
– TARUN SHARMA, Commented Sep 28, 2018 at 6:54
my question like [stackoverflow.com/questions/9416062/sql-like-inside-array] — TARUN SHARMA
– TARUN SHARMA, Commented Sep 28, 2018 at 7:01

Vaibhav Vishal · Accepted Answer · 2018-09-28 07:12:43Z

2

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)

answered Sep 28, 2018 at 7:12

Vaibhav Vishal

7,2487 gold badges32 silver badges51 bronze badges

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

1 Comment

Vaibhav Vishal Over a year ago

happy to help.😊

tarun sharma · Accepted Answer · 2018-09-28 07:12:26Z

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, 2018 at 7:12

tarun sharma

577 bronze badges

1 Comment

Vaibhav Vishal Over a year ago

same name, different guy?

Collectives™ on Stack Overflow

SQL LIKE query inside array in Django python

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related