Search in related records

Classic Servoy
1

Hi All ,

I am in a situation in which i need to do Find and Search operation on the fields of two different relations. Find operation is working fine when related records are existing for both the relations. But suppose we do not have any related record for the second relation then the find operation is not working even for first relation fields.This is showing empty foundset.

Sample Code:

if(controller.find()){
relation1.field1 = criteria;
controller.newRecord();
relation2.field2 = criteria;
controller.search();
}
Am i missing something ? .Waiting for your suggestions.Please kindly look into the problem.

Thanks,
Ashutos

2

Could you paste the resulting query that servoy is sending out to the DB? You can get it from the admin pages, just clear the performance result before running your method so that it’s easiest to find the correct query.

3

Thanks for the reply.

I have checked the query from the performance page. The problem lies in the Join type. In the resulting query the Join type is “INNER JOIN”. When I have executed the same query in my database query browser with join type “LEFT JOIN”, it worked fine. So I have modified the join type of the relationship to “Left outer join”. Still in performance page it is showing the resulting query with “Inner Join”.

So is there any other way to force the join type to Left join ? Currently I am working in Servoy 5.2.4 with PG as backend.

4

You should modify both both relations to LEFT OUTER, then it should work correctly.

5

Sorry there is only one relationship and I have already changed the join type of that relationship to “Left outer join” . Also restarted the developer. Still the resulting query is performing Inner join.

Is there any issue of Join types in Servoy 5.2.4 ?

6

Not that I’m aware of. Sorry.

7

There is a problem with join types in 5.2.4 changing to inner joins when table filter parameters are used. See topic http://forum.servoy.com/viewtopic.php?f=3&t=15008

If you are using table filter parameters you may be hitting the same problem we did.

8

Ashutos,

I think Iain is right, this is probably the same issue as mentioned in the thread he refers to.
This is fixed in Servoy 6, please try with the latest version.

Rob