Sort, then search related record field

Classic Servoy
1

I have three tables accounts, journal & journal_history
There are three entries for an account in the journal.
I am doing a sort on account_name of the file accounts from journal (for example account_name = “test”).
It gives me three journal records. I guess it must return just 1 master record regardless of how many related records it has.

Then I am doing a search on a field called “description” in the journal_history file from the journal file.
If there are multiple child records matched it must return one record master journal record. But it is not.
If I did not do the sort before hand the search works as expected.

frmObj = forms.departmentList;
frmObj.controller.find();
frmObj.account_id__journal_to_account.account_name = “test”;
frmObj.controller.search();
//returns 1 record

frmObj = forms.departmentList;
frmObj.controller.sort(“account_id__journal_to_account.account_name”);
//returns 3 records if there are three entries for the account “test”

frmObj = forms.departmentList;
frmObj.controller.find();
frmObj.department_id__department_to_employee.description = “%test%”;
frmObj.controller.search();
//returns 2 records (there are 2 matching records) //it must return 1 master journal record

Is this the expected behavior or a bug?

Thanks
Hameed

2

expected behaviour because there are 2 or 3 related records matching that 'test’you will get 2 times the master record.
We can’t do a distinct because of the sort for example if you have 2 related records both pointing to the same master record but you sort on a field of those related records which isn’t the same. What record should be used when you do distinct on the parent record?

Should it be the fk of the first related record in the order or the second?
That’s why we can’t do a distinct (And some databases even don’t allow it)

3

Hameed,

Go have alook at this entry in Servoy Magazine which addresses the very problem of finding distinct records:
http://www.servoymagazine.com/home/2005/06/tip_avoiding_re.html

Cheers
Harry

4

Thanks Harry & Johan for your note and explanation.

I understand that the servoy now automatically uses a DISTINCT clause if there are no related field in the sort parameter and if a related field is included then the servoy does not include DISTINCT in the sql because it could not know what record to use for the sort? Is this correct understanding or I am messing up with the concept?

Thanks

5

thats about it yes.