Why "LIKE" , "Contain", "REGIX" Operators are not working in phentom-dsl library ( scylladb )?

Question

I'm using scala language and scylladb database using phantom-dsl library.

I want to get the data of students from scylladb on the bases of city by using method ,

override def getStudnetByCity(city: String): Future[List[Student]] = {
studentDB.Student
   .select
   .where(_.city eqs city)
   .fetch()
}

The above code working fine.

Now what I want to get all the student from city JERSEY as well as NEW JERSEY and WESTERN NEW JERSEY by only searching JERSEY using the given code

override def getStudnetByCity(city: String): Future[List[Student]] = {
studentDB.Student
   .select
   .where(_.city like s"%$city%")
   .fetch()
} 

but getting error at like

Error: Cannot resolve symbol like

what I tried: I import library:

import com.outworkers.phantom.builder.syntax.CQLSyntax.Operators.like

but nothing happened and still getting the same error.

here is my build.sbt:

ThisBuild / scalaVersion := "2.13.1"

libraryDependencies ++= Seq(
  "com.outworkers" %% "phantom-dsl" % "2.59.0"
)

Is phontom doesn't suppor like or is there another way to do so?

Answer 1

EDIT:

I wrote a well detailed step by step "solution". The problem is that the question was for ScyllaDB and my approach will only work for Cassandra due to ScyllaDB doesn't support SASI Index. Also SASI index in Cassandra was experimental, it will be deprecated in v5 and removed in v6.

@NadavHar'El answer is the right one for this case. I will just add an example of how to follow his suggestion using phantom.

• phantom - primitives
• phantom - secondary indexes

import com.outworkers.phantom.dsl._

abstract class Students extends Table[Students, Student] {
  object id extends UUIDColumn with PartitionKey
  object name extends StringColumn
  object email extends StringColumn
  object city extends ListColumn[String] with Index

  def cityLike(city: String) = {
    select.where(student => student.city eqs city).fetch()
  }
}

---

OLD ANSWER: (Using an experimental feature from Cassandra that will be deprecated)

To be able to do that, phantom lets you add SASI Index support. Based on the code you provided, your table definition should be something like

import com.outworkers.phantom.dsl._

case class Student(
  id: UUID,
  name: String,
  email: String,
  city: String
)

abstract class Students extends Table[Students, Student] {
  object id extends UUIDColumn with PartitionKey
  object name extends StringColumn
  object email extends StringColumn
  object city extends StringColumn // the column you want to filter using `LIKE` operator

  def cityLike(city: String) = {
    select.where(student => student.city eqs(city)).fetch()
  }
}

As it says in the docs in the Modes section:

There are two modes directed specifically at text columns, namely Mode.Prefix and Mode.Contains. By using these modes, you will be able to perform text specific queries using the like operator.

The next step you have to do is add the SASI support for the column you want to filter

import com.outworkers.phantom.dsl._

abstract class Students extends Table[Students, Student] {
  object id extends UUIDColumn with PartitionKey
  object name extends StringColumn
  object email extends StringColumn
  object city extends StringColumn with SASIIndex[Mode.Contains] { // adding SASI Index using MODE.Contains
    override def analyzer: Analyzer[Mode.Contains] = // the analyzer must be override based on your needs
      Analyzer.NonTokenizingAnalyzer[Mode.Contains]().normalizeLowercase(true)
  }

  def cityLike(city: String) = {
    // once you added the SASI index to the column, you can filter
    // the column using the operator `LIKE`
    select.where(student => student.city like contains(city)).fetch()
  }
}

The SASI index must exist.

• You can create the SASI index using CQL

CREATE CUSTOM INDEX ON students (city) 
                 USING 'org.apache.cassandra.index.sasi.SASIIndex'
                  WITH OPTIONS = {
                      'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer',
                      'case_sensitive': 'false'
                  };

• you can use the schema generation provided by phantom

In some of your app you will have something like

class AppDatabase(override val connector: CassandraConnection)
    extends Database[AppDatabase](connector) {
  object students extends Students with Connector

  def createStudentsTable = students.create.ifNotExists()
}

• or create a custom index using scylla java driver

to be able to do that you have to add the correct dependency

"com.scylladb" % "java-driver-query-builder" % "4.15.0.0"

and then you can do the following

import com.datastax.oss.driver.api.querybuilder.SchemaBuilder.createIndex

object CustomIndex {
  val citySasiIndex = createIndex()
    .usingSASI()
    .onTable("students")
    .andColumn("city")
    .withSASIOptions(
      ImmutableMap.of("mode", "CONTAINS", "tokenization_locale", "en")
    )
}

---

backing to your initial question phantom supports like operator?, the answer is Yes. It is possible to do it. You should have in mind that you have to do some extra steps and evaluate the trade-offs between using one or other type of indexes. For further information about SASI Index, I've found this other posts in stackoverflow:

• Regular expression search or LIKE type feature in cassandra
• Is there any query for Cassandra as same as SQL:LIKE Condition?

Answer 2

ScyllaDB supports the LIKE operator, exactly like the one you tried to use, but only in filtering selects (with ALLOW FILTERING), which means the select reads all the data (of a single partition or the entire database) and checks each row whether it matches your LIKE operator.

Clearly, for many use cases such "filtering" is inefficient and inappropriate, and you want an index to be prepared in advance to make them efficient. Gaston's answer recommended that you should use SASI, Cassandra's advanced text-search secondary index, but unfortunately this was never implemented in ScyllaDB (https://github.com/scylladb/scylladb/issues/2203) and now that this feature was removed from Cassandra too, it never will be implemented in ScyllaDB.

ScyllaDB has another proposal, also not done yet, to add index support for efficient LIKE operations without adding a completely generic indexing mechanism: https://github.com/scylladb/scylladb/issues/6458. But this isn't available yet either.

There's something you can do in your application, however, in existing ScyllaDB: You can the so-called lemmatization yourself: Instead (or in addition to) of storing the string "WESTERN NEW JERSEY" store, in a list column the like ["western", "new", "jersey"]. You can then add a secondary index on this list column (ScyllaDB supports this since August 2022), and then you can efficiently search for "jersey" and get the relevant results.

I have no idea, however, how to do any of this with phantom-dsl. I guess this framework simple translates various Scala objects to CQL commands, so you just need to figure out what is the correct way to pass the CQL command you want to pass.