3766
Question:
I basically would like to replace the following code with something more "slicky":
final case class User(firstName: String, lastName: String)
def dbAction(lastNameParts: Seq[String]): SqlStreamingAction[Vector[User], User, Effect]
implicit val getUserResult =
GetResult((r: PositionedResult) => {
val resultSet: ResultSet = r.rs
User(
resultSet.getString(1),
resultSet.getString(2)
)
})
val pattern = orgIds.mkString("|")
sql"""SELECT u.first_name, u.last_name
FROM users u
WHERE last_name ~* $pattern""".as[User]
So the resulting SQL would be:
SELECT u.first_name, u.last_name
FROM users u
WHERE last_name ~* '%bar|baz%';
So this dbAction will return an action which I can use to Query for all users having some name parts included in a list.
So
dbAction(Seq("bar", "baz"))
will return an action querying for all last names containing the strings "bar" or "baz" (case insensitive). I found a way to query for a single pattern
val query = for {
user <- users if user.lastName like "%bar%"
} yield (user.firstName, user.lastName)
and I found a way to query for list inclusion
u <- users if u.lastName.inSet(Seq("bar", "baz"))
but could not find a way to combine that
EDIT: Another way to possibly solve that would be via a regular expression. Is there a way to achieve something like the following SQL statement:
select * from users where last_name ~ '[\w]*bar[\w]*|[\w]*baz[\w]*';
Since this is somehow a different question how to use a regex I created a different question for this: <a href="https://stackoverflow.com/questions/46218122/slick-is-there-a-way-to-create-a-where-clause-with-a-regex" rel="nofollow">Slick: Is there a way to create a WHERE clause with a regex?</a>
Answer1:Looking at the code you've posted, I didn't think you needed to literaly combine an IN with a LIKE. I read the question as wanting to do a regular expression query. Although Slick doesn't support the ~* operator out of the box, you can add it yourself. That would give you a way to execute the query using the <em>lifted embedded</em> style of Slick query.
To do that, you can use the SimpleExpression builder. There's not much documentation on it, but the jumping off point would be the <a href="http://slick.lightbend.com/doc/3.2.1/userdefined.html#scalar-database-functions" rel="nofollow">Scalar Database Functions</a> page of the reference manual.
What we want to do is write a method along these lines:
def find(names: Seq[String]): DBIO[Seq[String]] = {
val pattern = names.mkString("|")
users.filter(_.lastName regexLike pattern).map(_.lastName).result
}
To get regexLike we can enrich (enhance, "pimp") a string column to have the regexLike method:
implicit class RegexLikeOps(s: Rep[String]) {
def regexLike(p: Rep[String]): Rep[Boolean] = {
val expr = SimpleExpression.binary[String,String,Boolean] { (s, p, qb) =>
qb.expr(s)
qb.sqlBuilder += " ~* "
qb.expr(p)
}
expr.apply(s,p)
}
}
The implicit class part is allow the compiler to construct the RegexLikeOps class anytime it has a Rep[String] that calls a method that Rep[String] doesn't already have (i.e., when regexLike is asked for).
Our regexLike method takes another Rep[String] argument as the pattern, and then uses SimpleExpression builder to safely construct the SQL we want to use.
Putting it all together we can write:
val program = for {
_ <- users.schema.create
_ <- users ++= User("foo") :: User("baz") :: User("bar") :: Nil
result <- find( Seq("baz","bar") )
} yield result
println( Await.result(db.run(program), 2.seconds) )
The SQL generated (in my test with H2) is:
select "last_name" from "app_user" where "last_name" ~* 'baz|bar'
The full code is: <a href="https://github.com/d6y/so46199828" rel="nofollow">https://github.com/d6y/so46199828</a>
Answer2:Just combine these 2 query conditions:
import slick.lifted.Tag
import slick.jdbc.H2Profile.api._
import scala.concurrent.duration._
import scala.concurrent.Await
object Test {
final case class User(firstName:String, lastName:String, id:Long = 0l)
class UserTable(tag: Tag) extends Table[User](tag, "user"){
def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
def firstName = column[String]("first_name")
def lastName = column[String]("last_name")
def * = (firstName, lastName, id).mapTo[User]
}
def main(args:Array[String]):Unit = {
val db = Database.forConfig("h2config")
val users = TableQuery[UserTable]
val initialData = Seq(
User("Alex", "Arendar"),
User("David", "Arora"),
User("Dude", "Stoecki"),
User("Alexander", "the Great")
)
Await.result(
db.run(
users.schema.create andThen (users ++= initialData)
), 3 seconds
)
val query1 = for {
user <- users if user.firstName like "%Alex%"
} yield (user.firstName, user.lastName)
println(query1.result.statements.head)
println(Await.result(db.run(query1.result), 3 seconds))
val query2 = for {
user <- users if (user.firstName like "%Alex%") && user.firstName.inSet(Seq("Alex", "David"))
} yield (user.firstName, user.lastName)
println(query2.result.statements.head)
println(Await.result(db.run(query2.result), 3 seconds))
db.close()
}
My console output is:
select "first_name", "last_name" from "user" where "first_name" like '%Alex%'
Vector((Alex,Arendar), (Alexander,the Great))
select "first_name", "last_name" from "user" where ("first_name" like '%Alex%') and ("first_name" in ('Alex', 'David'))
Vector((Alex,Arendar))
Process finished with exit code 0
As you can see generated SQL does contain both like and in parts.
I used in-memory H2 database here but I think this shall work with any RDBMS.
