Create Cassandra @Query for one or more @Params

Question

I am trying to perform a SELECT on a cassandra database, using the datastax driver on a Java App.

I already developed simple queries as:

@Repository
public class CustomTaskRepository
    extends AbstractCassandraRepository<CustomTask> {

    @Accessor
    interface ProfileAccessor {
        @Query("SELECT * FROM tasks where status = :status")
        Result<CustomTask> getByStatus(@Param("status") String status);
    }

    public List<CustomTask> getByStatus(String status) {
        ProfileAccessor accessor = this.mappingManager.createAccessor(ProfileAccessor.class);
        Result<CustomTask> tasks = accessor.getByStatus(status);
        return tasks.all();
    }
}

Thats works great.

The problem I have now is that I want to execute a SELECT statement for more than one status. For example I would like to execute the query for one, two ... or more status codes (Pending, Working, Finalized,etc).

How could I create a @Query Statement with the flexibility of accepting one or more Status codes?

Thanks in advance!!!

EDIT: The table create statement is:

CREATE TABLE tasks(
"reservation_id" varchar,
"task_id" UUID,
"status" varchar,
"asignee" varchar,
PRIMARY KEY((reservation_id),task_id)
)

WITH compaction = {'class': 'org.apache.cassandra.db.compaction.LeveledCompactionStrategy'} ;


CREATE INDEX taskid_index ON tasks( task_id );
CREATE INDEX asignee_index ON tasks( asignee );

score 2 · Answer 1 · answered Feb 02 '17 at 14:14

2

Try using IN instead of = . If this is partitioning key you will get the rows that you need out. Also note that it might cause performance degradation if there are a lot of statuses in in.

answered Feb 02 '17 at 14:14

Marko Švaljek

2,071
1
14
26

Thanks for the answer. Yes the error displaying is: IN predicates on non-primary-key columns (asignee) is not yet supported. There is another workound? – melli-182 Feb 02 '17 at 14:24
1

if this is not working then you will have to use multiple querries (known thing with cassandra) could you please just show me the create statement ... just to check. I looked some of the docs, the in operator is supported at least directly in cql so you can use regular statements then or have multiple calls to statements. Plus there is a similar question here http://stackoverflow.com/questions/27432139/use-datastax-java-driver-to-bind-data-for-where-in-clause But it only works on partitioning key so I need your create statement ;) – Marko Švaljek Feb 02 '17 at 14:28
1

This doesn't work with secondary indexes, in operator is only possible with paritioning key. Indexes like status are usually also a bad idea because they have a lot of instances per relatively small occurance of stuff (there are probably only a few statuses and they contain a lots of rows). I would advise you to model the data per access. Also do you really need the Leveled compaction? it is pretty bad if you plan to update stuff and if you have "status" field you probably intend on. – Marko Švaljek Feb 02 '17 at 14:36
OK! Thanks for the help! I am going to look for other options or workarounds! – melli-182 Feb 02 '17 at 14:38
Basically create and model a table for everything that you need to access at write time. Here is a nice intro to what you need. http://www.slideshare.net/pygnosis/cassandra-advanced-data-modeling just as a heads up usually people use indexes during development time, in production they are most of the time trouble. – Marko Švaljek Feb 02 '17 at 14:41

Create Cassandra @Query for one or more @Params

1 Answers1