How can I without the 'allow filtering' to have the conditional query in CQL?

I am the new leaner in CQL. I have the (restaurants) table. By implemented 'desc restaurants', it shows the information in restaurants table information

How can I run the following query without using 'allow filtering', select name from restaurants where borough = 'brooklyn';

What are the necessary steps in order to be able to run the query without 'allow filtering'?

Thanks so much.

Solution

With Cassandra (CQL) you need to take a query-based modeling approach. This means that you'll need a table specifically-designed to serve queries by borough.

A good question to start with, is why (in your current table design) is id your sole primary key? Do you have to support a query by id? If not, then you shouldn't need that table. However, as borough is not unique, it makes sense to cluster an additional column(s) into your primary key definition.

I'd go with something like this:

CREATE TABLE stackoverflow.restaurants_by_borough (
    borough text,
    name text,
    id int,
    buildingnum text,
    cuisinetype text,
    phone text,
    street text,
    zipcode int,
    PRIMARY KEY (borough, name, id)
) WITH CLUSTERING ORDER BY (name ASC, id ASC);

Now this query will work:

select name from restaurants_by_borough
where borough = 'brooklyn';

It will return data for each borough, sorted by name. Also, if any two restaurants have the same name (there's probably more than one McDonalds in Brooklyn) id is added to the end of the key to ensure uniqueness.