CODEWITHSUNDEEP
slick
emote_control
emote_control

Reputation: 766

How do I filter an array against a list of strings in Slick

I've got a column containing an array of varchar, and a list of search strings that I want to match against the column. If any of the search strings match any substring in the column strings, I want to return the row.

So for example if the column contains:

row 1: ['butter', 'water', 'eggs']
row 2: ['apples', 'oranges']
row 3: ['chubby', 'skinny']

And my search strings are:

Set("ter", "hub")

I want my filtered results to include row 1 and row 3, but not row 2.

If I were writing this in plain Scala I'd do something like:

val rows = [the rows containing my column]
val search = Set("ter", "hub")
rows.filter(r => search.exists(se => r.myColumn.exists(s => s.contains(se))))

Is there some way of doing this in Slick so the filtering gets done on the DB side before returning the results? Some combination of LIKE and ANY, maybe? I'm a little fuzzy on the mechanics of filtering an array against another array in SQL in the first place.

Upvotes: 1

Views: 1255

Answers (1)

emote_control
emote_control

Reputation: 766

While I'm not convinced that this is the best way to do it, I've put together a solution that uses Regex. First, I concatenate the search terms into a simple regular expression:

val matcher = search.mkString(".*(","|",").*") // i.e. '.*(ter|hub).*'

Then I concatenate the array in the table column using an implicit SimpleExpression:

implicit class StringConcat(s: Rep[List[String]]){
    def stringConcat: Rep[String] = {
      val expr = SimpleExpression.unary[List[String], String] { (s, qb) =>
        qb.sqlBuilder += "array_to_string("
        qb.expr(s)
        qb.sqlBuilder += ", ',')"
      }
      expr.apply(s)
    }
  }

Finally, I build a regex query using another implicit SimpleExpression:

implicit class RegexQuery(s: Rep[String]) {
    def regexQ(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)
    }
  }

And I can then perform my match like:

myTable.filter(row => row.myColumn.stringConcat.regexQ(matcher))

Hope that helps someone out, and if you have a better way of doing it let me know.

Edit to add:

If you're looking for exact matches, and not partial matches, you can use the array overlap operator, like:

myColumn && '{"water","oranges"}'

In Slick this is the @& operator, like

.filter(table => table.myColumn @& myMatchList)

Upvotes: 2

Related Questions