3766

Slick: How can I combine a SQL LIKE statement with a SQL IN statement

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.

Recommend

  • Substatemachine
  • Issue with terraform lookup
  • How to get .mpd file for a youtube video
  • How to change textview height constraint within table view cell?
  • Textarea toolbar?
  • Spring Security sec:authorize tag using Java Config
  • Why does IntStream.range(0, 100000).parallel.foreach take longer then normal for loop
  • how can i open a webpage in a webview from a fragment with buttons?
  • Missing App Store icon codename one build
  • Get Network Interface Names and Set All to DHCP Batch Script
  • Using simplemodal with wordpress
  • How can I know the service name?
  • Why does packing not work across sibling unions or structs
  • JavaFX resize children relative to parent
  • Encode string to Base64 in Inno Setup (Unicode Version of Inno Setup)
  • MySql tables, error #1064 & error #1068 Multiple primary key defined [closed]
  • How to order the ties in data so that the previously observed value appears first
  • In Moment.js, how do you get the date of the next occurrence of a specific month (ex: 'next Jan
  • using vtkTimerCallback with QVTKRenderWindowInteractor not working
  • Nested AJAX Calls using .done
  • SyntaxError: expected expression, got '.'
  • How to run Daphne Server (Django Channels) & workers in the background?
  • Adding Dynamic Row and Data on Checkbox Click
  • Error processing job: Project has not enabled BigQuery
  • How to search for a method or variable name within android project?
  • Smarter Removing Unnecessary WhiteSpace CSV
  • How to process remote XML files with XSLT
  • First dynamically-added TinyMCE editor displays, others do not
  • How to use AJAX to upload large CSV file? [closed]
  • Wireshark Display Filter for Unique Source/Destination IP and Protocol
  • What is the difference between dynamically creating a script tag and statically embed a script tag?
  • Regex not working in java 1.5
  • Typeahead.js does give me suggestions but doesn't select them
  • Is there a better way for handling SpatialPolygons that cross the antimeridian (date line)?
  • Cloud Code: Creating a Parse.File from URL
  • How to call jQuery function in HTML returned by AJAX
  • How do I add a mouse over tooltip to an Image using .DrawImage()
  • Using redis as an LRU cache for postgres
  • How to warp text around image in iOS?
  • Bad automatic Triangulation with Mayavi for coloring a surface known only by its corner