Multi-Column Sorting with Cassandra/CQL

[Mohammed Abuharb]

Dears,

One of my tables contains 15 columns. and I want to sort the results from DB based on different criteria/columns, but that's not working with Cassandra.

Do you have any idea how I can work around this problem (i.e via php-driver API)?

Regards,

[Michael Fero]

Mohammed,

If you are unable to use CLUSTERING ORDER (http://cassandra.apache.org/doc/latest/cql/ddl.html#table-options) in your table to sort the items in ascending or descending order the way you desire, then you can process the entire result set from you statement execution into an array. Once you have the data in memory the way you want it laid out in an array (e.g. columns) you can then use the built in sorting methods provided by PHP to perform the sorting criterias desired (http://php.net/manual/en/array.sorting.php).

~Fero

[Mohammed Abuharb]

Thank you alot Michael,

I can use Clustering Order, but this limits me to sorting. let's assume I have this table
CREATE TABLE xyz (id int,
date timestamp,
fname text,
lname text,
country_id int,
PRIMARY KEY (id, date))
WITH CLUSTERING ORDER (date DESC);

then I want to execute the following query:
"SELECT * FROM xyz WHERE id=5 ORDER BY lname ASC"

This query will not be accepted via cassandra. SO, is there a solution for that case (does spark makes sense) or after retrieving data from cassandra
I must re-order the array Via PHP sorting-Array functions?

With my best regards,

[Michael Fero]

Mohammed,

Since lname is not in your CLUSTERING ORDER then ORDER BY will not work as ordering is limited by the clustering order defined on the table (http://cassandra.apache.org/doc/latest/cql/dml.html#ordering-results). If you have varying criteria for how the data is to be sorted and presented in your application and you can not achieve it with the CLUSTERING ORDER then yes I would recommend the sorting array functions in PHP. As far as using Spark I am definitely not the one that can answer these types of questions; however some of these results found on Planet Cassandra might help (http://www.planetcassandra.org/search_gcse/?q=spark)

~Fero