[Solved] 'Order by' for collection values

[Aliaksei]

Hi Telerik Team!

I have a question about 'order by' clause.

I have two classes:

/* NOTE: public class properties and methods omitted for clarity */

class Property{
   string val;
   ..
}

class Product{
   [ItemType(typeof(Property)]
   ArrayList properties;
   ..
}

I try to sort results by property values

select * from ProductExtent AS p, p.properties AS prop ORDER BY prop.val

and receive an exception

Object reference not set to an instance of an object.
   at OpenAccessRuntime.Relational.sql.exp.SelectExp.handleOrderByFieldNavNode(FieldNavNode nav, SelectExp se, Node prevNode)
   at OpenAccessRuntime.Relational.sql.exp.SelectExp.addOrderBy(OrderNode[] orders, Boolean append, JDOQLNodeToSqlExp visitor)
   at OpenAccessRuntime.Relational.query.RelationalJDOQLCompiler.addOrderBy(SelectExp root)
   at OpenAccessRuntime.Relational.query.RelationalJDOQLCompiler.compileImp(ImmutableQueryDetails q)
   at OpenAccessRuntime.Relational.query.RelationalJDOQLCompiler.compile(ImmutableQueryDetails q)
   at OpenAccessRuntime.Relational.RelationalStorageManager.compile(ImmutableQueryDetails q)
   at OpenAccessRuntime.Relational.RelationalStorageManager.compileQuery(ImmutableQueryDetails query)
   at OpenAccessRuntime.storagemanager.logging.LoggingStorageManager.compileQuery(ImmutableQueryDetails query)

Though prop.val in 'Where' clause works well

Could you please advice how I can order by collection values. Probably I need to change classes? I just need to have dynamic properties and ability to order by their values on SQL server side

Thanks in advance
Aliaksei

[Zoran]

Hello Aliaksei,

Yes you are right, that is a missing functionality of our OQL parser. The query can be re-written in another way though where the result set will be the one that you desire. Since the Product has a collection of properties I assume that the property has a reference to a Product object.

Here is the new look of the query then:

"SELECT * FROM  PropertyExtent AS p ORDER BY p.val"

The query generated will still contain a JOIN between the Product and Property tables as long as you use the product reference of the property objects from the result set.

I hope this is not to confusing, if you need more clarification please send us a sample project or describe the final goal you want to achieve and we can make a sample demonstrating this for you.

Kind regards,
Zoran
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[Zoran]

Hello Aliaksei,

Your Telerik Points were updated.

All the best,
Zoran
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[Aliaksei]

Hello Zoran,

Thank you for you message!

But it seems your solution does solve my problem.

I will clarify my task.

I have a Product class with a Hashtable of properties.

Product p1=new Product();
p1.Properties["price"]=10;
p1.Properties["color"]="red";
scope.Add(p1);

Product p2=new Product();
p2.Properties["price"]=15;
p2.Properties["color"]="green"
scope.Add(p2);

I need to select Products and order them by one of a property (let's say by "price" property) 

Could you please advice how could I implement this?

I will appreciate any help!

Thanks in advance,

Aliaksei

[Zoran]

Hi Aliaksei,

The goal would not be possible if every object of type Property does not know "it's Product". That is a common way how 1-n relations are handed with OpenAccess. That is a sample modification of the code from your first post so you could achieve your goal:

class Property{
   string val;
   Product product;
   ..
}

class Product{
   [ItemType(typeof(Property)]
   ArrayList properties;
   ..
}

If we have that and want to select the products the query would look as the following:

"SELECT p.Product FROM  PropertyExtent AS p ORDER BY p.val"

I hope that helps you.

Greetings,
Zoran
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[Aliaksei]

Hello Zoran,

Thank you very much, your solution is what I needed.

For those who might face the same problem I'll show what I did.

I use the following classes:

class PropertyMeta{

string title;

string type;

...

}

class Property{

PropertyMeta meta;

string val;     Product product; /* its important the property to contain reference to its owner */

...

}

class Product{

[ItemType(typeof(string),typeof(Property))]

Hashtable properties=new Hashtable();

...

}

PropertyMeta describes property type and contains property name

Then I make the following query:

SELECT prop.product FROM PropertyExtent AS prop WHERE prop.meta.title='price' ORDER BY prop.val

Have a nice day