CODEWITHSUNDEEP
cassandracqlcassandra-3.0cqlsh
NoobCoder123
NoobCoder123

Reputation: 5

viewing as list in cassandra

Table

CREATE TABLE vehicle_details (
    owner_name text,
    vehicle list<text>,
    price float,
    vehicle_type text,
    PRIMARY KEY(price , vehicle_type)
)

I have two issues over here

  1. I am trying to view the list of the vehicle per user. If owner1 has 2 cars then it should show as owner_name1 vehicle1 & owner_name1 vehicle2. is it possible to do with a select query? The output I am expecting
      owner_name_1 | vehicle_1
      owner_name_1 | vehicle_2
      owner_name_2 | vehicle_1
      owner_name_2 | vehicle_2
      owner_name_2 | vehicle_3
  1. I am trying to use owner_name in the primary key but whenever I use WHERE or DISTINCT or ORDER BY it does not work properly. I am going to query price, vehicle_type most of the time. but Owner_name would be unique hence I am trying to use it. I tried several combinations. Below are three combinations I tried.
      PRIMARY KEY(owner_name, price, vehicle_type) WITH CLUSTERING ORDER BY (price)
      PRIMARY KEY((owner_name, price), vehicle_type)
      PRIMARY KEY((owner_name, vehicle_type), price) WITH CLUSTERING ORDER BY (price)

Queries I am running

SELECT owner_name, vprice, vehicle_type from vehicle_details WHERE vehicle_type='SUV';
SELECT Owner_name, vprice, vehicle_type from vehicle_details WHERE vehicle_type='SUV' ORDER BY price desc;

Upvotes: 0

Views: 45

Answers (1)

Erick Ramirez
Erick Ramirez

Reputation: 16303

Since your table has:

PRIMARY KEY(price , vehicle_type)

you can only run queries with filters on the partition key (price) or the partition key + clustering column (price + vehicle_type):

SELECT ... FROM ... WHERE price = ?
SELECT ... FROM ... WHERE price = ? AND vehicle_type = ?

If you want to be able to query by owner name, you need to create a new table which is partitioned by owner_name. I also recommend not storing the vehicle in a collection:

CREATE TABLE vehicles_by_owner
    owner_name text,
    vehicle text,
    ...
    PRIMARY KEY (owner_name, vehicle)
)

By using vehicle as a clustering column, each owner will have rows of vehicles in the table. Cheers!

Upvotes: 1

Related Questions