Nested relation in sort method

This may be a short answer. Can you use nested relations in the sort function? Example:

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).

A very short one: no answer :)

You question is too short. What is ‘sorted not properly’.

According to you?
According to the dictionary?

Please give us an example…

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):

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):

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?

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…

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…

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.

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:

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?

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.

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:

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

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:

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

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.