I have a people form with a related tab panel of email addresses.
On People ID 1 their is an email address test1@mymail.org
On People ID 3 their are two email addresses test2@mymail.org
and test3@mymail.org
If I do a currentcontroller.find(); and then enter test% in the email field and press enter I get three records found and people record 1 is displayed and then people ID 2 is displayed two times.
What I should get is only two records, People ID 1 and 2.
What can I do about this?
This is normal behavior in ‘SQL land’.
You are searching on a related table and get multiple matches for the same parent record then you get the parent record multiple times.
To evade this behavior you can use the following method:
var query = 'SELECT DISTINCT parenttable.pkfield FROM parenttable, childtable WHERE childtable.emailfield LIKE ? ORDER BY parenttable.pkfield;';
var ds = databaseManager.getDataSetByQuery(controller.getServername(), query, [globals.mySearchField + '%'], 1000);
forms.myFormname.controller.loadRecords(ds);
Ofcourse you need to change the names accordingly.
Hope this helps.
Robert,
I guess I did not make myself clear.
If I do an SQL query I get two records fine, just the way I should, without having to do a select distinct.
What I am doing here is using servoy’s built-in find functionality using currentcontroller.find() to get a blank search form. I then enter the value test% into the email field on the form and press enter. What I should get back in the foundset in two records but I get three (two duplicates). There is no code that I can adjust or fix. This is internal to servoy.
gstein:
Robert,I guess I did not make myself clear.
If I do an SQL query I get two records fine, just the way I should, without having to do a select distinct.
What I am doing here is using servoy’s built-in find functionality using currentcontroller.find() to get a blank search form. I then enter the value test% into the email field on the form and press enter. What I should get back in the foundset in two records but I get three (two duplicates). There is no code that I can adjust or fix. This is internal to servoy.
It’s standard SQL behaviour: you’re probably searching on a related table that has 3 childs (one related to the first record on the main table and two related to the second record on main table, for instance).
Follow Robert suggestion, if you want to avoid these problems (I wrote a small article on the subject for Servoy Magazine, some time ago).
I just noticed I forgot the join in the SQL…but you get the idea
About the double records issue. I guess Servoy needs to supply us with a way to distinct/group by the result set of the main table you are looking at.
Maybe an extra parameter in the controller.search() perhaps ?
Else we always have to resort to pure SQL.
Btw the same issue is there with doing a sort on child tables.
I guess Servoy needs to supply us with a way to distinct/group by the result set of the main table you are looking at. Maybe an extra parameter in the controller.search() perhaps ? Else we always have to resort to pure SQL.
This seems a very good suggestion as the current behavior is odd. The client displays duplicate records for the main table when there really aren’t duplicate records – as a result, the view is not in sync with the database.
JDW
JDW:
I guess Servoy needs to supply us with a way to distinct/group by the result set of the main table you are looking at. Maybe an extra parameter in the controller.search() perhaps ? Else we always have to resort to pure SQL.
This seems a very good suggestion as the current behavior is odd. The client displays duplicate records for the main table when there really aren’t duplicate records – as a result, the view is not in sync with the database.
JDW
From a join point of view there are duplicates: you searched on a daughter record, after which it collects a mother record for each daughter. SQL always does exactly what you ask it to. There is no easy solution to this, also because you’d have to start using INNER and OUTER joins which are not standardized very well.
I submitted this now as a feature request.