Slick list mappings for like condition

Question

I have a table that has a column with values:

column
------
a
ab
abc
b
bc
bca
c
cba

I have a list = [a, b, bc], I want all the rows from the table which starts from any element in list.

column
------
a
ab
abc
b
bc
bca

How do i form queryCondition using slick:

table = TableQuery[Table]

mapping  = list map
        {
          value =>
            table.column like value + "%"
        }

Richard Dallaway Richard Dallaway · Accepted Answer · 2020-10-08T16:37:53

As I understand it, you'd like a query of the form:

SELECT column FROM table WHERE 
   column LIKE 'a%' 
OR column LIKE 'b%' 
OR column LIKE 'bc%'

That translates into Slick as basically:

val query = table.filter(row => 
 row.column like 'a%' || row.column like 'b%' || row.column like 'bc%'
)

So now the question is how to construct the LIKE parts from a Scala List.

The standard collections library has a few ways to do that. Here's one (reduceLeft):

val patterns = Seq("a", "b", "bc").map(_ + "%")
val query = table.filter(row => 
 patterns.map(pattern => row.column like pattern).reduceLeft(_ || _)
)

What we're doing is:

turning the list of patterns into a list of Slick like expressions
then combining the list of Slick expressions with ||
this gives us one Slick expression (the likes combined with or) to use inside a filter.

Instead of like, you can also use startsWith to avoid having to add % to your list of strings:

val patterns = Seq("a", "b", "bc")
val query = table.filter(row => 
 patterns.map(pattern => row.column startsWith pattern).reduceLeft(_ || _)
)

Slick will convert this into a Sql LIKE expression.

Slick list mappings for like condition

1 Answers