Cassandra Lucene Index boolean syntax

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).

score 1 · Accepted Answer · answered Feb 19 '16 at 19:09

1

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;

answered Feb 19 '16 at 19:09

Andrés de la Peña

231
1
4

With this Syntax the listed users are **only** Verified. What I wanted to do is get first the verified users, and then the non verified. Is it possible to achieve in one query? Or should I have to do two queries? One for verified and other for non-verified. – Ezequiel Adrian Feb 21 '16 at 01:25
Do you want to get first 15 verified users, and then all the not verified users? Or just get no more than 15 results satisfying the wildcard pattern sorted by is_verified? What is the purpose of the LIMIT clause? – Andrés de la Peña Feb 21 '16 at 20:47
Maybe you need to add a sort clause to order the rows by "is_verified": – Andrés de la Peña Apr 08 '16 at 10:22
2

`SELECT * FROM user WHERE lucene = '{ filter: {type : "wildcard", field : "username", value : "*%s*"}, sort: {fields: [ {field:"is_verified", reverse:true} ] } }' LIMIT 15;` – Andrés de la Peña Apr 08 '16 at 10:29

Cassandra Lucene Index boolean syntax

1 Answers1