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.
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:
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
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.
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.