How do I query an optional column with a secondary index using phantom?

Question

I have a secondary index on an optional column:

class Sessions extends CassandraTable[ConcreteSessions, Session] {
  object matchId extends LongColumn(this) with PartitionKey[Long]
  object userId extends OptionalLongColumn(this) with Index[Option[Long]]
  ...
}

However, the indexedToQueryColumn implicit conversion is not available for optional columns, so this does not compile:

def getByUserId(userId: Long): Future[Seq[Session]] = {
  select.where(_.userId eqs userId).fetch()
}

Neither does this:

select.where(_.userId eqs Some(userId)).fetch()

Or changing the type of the index:

object userId extends OptionalLongColumn(this) with Index[Long]

Is there a way to perform such a query using phantom?

I know that I could denormalize, but it would involve some very messy housekeeping and triple our (substantial) data size. The query usually returns only a handful of results, so I'd be willing to use a secondary index in this case.

Thiago Pereira Thiago Pereira · Accepted Answer · 2015-12-22T12:16:07

Short answer: You could not use optional fields in order to query things in phantom.

Long detailed answer:

But, if you really want to work with secondary optional columns, you should declare your entity field as Option but your phantom representation should not be an option in order to query.

object userId extends LongColumn(this) with Index[Long]

In the fromRow(r: Row) you can create your object like this:

Sessions(matchId(r), Some(userId(r)))

Then in the service part you could do the following:

.value(_.userId, t.userId.getOrElse(0))

You also have a better way to do that. You could duplicate the table, making a new kind of query like sessions_by_user_id where in this table your user_id would be the primary key and the match_id the clustering key.

Since user_id is optional, you would end with a table that contains only valid user ids, which is easy and fast to lookup.

Cassandra relies on queries, so use it in your favor.

Take a look up on my github project that helps you get up with multiple queries in the same table.

https://github.com/iamthiago/cassandra-phantom

How do I query an optional column with a secondary index using phantom?

1 Answers