IN Query

[Karthick V]

lets consider the below table

cqlsh:mykeyspace> create table sample ( column1 int ,column2 int , column3 int ,column4 int,primary KEY (column1,column2,column3));

here column1 is a partition key and column2, column3 are clustering key .After some sample entry it will be like

cqlsh:mykeyspace> SELECT * FROM sample ;

 column1 | column2 | column3 | column4

---------+---------+---------+---------

       1 |       2 |       3 |       4

       1 |       3 |       4 |       4

       1 |       4 |       5 |       4

       1 |       5 |       4 |       4

 

Now i need to retrieve the row where these two pair (column2,colum3) =(2,3) and(3,4) these can be done in cqlsh using

cqlsh:mykeyspace> select * from sample where column1=1 and (column2,column3) in ((2,3),(3,4));

 column1 | column2 | column3 | column4

---------+---------+---------+---------

       1 |       2 |       3 |       4

       1 |       3 |       4 |       4

How can i achieve this using DataStax java driver?,my driver allows me onnly to choose single column in IN condition(QueryBuilder.in function)

Note: my current java Driver version in 3.0

 

[hemendra kumar]

Hello Karthick,

You need to change your table structure as below:

CREATE TABLE sample (

  column1 int,

  column2 int,

  column3 int,

  column4 int,

  PRIMARY KEY ((column1, column2), column3)

) ;

Then you can get required data by writing query like this :

cqlsh:abc> select * from sample where column1=1 and column2 in(2,3) and column3 in(3,4);

 column1 | column2 | column3 | column4

---------+---------+---------+---------

       1 |       2 |       3 |       4

       1 |       3 |       4 |       4

(2 rows)

If you don't want to change your table structure the you can apply IN only column three only when column will be restrict will equal like

select * from sample where column1=1 and column2=2 and column3 in (3,4);

Thanks,

Hemendra

--
You received this message because you are subscribed to the Google Groups "DataStax Java Driver for Apache Cassandra User Mailing List" group.
To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-user+unsubscribe@lists.datastax.com.

[Olivier Michallat]

Karthick,

JAVA-866 fixed this issue. It was resolved in 3.0.1.

--

Olivier Michallat

Driver & tools engineer, DataStax

[Karthick V]

thanks for your replay ,It will work if we redefine the CF but redefining the CF is no an option and i like to achieve this by mean of datastax driver following are the thing in my mind

1. I can change my driver version to 3.0.3 which supports a additional in query  public static Clause in(List<String> names,List<List<?>> values). which i have tried already but the first param List<String> names seems like a bug .it gives error message to replace with a string.
2.  the alternative option will be to generate the tail part of the query as a String and append it .Eventually it works 
   

i like to explore that is there any possible way in datastax

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-us...@lists.datastax.com.

[Olivier Michallat]

the first param List<String> names seems like a bug .it gives error message to replace with a string.

Here's an example:

import com.google.common.collect.ImmutableList;

List<String> names = ImmutableList.of("column2", "column3");

List<List<?>> values = ImmutableList.<List<?>>of(

        ImmutableList.of(2, 3),

        ImmutableList.of(3, 4)

);

BuiltStatement select = select().from("sample")

        .where(eq("column1", 1))

        .and(in(names, values));

System.out.println(select.toString());

// prints: SELECT * FROM sample WHERE column1=1 AND (column2,column3) IN ((2,3),(3,4));

--

Olivier Michallat

Driver & tools engineer, DataStax

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-user+unsubscribe@lists.datastax.com.

[Karthick V]

As you explained every List<values> contains a combination of clustering key (like ImmutableList.of(2, 3)) but the number of combination is restricted because we have this Criteria IllegalArgumentException - if names.size() != values.size(). 

This is the exception I got while retrieving 50 combination of clustering key.

Caused by: java.lang.IllegalArgumentException: The number of names (2) and values (50) don't match

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-us...@lists.datastax.com.

[Kevin Gallardo]

Hi, 

The error message you are getting is because having more than 2 lists, in the lists of value for your Clustering key is not possible. Since your Clustering key is composed of 2 columns, there can only be 2 lists in the IN list.

SELECT * FROM sample WHERE column1=1 AND (column2,column3) IN ((2,3),(3,4));

                                                                                                                                        \__/       \__/

                                                                                                                                          |             |

                                                                                                                                          v            v

                                                                         Represents the possible values for column2   |   Represents the possible values for column3

So it is possible to input more values in the lists, but not more lists. To take back Olivier's example:

List<String> names = ImmutableList.of("column2", "column3");
List<List<?>> values = ImmutableList.<List<?>>of(

        ImmutableList.of(2, 3, 4, 5, 6, 7),
        ImmutableList.of(3, 4, 5, 6, 7, 8)

);
BuiltStatement select = select().from("sample")
        .where(eq("column1", 1))
        .and(in(names, values));
System.out.println(select.toString());

// prints: SELECT * FROM sample WHERE column1=1 AND (column2,column3) IN ((2,3,4,5,6,7),(3,4,5,6,7,8));

Hope that helps.

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-user+unsubscribe@lists.datastax.com.

--

Kévin Gallardo.

kgdo.me

Software Engineer in Drivers and Tools Team at DataStax.

[Alexandre Dutra]

I think Karthick has a point here. 

It appears that the way we implemented multi-column IN restrictions in JAVA-866 is wrong: each tuple should correspond to a possible combination of values for all the columns in the IN restriction, and NOT to all possible values for one single column in the restriction. 

So given two columns a and b of types int and varchar, the following should be valid:

List<String> names = ImmutableList.of("a", "b");

List<List<?>> values = ImmutableList.<List<?>>of(

        ImmutableList.of(1, "foo"),
        ImmutableList.of(2, "bar"),
        ImmutableList.of(3, "qix"));
BuiltStatement select = select().all().from("t").where(eq("pk", 1)).and(in(names, values));

But it actually throws IllegalArgumentException with message "The number of names (2) and values (3) don't match".

I've re-opened JAVA-866, we'll look into that. Thanks Karthick for reporting this!

Regards,

Alexandre

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-us...@lists.datastax.com.

--

Kévin Gallardo.

kgdo.me

Software Engineer in Drivers and Tools Team at DataStax.

--

You received this message because you are subscribed to the Google Groups "DataStax Java Driver for Apache Cassandra User Mailing List" group.
To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-us...@lists.datastax.com.

--

Alexandre Dutra

Driver & Tools Engineer @ DataStax