'Order by' for collection values

6 posts, 1 answers
  1. Aliaksei
    Aliaksei avatar
    7 posts
    Member since:
    May 2008

    Posted 16 May 2009 Link to this post

    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
  2. Zoran
    Admin
    Zoran avatar
    534 posts

    Posted 18 May 2009 Link to this post

    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.
  3. DevCraft banner
  4. Zoran
    Admin
    Zoran avatar
    534 posts

    Posted 18 May 2009 Link to this post

    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.
  5. Aliaksei
    Aliaksei avatar
    7 posts
    Member since:
    May 2008

    Posted 18 May 2009 Link to this post

    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
  6. Answer
    Zoran
    Admin
    Zoran avatar
    534 posts

    Posted 19 May 2009 Link to this post

    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.
  7. Aliaksei
    Aliaksei avatar
    7 posts
    Member since:
    May 2008

    Posted 19 May 2009 Link to this post

    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
Back to Top
DevCraft banner