Ask about the use of one-to-many and many-to-many mappings

[UlyssesChou]

Hello, I am learning java development by myself. I hope to get your help. I have a problem that has been plagued for a long time. Under the large amount of data, we know that using many-to-many annotations will cause poor efficiency. Does ebean recommend a more mainstream solution to solve this problem, or does the developer no longer use ManyTomany annotations to solve this problem? I haven't found a good solution to solve my troubles. I want to hear everyone's correct suggestions.

[rbygrave]

I have a problem that has been plagued for a long time. Under the large amount of data, we know that using many-to-many annotations will cause poor efficiency.

You are going to have to be more specific / and or give an example. Can you do that?

[UlyssesChou]

@Entity
@Table(name = "user")
class User(
    @ManyToMany(mappedBy = "users", fetch = FetchType.LAZY)
    var departments: MutableList<Department>? = ArrayList(),
)

@Entity
@Cache
@Table(name = "department")
class Department(
    @ManyToMany
    @JoinTable(
        name = "user_department",
        joinColumns = arrayOf(JoinColumn(name = "department_id")),
        inverseJoinColumns = arrayOf(JoinColumn(name = "user_id"))
    )
    var users: MutableList<User>? = ArrayList(),
)

Assume that the User and Department tables are larger,
How can I properly handle many-to-many relationships with EBean？

[rbygrave]

Yeah that is showing us the mapping, but the mapping isn't the problem per say. You need to show us the use-case where having a large cardinality hurts us. That is, show us the use-case / query that is poor / expensive - that will lead to the options available to deal with it.

Where this is going is that there isn't strictly speaking anything wrong with many-to-many per say, but more a problem with accessing any large cardinality *ToMany without any filtering (no predicates on the query). That is, we have this same issue with OneToMany and this is why ebean has filterMany (to apply a filter/predicates when loading large cardinality ToMany paths).

Your question may distill down to, Q: Can we use filterMany on a ManyToMany path? A: Yes, we can use filterMany on both OneToMany and ManyToMany paths.

filterMany - https://ebean.io/docs/query/filterMany

[rbygrave]

In ORM terms by default predicates apply to the root of the graph.

filterMany predicates are instead applied to a "*ToMany branch" of the graph.

e.g. Lets say a Department has 1 million Users, if we get all the users of a department we will get 1 million of them - we don't want to do that. More often the case is we only want for a given department say the "recent new users". In this example the "recent new users" would be predicates applied to the "user" branch of the graph.

List<Department> departments = 
new QDepartment()
  .name.startsWith("A") // predicate applied to the root of the graph
  .users.filterMany("status = ? and whenCreated > ?", UserStatus.NEW, today) // predicates applied to the ToMany users path
  .findList()

[UlyssesChou]

Thank you very much

I'd like to follow up with a question:
https://stackoverflow.com/questions/68063934/shardingsphere-cannot-query-self-referenced-entity

Of course, this may not be the problem with EBean

However, I hope your experience can help me. After splitting tables with large cardinality, @onetomany and @manytomany can no longer be used. What if I want to continue to simplify table splitting via eBeans? Because I really haven't found a solution that Ebean and ShardingSphere can use together, or do you have a more recommended solution? Looking forward to your reply. Thank you

I am sorry that I am not an English speaker, so I may have some errors in expression

[rbygrave]

So table partitioning / sharding / distributed database is all in all a very very big question in general.

Given you have ended up at "I'm using ShardingSphere" we don't know where you started from and the path / thinking that took you to get to using ShardingSphere. So the questions are what is the general context of the problem, what other options were considered and what lead you to ShardingSphere in particular?

For example, if my starting point is "I'm using Postgres" then we might quickly get to "I'm going to use Postgres table partitioning". A slight generalisation on that is ... If a database provides built in table partitioning then we should really look strongly at using it because table partitioning impacts the database query planner, parallel query execution, index management - lots of interesting stuff INSIDE the database.

Then there is the whole world of "distributed database" which include dealing with distributed query execution (efficiently performing joins across a cluster of nodes) and typically that brings in replication (so that the db can perform some of the joins locally).