Skip to main content

ManyToManyView

Limitations of classic ORM associations​

In Basic Mapping/Associative Mapping, we learned about the classic associative mappings in ORM, including one-to-one, many-to-one, one-to-many and many-to-many.

However, there is one scenario that makes the choice of mapping mode very tangled. To show this scenario, let's start with a familiar scenario.

Undisputed many-to-many association​

Let's look at a piece of DDL

create table book(
    ...omit...
)engine=innodb;;

create table author(
    ...omit...  
) engine=innodb;

create table book_author_mapping(
    book_id bigint unsigned not null,
    author_id bigint unsigned not null
) engine=innodb;
alter table book_author_mapping
    add constraint pk_book_author_mapping
        primary key(book_id, author_id)
;
alter table book_author_mapping
    add constraint fk_book_author_mapping__book
        foreign key(book_id)
            references book(id)
                on delete cascade
;
alter table book_author_mapping
    add constraint fk_book_author_mapping__author
        foreign key(author_id)
            references author(id)
                on delete cascade
;

In this piece of DDL, book_author_mapping table is very special, it only has two foreign keys, one pointing to book table, and one pointing to author table. This kind of subtable with only two foreign keys is used to express the many-to-many association between two parent tables.

ORM's many-to-many mapping will hide the intermediate table, that is, the intermediate table does not correspond to a Java/Kotlin entity type. Therefore, the intermediate table does not need an independent primary key, but directly uses the two foreign keys as a combined primary key.

info

In addition to the two associative foreign keys, the intermediate table must not have any other fields, which is a limitation of many-to-many associations in ORM.

The corresponding many-to-many association in ORM is as follows:

  • Owning side: Book.authors

    Book.java
    @Entity
    public interface Book {

        @ManyToMany
        List<Author> authors();

        ...other code omitted...
    }

  • Inverse side (optional): Author.books

    Author.java
    @Entity
    public interface Author {

        @ManyToMany(mappedBy = "authors")
        List<Book> books();

        ...other code omitted...
    }

Undisputed double many-to-one association​

Let's look at the second scenario, first, look at a piece of DDL

create table order_(
    ...omit...
) engine=innodb;

create table product(
    ...omit...
) engine=innodb;

create table order_item(
    id bigint unsigned not null auto_increment primary key, 
    order_id bigint unsigned not null,
    product_id bigint unsigned not null,
    quantity int not null,
    unit_price numeric(10, 2) not null
) engine=innodb;
alter table order_item
    add constraint business_key_order_item
        unique(order_id, product_id)
;
alter table order_item
    add constraint fk_order_item__order
        foreign key(order_id)
            references order_(id)
;
alter table order_item
    add constraint fk_order_item__product
        foreign key(product_id)
            references product(id)
;

This is a classic order - order item - product association.

Among them, order-item looks a bit like the intermediate table of many-to-many, because it has two foreign keys: order_id pointing to the order_ table and product_id pointing to the product table.

However, order-item is not an intermediate table, because it has other business fields, quantity representing quantity of goods, and unit_price representing a snapshot of the price of goods at the time of order.

Fortunately, for the classic order - order item - product association, it is more intuitive to think of order_item as an independent entity, with two many-to-one associations pointing to order_ and product respectively; Rather than viewing order-item as a join table and thinking there is a many-to-many relationship between order_ and product.

It is precisely because order_item is regarded as an independent entity that order_item uses an independent primary key.

We can map these three tables in ORM using two many-to-one associations

  • OrderItem.order and OrderItem.product

    OrderItem.java
    @Entity
    public interface OrderItem {

        @ManyToOne
        Order order();

        @ManyToOne
        Product product();

        int quantity();

        BigDecimal unitPrice();

        ...other code omitted...
    }

  • Order.items

    In such systems, it is often necessary to get the detail list according to the order, so we define a one-to-many property Order.items as a mirror of OrderItem.order.

    Order.java
    @Entity
    @Table(name = "ORDER_")
    public interface Order {

        @OneToMany(mappedBy = "order")
        List<OrderItem> items();

        ...other code omitted...
    }

  • Do not provide Product.items

    In such systems, it is rarely necessary to get the detail list based on the product (if starting the analysis from the product side, it is usually a complex query rather than a simple association), so a one-way OrderItem.product association is sufficient.

    So there is no need to show the code for the Product entity.

The controversial, tangled scenario​

In the previous text, we showed two business scenarios

  • Scenario 1: The intermediate table book_author_mapping is very clean, with only two foreign key fields, and is naturally mapped to a many-to-many association

  • Scenario 2: order_item looks like an intermediate table but is not an intermediate table, because in addition to the two foreign keys it also needs to have other business fields. Fortunately, people will choose to treat OrderItem as an independent entity and use two many-to-one associations to string the three entity types together.

Next, let's look at scenario 3, first look at the DDL:

create table student(
    ...omit...
)engine=innodb;;

create table course(
    ...omit...
) engine=innodb;

create table learning_link (
    id bigint unsigned not null auto_increment primary key,
    student_id bigint unsigned not null,
    course_id bigint unsigned not null,
    score int
) engine=innodb;
alter table learning_link
    add constraint pk_student_course_mapping
        primary key(student_id, course_id)
;
alter table learning_link
    add constraint fk_student_course_mapping__student
        foreign key(student_id)
            references student(id)
                on delete cascade
;
alter table learning_link
    add constraint fk_student_course_mapping__course
        foreign key(course_id)
            references course(id)
                on delete cascade
;

This piece of DDL represents the elective course system in schools. There is a many-to-many relationship between students and elective courses.

  • For students, it is of course very important to know which courses they have chosen

  • For schools, it is also very important to know which students have chosen each course, because teacher resources and teaching venues need to be arranged based on this information

That is, for the student entity and the course entity, associating with each other and querying each other is an important and high frequency operation. Therefore, abstracting bidirectional many-to-many associations between student and course is a very good choice.

Unfortunately, the elective relationship table learning_link has a score field that can be null, null indicating that the exam has not yet taken place, non-null indicating the score after the exam.

  • Because of the existence of this field, the intermediate table is no longer clean, and cannot be simply mapped to the classic ORM many-to-many association. That is, this is not a simple scenario 1.

  • Of course, we can handle this problem using the method of scenario 2, treating learning_link as an independent entity, and using two many-to-one associations to string the three entity types together.

    However, for a large part of the upper layer business, what they really care about is only the mutual association between student and course, and they don't care about the score field of the learning_link table. (That is, the non-foreign key business field learning_link.score of the intermediate table is far less important than the relevant fields in scenario 2). In this case, scenario 2's solution will bring a higher cognitive burden, and scenario 1's way of thinking is obviously simpler.

info

This scenario is actually the most tangled scenario in classic ORM, which cannot be simply mapped to many-to-many associations, while hoping that some upper layer business can adopt the many-to-many mentality to simplify the problem.

@ManyToManyView is a powerful tool designed for such scenarios.

Getting Started with ManyToManyView​

For the discussed scenario 3 above, Jimmer gave a two-step solution.

  1. Basic association mapping:

    First, treat learning_link as an independent entity according to scenario 2, and provide two many-to-one associations pointing to student and course respectively; Conversely, student and course also use one-to-many associations to reference learning_link. That is, use two bidirectional one-to-many associations to string the three entity types together.

  2. Use @ManyToManyView:

    Then, on the basis of the first step, use @ManyToManyView to quickly simulate the effect of scenario 1.

Basic relationship mapping​

  • LearningLink.student and LearningLink.course

    LearningLink.java
    @Entity
    public interface LearningLink {

        @ManyToOne
        Student student(); ①

        @ManyToOne
        Course course(); ②

        Integer score();

        ...other code omitted...
    }

    Where ① and ② will be referenced by subsequent code

  • Student.learningLinks

    Student.java
    @Entity
    public interface Student {

        @OneToMany(mappedBy = "student")
        List<LearningLink> learningLinks(); ③

        ...other code omitted...
    }

    Where ③ will be referenced by subsequent code

  • Course.learningLinks

    Course.java
    @Entity
    public interface Course {

        @OneToMany(mappedBy = "course")
        List<LearningLink> learningLinks(); ④

        ...other code omitted...
    }

    Where ④ will be referenced by subsequent code

Using @ManyToManyView​

  • Simulate many-to-many association: Student.courses

    Student.java
    @Entity
    public interface Student {

        @ManyToManyView(
            prop = "learningLinks", ❶ -> ③
            deeperProp = "course" ❷ -> ② 
        )
        List<Course> courses();

        // The one-to-many association `learningLinks` 
        // has been declared in step 1
        @OneToMany(mappedBy = "student")
        List<LearningLink> learningLinks(); ③

        ...other code omitted...
    }

    The current property Student.courses can be obtained in two steps:

    • ❶ prop = "learningLinks"

      First, get all LearningLink objects through another property of the current entity Student.learningLinks ③

    • ❷ deeperProp = "course"

      For each LearningLink object obtained in the previous step, Course can be further obtained through the LearningLink.course property ②

      tip

      Since the LearningLink entity has only one many-to-one association pointing to the Course entity, there is no ambiguity, so deeperProp = "course" can be omitted here

  • Simulate many-to-many association: Course.students

    Course.java
    @Entity
    public interface Course {

        @ManyToManyView(
            prop = "learningLinks", ❶ -> ④
            deeperProp = "student" ❷ -> ①  
        )
        List<Student> students();

        // The one-to-many association `learningLinks` has been declared in step 1
        @OneToMany(mappedBy = "course")
        List<LearningLink> learningLinks(); ④

        ...other code omitted...
    }

    The current property Course.students can be obtained in two steps:

    • ❶ prop = "learningLinks"

      First, get all LearningLink objects through another property of the current entity Course.learningLinks ④

    • ❷ deeperProp = "student"

      For each LearningLink object obtained in the previous step, Student can be further obtained through the LearningLink.student property ①

      tip

      Since the LearningLink entity has only one many-to-one association pointing to the Student entity, there is no ambiguity, so deeperProp = "student" can be omitted here

The essence of @ManyToManyView​

  • The property declared by @ManyToManyView does not maintain its own data, it is only a view of the original property, which proxies and wraps the collection returned by the original property.

    Take the Java example in this article Student side as an example (the Course side is the same).

    • Original association property: List<LearningLink> learningLinks();

    • View association property: List<Course> courses();

    You can understand the ManyToManyView view property with this pseudocode:

    @Override  
    public List<Course> courses() {
        return new ListProxy<>(
            this.learningLinks(),  
            LearningLink::course
        );
    }

    The view association property returns a proxy collection that wraps the collection of the original property and transforms the elements of the original collection.

    It is obvious that the two are essentially the same and share the same data.

  • When constructing entity objects, only the original property can be set, not the view property.

    This is different from @IdView. For @IdView, both the original property and the view property can be set.

  • Whether it is the original property or the view property, the preprocessor will generate code to match them, so both can be used in object fetchers and strongly typed SQL DSL.