Skip to Content
Technical Articles

Implementing a Many to Many relation in OData

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:

ER-Model%20for%20Example

 

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

 

/
ER-Model%20for%20Example
1 Comment
You must be Logged on to comment or reply to a post.
  • A many to many relationship can be achived with OData. What you want to do is create navigation properties from Student -> Teacher and Teacher -> Student with cardinality 1:N

    Your Database model looks good to me and is the way to go. However this can not be translated 1:1 to an OData Service.

    To get all Teacher of a Student you can then use this URI:

    StudentSet(SID=’1′)?$expand=Teachers

    and vice versa:

    TeacherSet(TID=’2′)?$expand=Students

    In your method Teacher: GET_ENTITYSET you call io_tech_request_context->get_navigation_path( ) and find the table has one entry, so you know you are coming from source Entity Student.

    There is no need for intermediate Entity StudentTeacher, as you can create the relation with this request:

    POST TeacherSet(TID=’2′)/Students (with payload of student). This again will call Students : CREATE_ENTITY and with a similiar trick you can see if you want to create the relationship or the student itself