# Nested relation in sort method **URL:** \ **Category:** Classic Servoy\ **Created:** [January 23, 2007, 3:55pm UTC](https://forum.servoy.com/t/nested-relation-in-sort-method/6998 "2007-01-23T15:55:41Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![Dexadrine](https://avatars.discourse-cdn.com/v4/letter/d/65b543/32.png) [@Dexadrine](https://forum.servoy.com/u/Dexadrine)\ **Post date:** [January 23, 2007, 3:55pm UTC](https://forum.servoy.com/t/nested-relation-in-sort-method/6998/1 "2007-01-23T15:55:41Z")
This may be a short answer. Can you use nested relations in the sort function? Example: ```auto var sortString = "product_priority_to_products.products_to_brands.brand_name asc"; controller.sort(sortString); ``` This code does not provide the results I would expect (the records are not sorted properly). ---
**Author:** ![IT2Be](https://avatars.discourse-cdn.com/v4/letter/i/dc4da7/32.png) [@IT2Be](https://forum.servoy.com/u/IT2Be)\ **Post date:** [January 23, 2007, 5:00pm UTC](https://forum.servoy.com/t/nested-relation-in-sort-method/6998/2 "2007-01-23T17:00:42Z")
A very short one: no answer ![:)]( "Smile") You question is too short. What is ‘sorted not properly’. According to you? According to the dictionary? Please give us an example… ---
**Author:** ![Dexadrine](https://avatars.discourse-cdn.com/v4/letter/d/65b543/32.png) [@Dexadrine](https://forum.servoy.com/u/Dexadrine)\ **Post date:** [January 24, 2007, 11:40am UTC](https://forum.servoy.com/t/nested-relation-in-sort-method/6998/3 "2007-01-24T11:40:32Z")
OK, here are the relationships: product\_priority.product\_id = products.product\_id(pk) products.brand\_id = brands.brand\_id(pk) When running the following code (related field is 2 ‘steps’ away): ```auto var sortString = "product_priority_to_products.products_to_brands.brand_name asc"; controller.sort(sortString); ``` Nothing seems to happen at all (sort order remains the same, ie not sorted by brand\_name alphabetically). However if i sort by brand\_id thus (related field 1 ‘step’ away): ```auto var sortString = 'product_priority_to_products.brand_id asc'; forms.PM_TAB_product_priority_list.controller.sort(sortString); ``` The sort happens as I would expect (by brand\_id). I have tested the nested relation in a calc field and it works fine and displays the right field text. Can Servoy cope with sorting on a related field 2 ‘steps’ away? Or only 1? ---
**Author:** ![IT2Be](https://avatars.discourse-cdn.com/v4/letter/i/dc4da7/32.png) [@IT2Be](https://forum.servoy.com/u/IT2Be)\ **Post date:** [January 24, 2007, 11:46am UTC](https://forum.servoy.com/t/nested-relation-in-sort-method/6998/4 "2007-01-24T11:46:12Z")
Aha, so you are sorting a relation over a relation… I can imagine that that doesn’t work. Something Servoy should answer I guess. I’m sorry for that… ---
**Author:** ![swingman](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/swingman/32/4536_2.png) [@swingman](https://forum.servoy.com/u/swingman)\ **Post date:** [January 24, 2007, 3:08pm UTC](https://forum.servoy.com/t/nested-relation-in-sort-method/6998/5 "2007-01-24T15:08:10Z")
If you are familiar with SQL, you can use databaseManager.getDataSetByQuery(), and write a query generating a dataset of pks sorted the way you want them… ---
**Author:** ![patrick](https://avatars.discourse-cdn.com/v4/letter/p/b2d939/32.png) [@patrick](https://forum.servoy.com/u/patrick)\ **Post date:** [January 24, 2007, 4:58pm UTC](https://forum.servoy.com/t/nested-relation-in-sort-method/6998/6 "2007-01-24T16:58:19Z")
I can confirm that that doesn’t work. I’d like to add one more remark to this > I have tested the nested relation in a calc field and it works fine and displays the right field text. This is a bit dangerous. It only works if both relations are 1:1. Imagine this: 1 record in Table A → 3 records in Table B every 1 record in Table B → 5 records in Table C If you now use > return table\_a\_to\_table\_b.table\_b\_to\_table\_c.column you get the only one value instead of the 15 possible combinations. ---
**Author:** ![Dexadrine](https://avatars.discourse-cdn.com/v4/letter/d/65b543/32.png) [@Dexadrine](https://forum.servoy.com/u/Dexadrine)\ **Post date:** [January 25, 2007, 9:24am UTC](https://forum.servoy.com/t/nested-relation-in-sort-method/6998/7 "2007-01-25T09:24:16Z")
> patrick: > This is a bit dangerous. It only works if both relations are 1:1. Yes, you are right, however in this case, the relations are both 1 to 1. > swingman: > If you are familiar with SQL, you can use databaseManager.getDataSetByQuery(), and write a query generating a dataset of pks sorted the way you want them… You are correct also, but using the loadRecords function is limited to a max number of records. In 3.1 it seems to be 1000. Hence this method: ```auto var query = "SELECT product_priority.priority_id FROM product_priority"; query += " Inner Join products ON product_priority.product_id = products.product_id"; query += " Inner Join brands ON products.brand_id = brands.brand_id ORDER BY brands.brand_name ASC"; var dataset = databaseManager.getDataSetByQuery(controller.getServerName(),query,null,-1); controller.loadRecords(dataset); ``` Yields a foundset of 1000 recs, where as the query actually returns 1430 results. I would prefer not to be forced to use a stored calc on the product\_priority table to achieve this, but it seems I may have to. Could someone from Servoy confirm this limitation with sorting? And with the loadRecords funtion? ---
**Author:** ![patrick](https://avatars.discourse-cdn.com/v4/letter/p/b2d939/32.png) [@patrick](https://forum.servoy.com/u/patrick)\ **Post date:** [January 25, 2007, 9:26am UTC](https://forum.servoy.com/t/nested-relation-in-sort-method/6998/8 "2007-01-25T09:26:23Z")
You can use loadRecords(query) directly. Then there is no limit to the number of records. However, you need to comply to all the restrictions that come with that. ---
**Author:** ![Dexadrine](https://avatars.discourse-cdn.com/v4/letter/d/65b543/32.png) [@Dexadrine](https://forum.servoy.com/u/Dexadrine)\ **Post date:** [January 25, 2007, 9:47am UTC](https://forum.servoy.com/t/nested-relation-in-sort-method/6998/9 "2007-01-25T09:47:18Z")
Thanks for the tip Patrick, after reading up on that in the reference guide, thats exactly what I was looking for. I used the following method: ```auto var query = "SELECT product_priority.priority_id FROM product_priority, products, brands"; query += " WHERE product_priority.product_id = products.product_id"; query += " AND products.brand_id = brands.brand_id ORDER BY brands.brand_name ASC"; controller.loadRecords(query); ``` I will certainly be using loadRecords() like this again. You learn something new every day, as they say. ![:D]( "Very Happy") ---
**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\ **Post date:** [January 25, 2007, 10:09am UTC](https://forum.servoy.com/t/nested-relation-in-sort-method/6998/10 "2007-01-25T10:09:18Z")
> Dexadrine: > Thanks for the tip Patrick, after reading up on that in the reference guide, thats exactly what I was looking for. I used the following method: > > ```auto > var query = "SELECT product_priority.priority_id FROM product_priority, products, brands"; > > ``` query += " WHERE product\_priority.product\_id = products.product\_id"; query += " AND products.brand\_id = brands.brand\_id ORDER BY brands.brand\_name ASC"; controller.loadRecords(query); > ```auto > > I will certainly be using loadRecords() like this again. You learn something new every day, as they say. :D > > ``` And if you do work with 1:n relations you still can use this approach without having ‘double’ records. Just add GROUP BY product\_priority.priority\_id.