0

I have a table in Cassandra with id as Primary Key and name, address, department as clustering key. Now I have following use cases:

  • Get users by id and name
  • Get users by id, name and address
  • Get users by id, name , address and department

As lower order clustering columns are optional in Cassandra query we are using a Custom accessor In Java for above use cases.

But we have to write 3 different queries in custom accessor.

For example:

select * from user where id=1 and name='A';

select * from user where id=1 and name='A' and address='a1';

select * from user where id=1 and name='A' and address='a1' and department='d1';

Does Java Driver for Cassandra provides any way so that I can handle all the above mentioned use cases by a single query?

Right now if I am writing a query with all clustering columns and For example if I am trying to get users by id and name only I am getting incorrect result as I cannot pass null and if I pass empty string then Cassandra filters the remaining clustering columns by empty string which is causing the incorrect results.

Yug Singh
  • 3,112
  • 5
  • 27
  • 52
  • 1
    Did you check [Built statements](https://docs.datastax.com/en/developer/java-driver/3.6/manual/statements/built/)? – Horia Oct 08 '18 at 14:09
  • @Horia Thanks for the suggestion. I had looked at it some time back but didn't pay much attention as I was looking for something else. I just went through it and seems it might be the thing I am looking for. I will try it out and let you know whether it worked. – Yug Singh Oct 08 '18 at 14:30
  • Any updates on this one? – Horia Oct 10 '18 at 07:26
  • @Horia I looked into it and I saw the same issue with it that I am trying to solve. For ex consider this `BuiltStatement select = QueryBuilder.select().from("test", "test") .where(eq("id", "1")).and(eq("txt", "test"));`. So I cannot dynamically add `and` and thus have to write the `built statement` again and again as per the scenarios I mentioned in the question. – Yug Singh Oct 10 '18 at 08:35
  • Most probably you must add the dynamic stuff in your code. Maybe having a Statement class that would generate the query using the QueryBuilder. The reason for not having this is that Cassandra works with predefined queries. I came across this, maybe is useful [Cassandra 3 Java Driver build dynamic queries](https://stackoverflow.com/questions/36427449/cassandra-3-java-driver-build-dynamic-queries) – Horia Oct 10 '18 at 09:03
  • In my case I have a table having primary key composed of 13 columns. I think the solution will work. The only concern I have is that there will be so many if-else statements. What are your views about that? – Yug Singh Oct 10 '18 at 09:11
  • You could have a concurrenthashmap with column name as key and the value as value. You would iterate over it with a for and add to your builder. – Horia Oct 10 '18 at 09:55
  • Thanks @Horia. I will try it out. – Yug Singh Oct 10 '18 at 10:42
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/181612/discussion-between-horia-and-yug-singh). – Horia Oct 10 '18 at 11:08

0 Answers0