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