Cassandra (CQL) select IN query with Cassandra4IO-CodePudding

I am using scala with Cassandra4io library. I am trying to perform a select IN query. The parameter of IN is like a tuple (comma separated string values). And it has not worked for me. I tried different approaches.

// keys (List[String])
 val clientIdCommaSepValues = keys.mkString(",")
val selectValue = selectQuery(clientIdCommaSepValues)

 private def selectQuery(clientids: String) =
    cql"select * from clientinformation WHERE (clientid IN (  ${clientids} ))".as[CassandraClientInfoRow]

this worked only when the value is one (length of keys is 1).


  private val selectQuery =
    cqlt"select * from clientinformation WHERE (clientid IN ${Put[String]}) ".as[CassandraClientInfoRow]

I also tried to put ' ' quotes on the strings.

CodePudding user response：

sorry for the delay on this. It turns out that adding that extra set of parenthesis around your value (in the example above IN (${clientIds})) throws off the string interpolator leading it to select the wrong Binder datatype which is used to serialize the datatype in your query before it sends it off to Cassandra (ouch!).

This selected TEXT instead of List[TEXT]

What you want to do instead is reformulate the query like so:

val keys: List[String] = ???
val selectValue = selectQuery(keys)

 private def selectQuery(clientids: List[String]) =
    cql"select * from clientinformation WHERE clientid IN ${clientids}".as[CassandraClientInfoRow]"""

I was able to reproduce this on my end and drop the parens. Here's what I did

CREATE KEYSPACE example WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 };

CREATE TABLE IF NOT EXISTS test_data (
    id TEXT, 
    data INT, 
    PRIMARY KEY ((id))
);

package com.ringcentral.cassandra4io

import cats.effect._
import com.datastax.oss.driver.api.core.CqlSession
import com.ringcentral.cassandra4io.cql._
import fs2._

import java.net.InetSocketAddress
import scala.jdk.CollectionConverters._

object Investigation extends IOApp {
  final case class TestDataRow(id: String, data: Int)

  def insert(in: TestDataRow, session: CassandraSession[IO]): IO[Boolean] =
    cql"INSERT INTO test_data (id, data) VALUES (${in.id}, ${in.data})"
      .execute(session)

  override def run(args: List[String]): IO[ExitCode] = {
    val rSession = {
      val builder =
        CqlSession
          .builder()
          .addContactPoints(List(InetSocketAddress.createUnresolved("localhost", 9042)).asJava)
          .withLocalDatacenter("dc1")
          .withKeyspace("example")

      CassandraSession.connect[IO](builder)
    }

    rSession.use { session =>
      val insertData: Stream[IO, INothing] =
        Stream.eval(insert(TestDataRow("test", 1), session) *> insert(TestDataRow("test2", 2), session)).drain

      def query(ids: List[String]): Stream[IO, TestDataRow] =
        cql"SELECT id, data FROM test_data WHERE id IN $ids"
          .as[TestDataRow]
          .select(session)

      (insertData    query(List("test", "test2")))
        .evalTap(i => IO(println(i)))
        .compile
        .drain
        .as(ExitCode.Success)
    }
  }
}

This works great since now it selects the right Binder which is List(TEXT) as you can see above! Sorry for the trouble you had and the cryptic error messages but thank you for using this library :D