Hi.
When I worked on an SAPUI5 Application, I ran into the problem of implementing a many to many relation into my OData Service. After searching the Internet for a long time, i wasn't satisfied with any solution, so I asked some professionals and this is what I learned so far. There might be a better solution for this problem, but this is how I did it and I think that it is a nice solution.
When trying to implement a Many to Many (or m to n) relation the best way to do it, is not to do it. Hear me out.
The thing is, that the OData service is not really connected to your Database. That means that the logic from the Database can be different from the logic in your OData service. I found that this is not clear when starting with OData.
To make it more clear, I will guide you through the process with an example.
For this example I will use the classic "teacher - student" relation. I am going to tell you already that this example is not perfect, as you will see, the Data Model is not perfectly normalised -- but it should be able to convey what i am trying to explain.
This would be our ER-Model:
As you can see, the student has some attributes, the teacher has some attributes and they are connected by a n to m relation, which also has an attribute, subject.
Now, if you want to implement this in a normalised Database, you would need to create 3 Tables. One for each of the entiities and one for the many to many relation. It would look like this:
Student (SID, Name)
Teacher (TID, Name)
StudentTeacher(SID, TID, Subject)
(Bold= Key, Italics = Foreign Key)
Now the first instinct for an unexpierenced OData & ABAP Developer (like myself) would try to replicate this structure in the OData Service. This is not possible. To solve this Problem, you have to take a step back and look at what your application actually needs.
In our example, we would probably need a list of all the teachers of a student, and a list of all the subjects of a teacher and the corresponding students. To do this, you could create a structure that fits your needs and then build an entity type based on that structure in your OData service.
The structures could look like this:
StudentsTeacherStructure( SID, Name, TID, Name)
TeachersSubjectsAndStudentsStructure( TID, Subject, SID, Name)
Note: I do not recommend using these names for the structure. Check with the popular conventions.
Now, when you have created the entity types from these structures, you will need to write the CRUD Methods.
And this is where your two logics meet: Here you have to stich the tables together or rip them apart into their corresponding fields.
The following code snippets are more of a pseudo code type, I did not test them, but you should get the idea:
StudentsTeacher: GET_ENTITYSET
TeacherSubjectsAndStudents : CREATE_ENTITY
As you can see, you can easily pick fields from the structure and put them back into the normalised database.
What you can also see is that the structure, or the entity that is provided by the OData service is not normalised because it has redudancies.
I hope you were able to understand what I am trying to explain.
If you have any question, feel free to post them in the comments.
Good luck with your project!
Anton Ochel
When I worked on an SAPUI5 Application, I ran into the problem of implementing a many to many relation into my OData Service. After searching the Internet for a long time, i wasn't satisfied with any solution, so I asked some professionals and this is what I learned so far. There might be a better solution for this problem, but this is how I did it and I think that it is a nice solution.
When trying to implement a Many to Many (or m to n) relation the best way to do it, is not to do it. Hear me out.
The thing is, that the OData service is not really connected to your Database. That means that the logic from the Database can be different from the logic in your OData service. I found that this is not clear when starting with OData.
To make it more clear, I will guide you through the process with an example.
For this example I will use the classic "teacher - student" relation. I am going to tell you already that this example is not perfect, as you will see, the Data Model is not perfectly normalised -- but it should be able to convey what i am trying to explain.
This would be our ER-Model:
As you can see, the student has some attributes, the teacher has some attributes and they are connected by a n to m relation, which also has an attribute, subject.
Now, if you want to implement this in a normalised Database, you would need to create 3 Tables. One for each of the entiities and one for the many to many relation. It would look like this:
Student (SID, Name)
Teacher (TID, Name)
StudentTeacher(SID, TID, Subject)
(Bold= Key, Italics = Foreign Key)
Now the first instinct for an unexpierenced OData & ABAP Developer (like myself) would try to replicate this structure in the OData Service. This is not possible. To solve this Problem, you have to take a step back and look at what your application actually needs.
In our example, we would probably need a list of all the teachers of a student, and a list of all the subjects of a teacher and the corresponding students. To do this, you could create a structure that fits your needs and then build an entity type based on that structure in your OData service.
The structures could look like this:
StudentsTeacherStructure( SID, Name, TID, Name)
TeachersSubjectsAndStudentsStructure( TID, Subject, SID, Name)
Note: I do not recommend using these names for the structure. Check with the popular conventions.
Now, when you have created the entity types from these structures, you will need to write the CRUD Methods.
And this is where your two logics meet: Here you have to stich the tables together or rip them apart into their corresponding fields.
The following code snippets are more of a pseudo code type, I did not test them, but you should get the idea:
StudentsTeacher: GET_ENTITYSET
method IDEATIONSET_GET_ENTITYSET.
DATA: LS_KEY_TAB TYPE /IWBEP/S_MGW_NAME_VALUE_PAIR,
LT_DATA TYPE TABLE OF ZTEST_STUDENTSTEACHERSTRUCTURE.
SELECT ZTEST_STUDENT~SID as SID,
ZTEST_SUTDENT~NAME as NAME,
ZTEST_TEACHER~TID as TID,
ZTEST_TEACHER~NAME as NAME
FROM ZTEST_STUDENTTEACHER
INNER JOIN ZTEST_STUDENT ON ZTEST_STUDENT~SID = ZTEST_STUDENTTEACHER~SID
INNER JOIN ZTEST_TEACHER ON ZTEST_TEACHER~TID = ZTEST_STUDENTTEACHER~TID
INTO TABLE @LT_DATA
WHERE ZTEST_STUDENT~SID = LS_KEY_TAB-VALUE.
ET_ENTITYSET = LT_DATA.
endmethod.TeacherSubjectsAndStudents : CREATE_ENTITY
method TEACHERSUBJECTSANDSTUDENTS_CREATE_ENTITY.
TYPES: TT_STUDENT_DATA TYPE TABLE OF ZTEST_STUDENT,
TS_STUDENT_DATA TYPE LINE OF TT_STUDENT_DATA,
TT_STUDENTTEACHER TYPE TABLE OF ZTEST_STUDENTTEACHER,
TS_STUDENTTEACHER TYPE LINE OF TT_STUDENTTEACHER.
DATA: LS_DATA TYPE ZTEST_TEACHERSIBJECTSANDSTUDENTSSTRUCTURE,
LS_STUDENT TYPE TS_STUDENT_DATA,
LS_STUDENTTEACHER TYPE TS_STUDENTTEACHER.
CALL METHOD IO_DATA_PROVIDER->READ_ENTRY_DATA
IMPORTING
ES_DATA = LS_DATA.
DATA LV_ID TYPE ZTEST_STUDENT-SID.
SELECT MAX( SID ) FROM ZTEST_STUDENT INTO @LV_ID.
LS_DATA-SID = LV_ID + 1.
"fill local structure data with frontend data
LS_STUDENT-SID = LS_DATA-SID.
LS_STUDENT-NAME = LS_DATA-NAME.
LS_STUDENTTEACHER-SID = LS_DATA-SID.
LS_STUDENTTEACHER-TID = LS_DATA-TID.
LS_STUDENTTEACHER-SUBJECT = LS_DATA-SUBJECT.
INSERT INTO ZTEST_STUDENT VALUES LS_STUDENT. "add Student
INSERT INTO ZTEST_STUDENTTEACHER VALUES LS_STUDENTTEACHER. "add new connection
endmethod.As you can see, you can easily pick fields from the structure and put them back into the normalised database.
What you can also see is that the structure, or the entity that is provided by the OData service is not normalised because it has redudancies.
I hope you were able to understand what I am trying to explain.
If you have any question, feel free to post them in the comments.
Good luck with your project!
Anton Ochel
- SAP Managed Tags:
2 Comments
