How to update table in Phantom for cassandra Scala

Question

I created following table for Cassandra

abstract class MessageTable extends Table[ConcreteMessageModel, Message] {

  override def tableName: String = "messages"

  // String because TimedUUIDs are bad bad bad
  object id extends Col[String] with PartitionKey {
    override lazy val name = "message_id"
  }

  object phone extends Col[String]

  object message extends Col[String]

  object provider_message_id extends Col[Option[String]]
  object status extends Col[Option[String]]

  object datetime extends DateColumn {
    override lazy val name = "message_datetime"
  }

  override def fromRow(r: Row): Message = Message(phone(r), message(r), Some(UUID.fromString(id(r))), None, status(r), Some( ZonedDateTime.ofInstant(datetime(r).toInstant, ZoneOffset.UTC) ))
}

In above table, I want to be able to update the table based on id or provider_message_id.

I can easily update the row using id

update().where(_.id eqs message.id)...

But I can't update the table using provider_message_id

update().where(_.provider_message_id eqs callback_id)...

How can I use multiple fields to update the table in cassandra

Answer 1

There is a restriction with Cassandra updates is that they will work only with the primary key. The primary key can be one column (named partition key), or multiple columns (a partition key, and one or many clustering keys).

In the case that you are providing, you need to ensure that both id and provider_message_id are part of the primary key, the description of the table with cql should be something similar to:

cqlsh:> DESCRIBE keyspace1.messages;
...
CREATE TABLE keyspace1.messages (
    id text,
    phone text,
    message text,
    provider_message_id text,
    status text,
    datetime date,
    PRIMARY KEY (id, provider_message_id)
) WITH CLUSTERING ORDER BY (provider_message_id ASC)
    ...

Also, please note that you will need to use id and provider_message_id in all the update queries (there is no update by id or provider_message_id). Your code will look as:

update().where(_.id eqs message.id).and(_.provider_message_id eqs callback_id)...