Displaying attributes of a join

[birgit]

Hi

We have defined a select with one inner join and one left outer join (3 tables). Now we would like to display the result attributes in our form. How can we do this? We find the form to be limited to load only primary key attributes of the forms table. But we would like to display attributes of the other (joined) tables as well.

The returned dataset is correct. But since we want to display more than pk attributes of the forms table, loadRecords does not work. What other options do we have?

Thanks for any help and best regards
Birgit

[Joas]

To display values over multiple relations, you can use a label. Make sure you check the “displaysTags”-property and fill the text property like: ```
%%relation1name.relation2name.fieldname%%

[Robert_Huber]

Hi Joas

As Birgit just had to leave I would like to thank you in her name for your answer. We tried your idea. The problem with it is that the relationship between the two tables with the left outer join is a 1:m relationship whereas the m-side table is the “right” table in the left outer join. Now, as we have to base the form on the 1-side table, the displayed content of the label with the relationship to the m-side shows (seems to show) the first entry in that m-side table instead of the correct row as in the SQL statement (returns with the SELECT … LEFT OUTER JOIN … ON …) a correct foundset. But we couldn’t find a way to display that foundset yet.
What I would like to say (what is the problem in principal) is that we have a SELECT statement with the the correct result, but can’t display it on a form because of the “primary key limitation” of the controller.loadRecords(dataset) command, i. e. can’t display the column of the other table. Is there another way of loading a form with a foundset by a SQL query with joins over 2 or more tables?

Best regards, Robert

Joas:
To display values over multiple relations, you can use a label. Make sure you check the “displaysTags”-property and fill the text property like: ```
%%relation1name.relation2name.fieldname%%

[IT2Be]

If I understand your issue correct the answer is yes and no

NO, you can not simply show the records based on the found set.
YES, you can use html for that.

Hope this helps

[Robert_Huber]

Hi Marcel

I think we have to fundamentally understand what Servoy offers (or doesn’t offer) in this respect. If the problem is not simply displaying a master - detail relationship, based on a master table 1:m to a detail table, what are the options?

We realized that we don’t know that.

To come back to our original question: We have 3 entities with these relationships: Person to PersonsRole is 1:m and Person to ClassMember is also 1:m (just for info why 1:m: Because ClassMember contains the period, i. e. the semester).

Now, to get the result as seen in the picture, we need this code:

var query = 
	" SELECT persons.id, class_members.class_level_number, class_members.class_sign" +
	" FROM persons_roles INNER JOIN persons" + 
	" ON (persons_roles.person_id = persons.id)" +
	" LEFT OUTER JOIN class_members" +
	" ON (persons.id = class_members.person_id"+
	" AND class_members.class_period_fraction_name = 'HS'" +
	" AND class_members.class_period_school_year = '2005')" +
	" WHERE persons_roles.role_id = 1" +
	" AND persons_roles.status = 'immatrikuliert'" +
	" AND persons.type = 'natural'" +
	" AND persons.name LIKE 'A%'" +
	" AND persons.profile_code = 'N'" +
	" AND persons.profile_period_fraction_name = 'HS'" +
	" AND persons.profile_period_school_year = '1997'" +
	" ORDER BY persons.name ASC, persons.first_name ASC";

We need to have the class_members columns class_level_number and class_sign (and the omited profile columns which I have done to simplify the example) to get the class_members entry with the HS 2005 (Herbstsemester 2005). But the statement loadRecords() does accept only the (pk) columns from the table persons, so the result is wrong. For example, instead of getting class 2 sb for Achermann Jonas one get’s 1 sb, which is the first entry in table class_members for Achermann Jonas.

We have many times joins over more than one two tables and I hope Servoy supports that somehow, but how?

Best regards, Robert

IT2Be:
If I understand your issue correct the answer is yes and no

NO, you can not simply show the records based on the found set.
YES, you can use html for that.

Hope this helps

[hpmxxx]

Hello Birgit and Robert

If I understand your issue correct, you only have a problem to display data without a pk? In this case in my opinion you have two options:

1. display data via html as Marcel suggested
   or
2. display data via datagrid bean → dataset.getAsTableModel() → Servoy 3.0 beta 3 - Classic Servoy - Servoy Community

I’m not very familiar with the datagrid bean at the moment, but I think this is a very interesting way to display data.

[IT2Be]

The datagrid option is a very good one! Stick your data in a dataset (which you already do by processing the query) and add the data to the grid. Should have thought of that

I understood from Servoy that it is some sort of beta but I have played with it and it works really well.

[Robert_Huber]

Salut Hans-Peter

Merci for the tip with the dataset.getAsTableModel, we will try it.

Best regards, Robert

hpmxxx:
Hello Birgit and Robert

If I understand your issue correct, you only have a problem to display data without a pk? In this case in my opinion you have two options:

1. display data via html as Marcel suggested
   or
2. display data via datagrid bean → dataset.getAsTableModel() → Servoy 3.0 beta 3 - Classic Servoy - Servoy Community

I’m not very familiar with the datagrid bean at the moment, but I think this is a very interesting way to display data.

[Robert_Huber]

hi Marcel

Also thanks for your reply. Just to be sure what Servoy can, resp. can’t in this respect:

• Servoy is only (directly) able to show columns from a given table or a master - detail realtionship with a tabless ta or portal?

• I have no way of displaying (but also getting the record info the user selects) data foundsets done by a more complex join (as described in the original message) than the above mentioned?

If the points mentioned are true, is there the general thinking that stays like this in future versions of Servoy? I would love to hear from Servoy people if it’s not already discussed in the past, as I think this is quite a limmiting factor, as medium complex applicatins usually have such requests in quite a number, but may be I am missing something here (I hope so). Or is the bean created for such requests? Any help shedding light to this in general is appreciated.

Best regards, Robert

IT2Be:
The datagrid option is a very good one! Stick your data in a dataset (which you already do by processing the query) and add the data to the grid. Should have thought of that

I understood from Servoy that it is some sort of beta but I have played with it and it works really well.

[IT2Be]

Well, I am not Servoy so I can not speak for them but I guess the datagrid beta is proof of the fact that they ‘are thinking of/have thought of’ some sort of detaching data from displaying purposes.

I really think the 2 options mentioned are it for the moment.

Hope this helps

[patrick]

I am not 100% sure I understand everything right, so please forgive me if it goes wrong.

To me it seems the problem has two parts:

1. Load a foundset of records
2. Display information that might be a few tables away

While it is a little difficult to find a way for this, I think there is one:

1. You can load a foundset if you comply to the rules. So you could alter your query and select only primary keys.
2. Then you can create a form based on persons and have a calculation there, that calculates the information from class_members that you want

Then the query takes care of the persons that you see and the calcs give you access to the related information.

But I would also like to encourage Servoy to make the datagrid more than beta. Because that really allows you to instantly take your query and display what you queried. That would save me tons of forms that simply display certain stuff in a table!

Patrick

[ROCLASI]

Hi Robert,

From what I understand is that support for outer joins is on the to-do list of Servoy. However no time schedule has been given.
So for now you have to do things with a table grid or HTML.

[john.allen]

Hi Robert,

Just so I understand the problem… As I read your query, it will return data from the parent records (persons table) and from the child records (persons_roles and class_members tables). However it will not return ALL the child records to the parent record based on the PK/FK relationship, only those meeting the additional criteria specified in your query. From a display point-of-view, will the number of child records that meet that outer join criteria always be zero or one? Or could that yield many child records also?

I’m playing around with a couple of things related to this and if I get a chance I’ll check out this specific example and see if they work too. But before I need to know whether this needs to display multiple child records to the parent form or just one (or presumably zero since you are using an outer join).

[Robert_Huber]

Hi John

john.allen:
Hi Robert,

Just so I understand the problem… As I read your query, it will return data from the parent records (persons table) and from the child records (persons_roles and class_members tables).

The query returns records as seen in Picture 1 (see previous post), i. e. Achermann Jonas (student) in class 2 sb with profile S HS 1997 (which means Sprachliches Profil, valid since Herbstsemester 1997). The entity PersonsRole (table persons_roles) exists, because persons can have more than one role, e. g. a teacher has the role teacher and may also have the role father, if his child is (currently) at the school where he is also teacher.

john.allen:
However it will not return ALL the child records to the parent record based on the PK/FK relationship, only those meeting the additional criteria specified in your query. From a display point-of-view, will the number of child records that meet that outer join criteria always be zero or one? Or could that yield many child records also?

Absolutely, and that’s why I can’t have a query with only the pk of the base table, because then I get too many records from the m-side tables. The number of child records (for both m-side tables, i. e. persons_roles and class_members) that meet the outer join criteria will always be one (on the persons_roles side, as every person must at least have one role assigned) or zero or one for the class_members side, as a person can already be in a class (when the semester has begun) or may not be (in the planning phase where students are assigned to classes. That’s by the way the reason why I have to use person as base table (for the outer join), as otherwise I would not get the desired result.

john.allen:
I’m playing around with a couple of things related to this and if I get a chance I’ll check out this specific example and see if they work too. But before I need to know whether this needs to display multiple child records to the parent form or just one (or presumably zero since you are using an outer join).

Good luck and I am very interested in your findings. I assume it’s a fundamental problem Servoy can not handle right now, as it has only storing places (memory space) for the attributes from the base table for the foundset (I assume that’s why one only can specifiy the pk for loadRecords()) whereas a little more complex join queries demands storeage space to put the m-side columns (attributes) in it, as for my example the class info 2 sb. I really hope we get this functionality soon, otherwise it’s only good for basic queries which is surely not sufficient for more complex applications.

Hope my explanations help and looking forward to hear more from you developers (as I am still hoping our findings are not true and we overlooked something in Servoy).

Best regards, Robert

[john.allen]

Robert,

I am probably missing something obvious but I think it is actually simpler to do after reading your response. If (after the where criteria) you are just showing zero or one records from the child tables as you indicate then you can do this just using global relationships. Servoy will do the equivalent of an outer join naturally when showing parent records with child columns displayed.

I’m using data from our database to do it quicker. In the first picture (picture1) there is a simple form based on the parent table (patient in our case). In addition there are two columns from two separate child tables (sample and diagnosis). Notice that even when there is no child record (patient_id 107 and 111 for sample (child1) and 102-103 and 115-118 for diagnosis (child2) these records still show up. When doing a straight SQL query you need to show this via an outer join but with Servoy a form based on the parent table will show columns from related child tables on an ‘as-is’ basis: if the records exist the columns have that data, if not the columns are empty.

In the second picture (picture2) I am showing the same parent records but with the child records further limited to those that relate to the criteria stipulated in the global fields (‘sp’ and ‘l’ respectively). Notice again all parent records are showing (as though through an outer join) but only those child records that match show column entries.

In a simplified form, isn’t that about what you want to display? Using global relationships like this also make it easy for users to easily change the criteria/relationship too of course.

[Robert_Huber]

Hi John

Thanks for the example!

john.allen:
Robert,

I am probably missing something obvious but I think it is actually simpler to do after reading your response. If (after the where criteria) you are just showing zero or one records from the child tables as you indicate then you can do this just using global relationships. Servoy will do the equivalent of an outer join naturally when showing parent records with child columns displayed.

Yes, you are missing a little piece .-)

john.allen:
I’m using data from our database to do it quicker. In the first picture (picture1) there is a simple form based on the parent table (patient in our case). In addition there are two columns from two separate child tables (sample and diagnosis). Notice that even when there is no child record (patient_id 107 and 111 for sample (child1) and 102-103 and 115-118 for diagnosis (child2) these records still show up. When doing a straight SQL query you need to show this via an outer join but with Servoy a form based on the parent table will show columns from related child tables on an ‘as-is’ basis: if the records exist the columns have that data, if not the columns are empty.

That looks good so far.

john.allen:
In the second picture (picture2) I am showing the same parent records but with the child records further limited to those that relate to the criteria stipulated in the global fields (‘sp’ and ‘l’ respectively). Notice again all parent records are showing (as though through an outer join) but only those child records that match show column entries.

I try to show what I need on the basis of your example: I need to show patients with a certain source (sp, out, …) (in my example persons in a certain role). That means I’d like to show for example in Picture 2 only patients with the value sp in source, i. e. patien_id 104, 105 and 112 - 114.
And the entry in child 2 has to be a certain one, n my case defined by the where clause fraction_name and school_year, e. g. HS 2006.

Am I correct that your example is done the FileMaker way with a relationship and setting columns both sides of the relationship and may be sorting used to get the desired result?

Actually, I would like to avoid such programming (as far as possible), because it leads to a (spaghetti pot of relationships, may be sorted and corresponding fields and calculated fields which are not very easy handable (my experience only) whereas a SQL statement doesn’t need any additional, I call them functional (not from the DB design given) relationships and additional fields. But may be the capabilities of Servoy teach me otherwise.

john.allen:
In a simplified form, isn’t that about what you want to display? Using global relationships like this also make it easy for users to easily change the criteria/relationship too of course.

One (main) question with your example remainds: Can you displaying patients having a certain value in source, e. g. sp?

And what are the relationships and (calculated) fields you use?

Thanks a lot in advance, John and your help with an example which is more easily to discuss tha without one!

Best regards, Robert

[john.allen]

Hi Robert,

I too tend to steer away from calculations except for using Servoy’s nifty rowbackground color calculation. I’m not quite sure I understand your description of the relationship so let me start over and just describe exactly what I showed relationship-wise.

Three tables: parent table is patient with PK of patient_id. Two child tables: sample and diagnosis with FK of patient_id. In the first picture the form (based on patient) is just using those simple relationships to show the columns of the child relationships. However, if a particular patient record doesn’t have a child table, the patient record still displays and the child columns are just blank (as picture 1 shows), in other words an outer join.

In picture 2 I haven’t limited the patient records in any way although I could. But what I am doing is changing the relationship to each child record slightly. Instead of just a patient.patient_id to sample.patient_id relationship, I am also adding a second part to the patient-sample relationship (and patient-diagnosis). I am also relating those tables based on the values in the global fields labeled ‘OuterJoinCriteria1’ (for the sample table) and ‘OuterJoinCriteria2’ (for the diagnosis table). What this does is identical to what you are doing in your outer join, namely displaying zero or one CHILD columns that match the criteria of the outer join while retrieving ALL parent records (or that meet the rest of the WHERE criteria): in your case it is ‘HS’ and ‘2005’, in our case it is ‘sp’ and ‘l’.

You then bring up that point namely:

Can you display patients having a certain value in source, e. g. sp?

Naturally you can limit the PARENT table in any way that you want with the overall query that populates the parent form in the first place. So to use an example that could be seen in picture 1…

Let’s say I limit the rows from the parent table like this:

SELECT patient_id 
FROM patient p, diagnosis d
WHERE p.patient_id = d.patient_id
AND d.stage = '2a'

In this case, of the patient records shown currently in picture1, you would only retrieve three patient records: 105, 110 and 112. If you further selected ‘h’ as the ‘OuterJoinCriteria2’, you would only see child2 (diagnosis) column data for 112 but the patient records of 105 and 110 would still be showing of course.

There really isn’t anything different about this than normal querying/relationships. Using globals in a relationship just gives you the power of dynamic relationships that can be set ad hoc by the user or the developer based on events. And any ‘relationship’ itself is simply a ‘query’ as well, just a little more optimized. (Now off to bed for me!)