Fork me on GitHub
#beginners
<
2024-06-01
>
m3tti13:06:06

Hey guys i guess i'm just dumb 😄 but how do i use next.jdbc with in in query something like (jdbc/execute! db ["SELECT * FROM companies WHERE feedname = 'indeed' AND city in ? LIMIT ? OFFSET ?" cities-in-radius limit offset])

bibiki14:06:29

can you give a row of data from your companies table and a query that you'd expect to return that row? it appears like cities-in-radius has shape different than column city. i'd expect this to work for something like city in ["one city" "another city"], but your city-in-radius is probably a single number that limits how far from current location you want to search for cities

m3tti14:06:35

yeah i managed it with doing something like WHERE location IN (?) and gave the ? as joined string. So (str/join ", " cities)

m3tti14:06:55

BUT now i see the location column can consist of concatenated lists of cities -.-

m3tti14:06:02

so what can i do now 😄

m3tti14:06:02

so its location that has City1, city2, city3 and my data i have is city4, city2, city8 and it should match when one city is equal so basically an intersection

bibiki14:06:21

right, intersection... i dont know if you can do that at sql level

m3tti14:06:47

Yeah i'm currently searching 😄 there is an intersection command BUT its just for joining two tables 😕

👍 1
m3tti14:06:40

ok found a poor mans solution 😄 i create the query on my own with locate and map through my list and see if one value of my list can be located in the string from the location field.

m3tti14:06:33

like this

[(str "SELECT * FROM companies WHERE feedname = 'indeed' AND "
             (->>
              cities-in-radius
              (map #(str "LOCATE('" % "', location) > 0"))
              (str/join  " OR "))
             " LIMIT ? OFFSET ?") limit offset]
I know this is not save for sql injection BUT the city values come from another location table and is not modifyable by a user

bibiki14:06:24

cool, that is some progress

bibiki14:06:37

but since you have cities in another table, perhaps you can have a join instead of retrieving cities from that table first and then running another query in your companies table