How to create a cql query with order by in cassandra using datastax driver session?

3
votes

I am trying to do a following thing:

private val preparedFindQuery = session.prepare {
s"""
   |SELECT hash, art_index, operating_system, height, width, resolution, orientation, brand, model, networktype, network, aggcount
   |FROM $keyspace.$table
   |WHERE hash = :hash
   |  AND art_index >= :from_art_index
   |  AND art_index <= :to_art_index
   |  ORDER BY :orderCol :order
 """.stripMargin.trim
}

Unfortunately I get the following error:

line 6:11 no viable alternative at input ':' (... <= :to_art_index  ORDER BY [:]...)
com.datastax.driver.core.exceptions.SyntaxError: line 6:11 no viable alternative at input ':' (... <= :to_art_index  ORDER BY [:]...)

Is it somehow possible to make this work or it is impossible to use order by programatically ? Thanks in advance

2
cassandracqldatastax
The only thing you can change with ORDER BY is the sort direction (ASC/DESC). So if your default clustering order is correct for your query, then you don't even need ORDER BY here. – Aaron
what if i add an index on a specific column? won't i still be able to order by it ? – homar
Nope. stackoverflow.com/questions/25920078/… – Aaron
ok, thanks for explaining, i thought i could get away with this :) – homar

2 Answers

3
votes

You cannot use named parameters for the 'order by' criteria since it requires a column name and not a value/bind marker (?, :name) . You will need to provide the actual column names you wish to order your data by in part of the prepared statements and as BryceAtNetwork23 mentioned certain columns cannot be ordered on.

1
votes

You could create a CUSTOM INDEX based on Lucene framework (just a library) and then to sort by any column you want like this example:

SELECT * FROM tweets WHERE expr(tweets_index, '{
filter : [ {type: "range", field: "time", lower: "2014/04/25", upper: "2014/05/01"},
           {type: "prefix", field: "user", value: "a"} ],
query : {type: "phrase", field: "body", value: "big data gives organizations", slop: 1},
sort : {field: "time", reverse: true}') limit 100;

Find details and examples here cassandra-lucene-index