I’m using a SQL query to load all contact details for a person,
SELECT contact_id FROM crm_contacts LEFT OUTER JOIN classification_types ON type_id = contact_type_id WHERE
contact_person_id = ? OR contact_company_id = ?
ORDER BY CASE WHEN contact_person_id > 0 THEN 1 ELSE 0 END DESC, type_sort_order ASC, contact_id
The idea is to load the records already sorted by the query so the personal stuff like mobile and email address comes first, while contact details from the company (the employer) comes last.
There is NO sort-order defined on the form
This works fine if I execute the query against the back-end database (PostgreSQL).
In Servoy, prior to version 3.5, I would create a dataset and load it and everything would end up in the right order.
In version 3.5+, I use
controller.loadRecords(query, args);
However, the records loose their sort-order and I have to add an extra
That’s a bit strange though, release notes for 3.5 states:
With the new sql generation engine, just about any legal sql can be used for custom queries. Your sql will still have to produce primary keys for the forms' table. When your sql uses the ORDER BY keyword, the old sql generator method is still used. This means that all the old restrictions apply. It also means that your old solutions' queries should cause no trouble with Servoy 3.5 .
So looks like loadRecords(query, args) is an exception to that and it always uses the new SQL engine. Good to know.
Hmmm you’re right. I kinda assumed it would work the same way as putting a sort within a controller.find()/controller.search() block.
Unless someone comes up with another way I suggest you file a feature request for this in the support system.
swingman:
The load is reasonably fast, but the sort causes a horrible delay as Servoy seems to reload some of the data…
I have the exact same problem
I have a tableview with one field showing customers and two fields showing a calculation of related data (in this case: addresses)
When I do this:
controller.loadAllRecords()
the data shows almost instantly!
but when I do this:
controller.loadAllRecords(0
controller.sort(‘companyname asc’)
my related field data (lazy loading) becomes SOOOOO SLOWWWWWWWW!!
UPDATE: even worse, when I fire this method 20 times, the data is showing slower and slower, at the end I have to restart the client!
I think this is a bug, because in Servoy 3.1 this has never happened before.
swingman:
Clearing and sorting before the load, makes the search nice and snappy…
But the records are not loaded in the correct order
Christian,
We are planning to change controller.loadRecords(sql) to use the existing sorting when the sql does not define a sorting (or when we cannot use the sorting as explained in http://forum.servoy.com/viewtopic.php?p=51478#51478 )
To be able to set the sorting without firing a superfluous query we are planning to introduce a boolean argument to controller.sort()
When set to false, the sorting will be set, but only applied on the next query.
This will not be changed in 3.5 because it involves a small behaviour change, so it would be introduced in 4.0.
Good, but I still think the whole question of loading records using SQL queries need to be looked at carefully and made simpler and more intuitive.
You want Servoy to be as intuitive as possible. Take this snippet of Ruby code, even if you have never seen Ruby before, if is very clear what order you are going to get your records in…
var query = 'SELECT id FROM categories WHERE hide = ? ORDER BY title ASC';
var args = new Array();
args.push(0);
forms.categories.controller.loadRecords(query,args);
Sorry! Servoy will load your records in random order… because…
The new query method is used when the sql does not meet the old (pre-3.5) requirement
you have no order-by clause
you have no from keyword
your query is not fully qualified on the main table
you have a group-by, having, join or union keyword
Then the new style is used in which an order-by clause is ignored
This is neither simple or intuitive.
Adding a third param to ‘loadRecords’ to specify sorting makes it obvious that if you want your records sorted, you’ll have to specify it separately from the query.
I managed to get the sorting back for now by qualifying the pk field in the queries…
As Rob said, an extra parameter was added to controller.sort(). This second parameter is a boolean named “defer”. If set to true, then the sort options will be just remembered, and used only at the next data load operation. By default this parameter is false, so if you don’t specify it, the sort() method will work just like until now.
Basically this means that if you write something like this:
