Welcome to the new Gigaspaces XAP forum. To recover your account, please follow these instructions.

Ask Your Question

Using nested fields in Order by clause in SQLQuery


I have been testing to see if nested fields in an order by clause would work, it appears that it doesn't.

I was trying something like the following:

Class 1: Person
Person.getAddress() resolves to an address space class that is NOT SpaceExcluded.

Class 2: Address

The query:
new SQLQuery<Person>(Person.class, " ORDER BY address.city ASC");

My test ends up with
"org.openspaces.core.BadSqlQueryException: Failed to execute SQLQuery"

and deep down:
Caused by: java.sql.SQLException: Table address not found
    at com.j_spaces.jdbc.SelectQuery.setColumnMetaData(SelectQuery.java:913)
    at com.j_spaces.jdbc.SelectQuery.validateQuery(SelectQuery.java:818)
    at com.j_spaces.core.client.sql.SqlQueryParser.parseSqlQuery(SqlQueryParser.java:62)
    at com.j_spaces.core.client.sql.QueryManager.readMultiple(QueryManager.java:185)
... 31 more

So, is ordering on nested fields supported or not? (it doesn't say on the wiki)


This thread was imported from the previous forum.
For your reference, the original is available here

asked 2009-12-08 04:21:49 -0600

updated 2013-08-08 09:52:00 -0600

jaissefsfex gravatar image
edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted


This is not supported in XAP 7.0.x and prior versions. This feature is currently under development in the 7.1.x development stream.
You should try the early access release.


answered 2009-12-09 13:28:27 -0600

nirpaz gravatar image
edit flag offensive delete link more


Should something like new SQLQuery<Person>(Person.class, " address.city = 'LA'") work in 7.0 or this will be introduced in 7.1 also?
I mean using nested fields in where clause, not in order by

Braam, can you please point me to the wiki page you are referring?

lukeh gravatar imagelukeh ( 2009-12-10 03:43:51 -0600 )edit


Edited by: Braam Wijsmuller on Dec 10, 2009 5:26 AM

stratofarmer gravatar imagestratofarmer ( 2009-12-10 05:25:46 -0600 )edit

Yes it will.

nirpaz gravatar imagenirpaz ( 2009-12-10 17:30:08 -0600 )edit

Here is an example for a space class and its embedded classes and relevant queries we will support with 7.1: {code} @SpaceClass public static class Person { private int id; private Info personalInfo; private String description; //getter and setter methods } {code}

{code} public static class Info implements Serializable { private String name; private Address address; private Date birthday; private long socialSecurity; private int _id; //getter and setter methods } {code}

{code} public static class Address implements Serializable { private int zipCode; private String street; //getter and setter methods } {code} Here are examples for queries we will support with 7.1: {code} SQLQuery<person> query = new SQLQuery<person>(Person.class.getName(), " personalInfo.name='John' "); SQLQuery<person> query = new SQLQuery<person>(Person.class.getName(), " personalInfo.socialSecurity<10000050L "); SQLQuery<person> query = new SQLQuery<person>(Person.class.getName(), " personalInfo.socialSecurity in (10000010L,10000004L,1L) "); SQLQuery<person> query = new SQLQuery<person>(Person.class.getName(), " personalInfo.birthday='1982-04-18' "); SQLQuery<person> query = new SQLQuery<person>(Person.class.getName()," personalInfo.address.street ='Street0' "); SQLQuery<person> query = new SQLQuery<person>(Person.class.getName(), "personalInfo.name like '%n1%' "); SQLQuery<person> query = new SQLQuery<person>(Person.class.getName()," personalInfo.name not like '%n1%' and description not like '%a2b%' "); SQLQuery<person> query = new SQLQuery<person>(Person.class.getName()," personalInfo.id >10 and personalInfo.id <20 and personalInfo.id <> 15 "); SQLQuery<person> query = new SQLQuery<person>(Person.class.getName()," personalInfo.name='John1' or personalInfo.address.street ='Street0'"); SQLQuery<person> query = new SQLQuery<person>(Person.class.getName()," personalInfo.socialSecurity<10000050L and personalInfo.socialSecurity>=10000010L"); {code}

Since like/rlike queries does not use indexes these will need to scan the relevant objects to find matching objects. This means that the performance of such queries will depend on the amount of candidate result set we will have out of the initial query execution phase.

Limitations for nested object query with XAP 7.1: - No collections support. - No order by/group by on nested objects fields support.


shay hassidim gravatar imageshay hassidim ( 2009-12-23 13:52:57 -0600 )edit

Your Answer

Please start posting anonymously - your entry will be published after you log in or create a new account.

Add Answer

Question Tools

1 follower


Asked: 2009-12-08 04:21:49 -0600

Seen: 68 times

Last updated: Dec 09 '09