I just found out that doing an initial sort via a relation applies implicitly an inner join. This of course may have an influence of the displayed number of records of the table the forms is based on. Assume you have a simple table view with some fields on it and not initial sorting via relation. ALL table content (i. e. all records of the table) are displayed as expected.
Now add an initial sort via relation (to a related field) and you only get a subset of the number of records of the table displayed. The assumption for this of course is that there are some values missing in the related sort field.
My conclusion: Servoy applies an inner join for that relation!
This does NOT happen for the exactly same (related) field used as an ordinary field (to display), for example!
I find this a quite “dangerous” behaviour, as it is inconsistent and not obvious.
I would propose NOT to apply a inner join for an initial sort field via relation. As it means I can never show all the tables content when using a related sort field.
Servoy has always worked this way and if you translate it to SQL it makes sense it shows all records when not sorting on a related field and a subset when you do.
Having said that I am with you that in 3.5 now with the new query engine and the option to use outer-joins Servoy should change this behavior and make it (programatically) optional to use either join type.
This way you can counter situations where you DO want to use an inner-join.
Ok, obviously, I didn’t realize the current behaviour clearly. For getting a subset when sorting is now as you say not so obvious any more (may be it was not so previously) but sure this was debattable. Today, consistently we should have the choice as we do with ordinary relations.
Did you already file a request?
Best regards, Robert
ROCLASI:
Hi Robert,
Servoy has always worked this way and if you translate it to SQL it makes sense it shows all records when not sorting on a related field and a subset when you do.
Having said that I am with you that in 3.5 now with the new query engine and the option to use outer-joins Servoy should change this behavior and make it (programatically) optional to use either join type.
This way you can counter situations where you DO want to use an inner-join.
I read your thread and fully support your opinion! One would in no way ever anywhere (in any tool) expect a sort to change the number of records, would one?
I could have understand if a related field in a form would have behaved like having an inner join (but it doesn’t), but not a sorting criteria.
Regards, Robert
david:
i’m not sure this is something they will change at this point:
The fact that it effects sorts as well is the other gotcha. We’ve taken to making left outer joins the default on all of our relationships. Not such a bad approach and I guess if we ever do need to have the inner-join behavior it is there.
Seeing records go missing certainly messed with our heads for a couple of days there!
EDIT: being able to programatically specify which relationship type you want to use in a search/sort is a great idea.
I just found out that doing an initial sort via a relation applies implicitly an inner join. This of course may have an influence of the displayed number of records of the table the forms is based on. Assume you have a simple table view with some fields on it and not initial sorting via relation. ALL table content (i. e. all records of the table) are displayed as expected.
Now add an initial sort via relation (to a related field) and you only get a subset of the number of records of the table displayed. The assumption for this of course is that there are some values missing in the related sort field.
Robert,
When a relation is marked as ‘left outer join’, records of the main table should never be skipped by doing a sort.
Is the join type of the relation not used in your case?
The relation is not marked as ‘left outer join’ as it is the generated relation from the Entity Relationship Model and this is by your default an ‘inner join’ relation - and that is absolutely correct in my opinion. Now of course I would like to mainly use the “original”, real relations defined in the model, for obvious reasons. What you are asking (if I understand you correctly) is either
I change the “original” relation to a ‘left outer join’ or
I define a new relation in Servoy (with another name) which is the same as the “original” relation just as a ‘left outer join’ relation
The first is not an option as it would generally change the behaviour of the ERM design (I never saw anyone doing that in any company I modelled ERMs). In my opinion not an option.
The second option is possible but one has to add the same relation again, naming it differently with just the difference of being a ‘left outer join’ relation! This option has very little charm as it bloates up the whole application! And, it has to be done manually!
But I would like you to ask again: Would you ever expect that sorting a column changes the amount of records? I think you would not be very happy if you sort a column in a spreadsheet like Numbers or Excel and that would (automatically) change the amount of records! Same goes for a database table. So my only conclusion is that sorting per definition may never change the amount of records. So I assume you have implicitly to make sure (in Servoy) that this is always guaranteed. otherwise it really get’s very complicated and error prone. Don’t you think so?
Best regards, Robert
rgansevles:
Robert,
When a relation is marked as ‘left outer join’, records of the main table should never be skipped by doing a sort.
Is the join type of the relation not used in your case?