Hi Ravi, Thanks for the good question and thorough explanation. Unfortunately I don't think this is possible with Apache MetaModel currently. The IN operator in MetaModel only supports a single field operand, but your use case requires a tuple. I think it would be very nice if you would record this as a feature request on https://issues.apache.org/jira/browse/METAMODEL .
I would also point you at the workaround that you mention yourself. But with hundreds of tuples to compare, it might in deed not be scalable enough for you. Best regards, Kasper 2017-01-01 4:31 GMT+01:00 Ravi Sunchu <ravi.sun...@sas.com>: > My apologies. > > > > Forgot to mention one key detail... in my use case, I may encounter cases > where the number of composite keys I need to send may go into couple of > hundreds. > > > > In simple cases where I have a handful of composite keys, I could do this: > > > > select col1, col2, col3, col4 > > from my_table > > where (col1 = 1 and col2 = ‘abc’) OR (col1 = 2 and col2 = ‘xyz); > > > > I am assuming this approach would not work with hundreds of conditions > OR’ed like this (SQL statement length restrictions or performance may be an > issue. For the background on why I am considering OR’ed option might cause > performance issues, see http://stackoverflow.com/ > questions/3074713/in-vs-or-in-the-sql-where-clause). > > > > Also, I’d like to use CompiledQuery for this (i.e., prepared statements). > What I am looking for is a way to do somewhat like: > > > > select col1, col2, col3, col4 > > from my_table > > where (col1, col2) in ?; > > > > where value supplied for ? at execution time could be a List of Maps with > each Map in the List containing key/value pairs for col1 and col2 for each > tuple. > > > > Thanks > > Ravi > > > > *From:* Ravi Sunchu [mailto:ravi.sun...@sas.com] > *Sent:* Saturday, December 31, 2016 9:57 PM > *To:* user@metamodel.apache.org > *Subject:* Querying with composite key and in-operator with > JdbcDataContext > > > > Hi All: > > > > I am new to Apache MetaModel and trying to implement a POC in the project > I am working on. > > > > We have the need to query database tables with composite keys using the > key. Simple lookup of one object is easy by adding appropriate FilterItem > to Query. I am aware of how to create composite FilterItem using child > FilterItem list and appropriate logical operator. > > > > In the API docs or by going through MetaModel project code, so far I am > unable to figure out how to create a Query object to do something like this: > > > > select col1, col2, col3, col4 > > from my_table > > where (col1, col2) in ((1, ‘abc’), (2, ‘xyz’)); > > > > Any help would be appreciated. > > > > I was also wondering if this is even supported. I understand that > composite keys may not be supported by all data stores Apache MetaModel > works with. At this point my primary concern is JDBC data stores. > > > > Thanks > > Ravi >
