Hot questions for Using Cassandra in stratio

Question:

I am performing a user search system in my Cassandra database. For that purpose I installed Cassandra Lucene Index from Stratio. I am able to lookup users by username, but the problem is as follows:

This is my Cassandra users table and the Lucene Index:

CREATE TABLE user (
    username text PRIMARY KEY,
    email text,
    password text,
    is_verified boolean,
    lucene text
);
CREATE CUSTOM INDEX search_main ON user (lucene) USING 'com.stratio.cassandra.lucene.Index' WITH OPTIONS = {
    'refresh_seconds': '3600',
    'schema': '{
        fields : {
            username : {type : "string"},
            is_verified : {type : "boolean"}
        }
    }'
};

This is a normal query performed to Lookup a user by username:

SELECT * FROM user WHERE lucene = '{filter: {type : "wildcard", field : "username", value : "*%s*"}}' LIMIT 15;

My Question is:

How could I sort the returned results to ensure that any verified users are between the first 15 results in the query? (Limit is 15).


Answer:

You can use this search:

SELECT * FROM user WHERE lucene = '{filter: {type:"boolean", must:[
    {type : "wildcard", field : "username", value : "*%s*"},
    {type : "match", field : "is_verified", value : true}
]}}' LIMIT 15;

Question:

I just installed the Stratio cassandra-lucene-index Cassandra plugin.

Is it possible to use the Stratio cassandra-lucene-index via BoundStatements somehow ? Or Using the Builder is the closest as it can get ?

The reason I am asking is because I and looking to make my existing DAO layer, that is 100% based on BoundStatements, to use the Stratio cassandra-lucene-index and would like to continue using BoundStatements for many reasons: clarity, security and overall coding standardization within my application.

Thanks


Answer:

Most documentation examples where recently updated to show how to search using BoundStatements and the Builder together.

I hope it helps.

Question:

I want that a field in my table scores higher than another field. For example, I have two columns name and surname. If I search after "Hans" i want that the content in name scores higher. How can I do this using a cql statement like in the official examples.

regards Tugi


Answer:

As stated in doc, every search type has a boost option that allows user to set a weight for every different query.

Your use case should look like this:

CREATE KEYSPACE test with replication = {
    'class' : 'SimpleStrategy', 'replication_factor' : '1' 
}; 

CREATE TABLE test.users (
    id bigint PRIMARY KEY,
    name text,
    surname text
);

CREATE CUSTOM INDEX test_users_idx ON test.users() 
USING 'com.stratio.cassandra.lucene.Index'
WITH OPTIONS = {
    'refresh_seconds': '1',
    'schema': '{
        fields: {
            name: {type: "string"},
            surname:{type:"string"}
        }
    }'
};

INSERT INTO test.users(id, name, surname) VALUES (1, 'Hans', 'Albers');
INSERT INTO test.users(id, name, surname) VALUES (2, 'Quintina', 'Koch');
INSERT INTO test.users(id, name, surname) VALUES (3, 'Orlando', 'Schwarz');
INSERT INTO test.users(id, name, surname) VALUES (4, 'Federico', 'Hans');
INSERT INTO test.users(id, name, surname) VALUES (5, 'Berenice', 'Schwarz');
INSERT INTO test.users(id, name, surname) VALUES (6, 'Zaida', 'Koch');

SELECT * FROM test.users WHERE expr(test_users_idx,'{
    query: {
        type : "boolean", 
        should : [
            {type: "match", field: "name", value: "Hans", boost: 1.5},
            {type: "match", field: "surname", value: "Hans", boost: 1.0}
        ]
    }
}');