CODEWITHSUNDEEP
sqlpostgresqlscalatuplesscalikejdbc
Niro
Niro

Reputation: 443

Scala, sql interpolation for tuples

I have a query that looks something like that in normal sql

Select * from persons 
where (persons.first_name, persons.last_name) 
in (('a', 'b'), ('c', 'd'))

I am trying to execute this from within scala 

val names = List(("James", "hasi"), ("Michael", "Myers))
sql""" Select * from ${table}     where (first_name, last_name) IN ${names}""".stripMargin.map(...)

However it is failing since scalikejdbc doesn't know how to interpolate a tuple.

"PSQLException: Can't infer the SQL type to use for an instance scala.Tuple2"

Any idea how to achieve that? I guess I can always transform the list of tuple into a string but the problem would be that scala will wrap it with single quotations, dealing with everything as a string.

Upvotes: 2

Views: 516

Answers (2)

Niro
Niro

Reputation: 443

I will post this as an answer because it solves the problem but with a different way. I ended up writing raw SQL in scalikejdbc without escaping the tuple "names"

You can do that by using SQLSyntax class

val a = SQLSyntax.createUnsafely(names)
sql"${a}" <- not escaped

Upvotes: 1

uh_big_mike_boi
uh_big_mike_boi

Reputation: 3470

Hi I have two suggetions -

  1. Have you tried other types? Or do you have to use a tuple? Maybe quickly try a Sequence or a Set, etc.

  2. Also it looks like you are using raw interpolation. I'm not sure of the API you are using but it looks like you might want to use just one quotation mark like -

    sql"Select * from ${table} where (first_name, last_name) IN ${names}".stripMargin.map(...)

Edit:
Could you try to map over the list or is that too hacky? Something like names.map((fname, lname) => sql""" Select * from ${table} where first_name = $fname and last_name = lname""".stripMargin).for(r <- rs) yield r

Upvotes: 0

Related Questions