ATG RQL query like SQL 'property in (x, y, z)'

Question

I'm trying to write an RQL query that does the equivalent of this sql:

select * from some_table t where t.property in (1, 2, 3, 4 ...)

I'm not sure if RQL supports this though. In the oracle docs, there's an example of how to do this on the ID property of a repository item:

ID IN { "0002421", "0002219", "0003244" ... }

but when I try to change ID in this example to the property I want to query on, I get an RQL ParseException.

Does anyone know if this is possible?

score 2 · Accepted Answer · edited Aug 27 '14 at 11:53

2

this is possible through querybuilder api(see example below). I'm not sure why this is not available through plain RQL though.

QueryExpression thePropertyExpression =     
   theQueryBuilder.createPropertyQueryExpression("postalCode");

String [] zipcodeArray = {"22185", "22183"};

QueryExpression theValueExpression = 
    theQueryBuilder.createConstantQueryExpression(zipcodeArray);

Query theQuery = 
    theQueryBuilder.createIncludesQuery(theValueExpression, thePropertyExpression);

edited Aug 27 '14 at 11:53

parakmiakos

2,896
8
26
42

answered Aug 27 '14 at 11:48

Aromal

124
2
6

@rcgeorge23 probably because it wasn't formatted properly. – radimpe Aug 28 '14 at 05:10
Sorry, I'm a newbie here and this is my first answer in stackoverflow. – Aromal Sep 14 '14 at 06:30
@Vihung, I didn't understand what you meant. Could you please explain. – Aromal Jun 07 '16 at 11:13

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

From the ATG Documentation the RQL Grammar includes a specific comparison query for ID IN so changing ID to another property will not parse properly thus your ParseException.

Looking further down the Grammar document you'll find the ComparisonOperator. The one of particular interest is the INCLUDES ANY.

An example around its use (from the docs)

interests INCLUDES ANY { "biking", "swimming" }

This is equivalent to:

(interests INCLUDES "biking") OR (interests INCLUDES "swimming")

So this may work, as long as you are searching scalar properties.

So that leaves you with the final option, which you are probably trying to avoid which is to create a big OR condition, which is the way I normally would do it, since you'll have to go through a loop to build up your IN statement anyway.

Thanks for the answer, although I think INCLUDES is only used for collections. The property I'm querying against isn't a collection - I did try using INCLUDES, but I got another syntax error. And yep, I'm trying to avoid a big (property='x' or property='y' ...) clause, but I think this may be the only option. — rcgeorge23, Dec 09 '13 at 13:36

score -1 · Answer 3 · answered Dec 20 '13 at 12:41

-1

Answer from radimpe is correct and is straight from oracle docs. You can do an INCLUDES ANY query for your requirement. It works for strings. For ex. if you are searching for users with first name in Alex or Brian you can write in RQL :

firstName INCLUDES ANY {"Alex","Brian"}

Link here : http://docs.oracle.com/cd/E24152_01/Platform.10-1/ATGRepositoryGuide/html/s0305multivaluedpropertyqueries01.html

answered Dec 20 '13 at 12:41

boyintello

326
3
14

That example doesn't appear in the link you provided. All the examples on that page operate on collections. From the paragraph at the top of the page "Another set of queries can be applied to arrays or collections of scalar values—for example, properties of type int[], or Set of Strings". INCLUDES does not do what you've shown in your example. – rcgeorge23 Dec 20 '13 at 12:55
Oops sorry, it was my mistake. it really doesn't search that way.. only multi valued (I tried it thru dyn admin). Also I didn't find anything that does this in an RQL. If you are doing it programatically, you might need to use sqlpassthrough (parameterized). – boyintello Dec 20 '13 at 13:06
INCLUDES ANY only works for properties that are lists or arrays – Vihung Apr 27 '16 at 15:49

ATG RQL query like SQL 'property in (x, y, z)'

3 Answers3