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

Ask Your Question
0

Using nested fields in Order by clause in SQLQuery

Hi,

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)

Regards,
Braam

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

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

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

jaissefsfex gravatar image
edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

Hi,

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.

Cheers,
Guy

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

nirpaz gravatar image
edit flag offensive delete link more

Comments

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?
Thanks

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

http://www.gigaspaces.com/wiki/displa...

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

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

Yes it will.

nirpaz gravatar imagenirpaz ( 2009-12-10 17:30:08 -0500 )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

shay hassidim gravatar imageshay hassidim ( 2009-12-23 13:52:57 -0500 )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

Stats

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

Seen: 85 times

Last updated: Dec 09 '09