Support for one-to-one and one-to-many relationships

[murdos]

Spring Data JDBC supports one-to-one and one-to-many (either as Set, List or Map) relationships: https://docs.spring.io/spring-data/jdbc/docs/current/reference/html/#jdbc.entity-persistence.types

It would be really useful to also have this feature with R2DBC.

[mp911de]

This ticket is a duplicate of #352. Since this one is more focussed, we're closing #352.

[darichey]

Would love to see one-to-one support. For now, I think I have to modify my database schema.

[mp911de]

The reason we cannot provide the functionality yet is that object mapping is a synchronous process as we directly read from the response stream. Issuing sub-queries isn’t possible at that stage.

Other object mapping libraries work in a way, that they collect results and then issue queries for relation population. That correlates basically with collectList() and we’re back to all disadvantages of blocking database access including that auch an approach limits memory-wise consumption of large result sets.

Joins can help for the first level of nesting but cannot solve deeper nesting. We would require a graph-based approach to properly address relationships.

[mspiess]

Would it be possible if the type of the relation was Flux instead of a collection? Of course that would limit it to lazy loading, but it might be good enough as a compromise for the time being. I was assuming one-to-many here, but the approach would be the same for one-to-one with Mono.

[mp911de]

Modeling a domain with Mono's and Fluxes rather pollutes your domain with things that do not belong in there. Imagine an Order with Flux<OrderLines>. There's no chance to add another one. Having a ReactiveRelation<T> could be built, but honestly, adding reactive types to a domain model introduces a lot of complexity. We recommend rather a lookup of relations where it applies.

[thachhuynh]

So for now, can we use spring data jpa annotations instead?

[Bittuw]

@mp911de, parallel != reactive. We still can read relationship between entities. We have two ways:

1. Sequence of requests in reactive manner;
2. Create VERY BIG JOIN;

I am for the first option, because we can easy resolver circle dependencies between responses + if RDMBS provide real async, we already can request the next part of data from relationship entity (Which have already come). In the second we have to do complex analyze of answer from RDMBS which will be very ugly.

[wickedev]

What if introduce concept of CallAdapterFactory, in "retrofit" which is RESTful API client library. by introducing it, community can be handle wrapper type with adaptor. List(eagar, one to many), Set, Deferred, Observable, Flow, Reactive Streams, Mono, Flux whatever else. This way should not have to get hands dirty at the core library level.

Retrofit retrofit =  
  new Retrofit.Builder()
      .baseUrl(apiBaseUrl)
      .addConverterFactory(GsonConverterFactory.create())
      .addCallAdapterFactory(RxJavaCallAdapterFactory.create())
    .build();

public interface GistService {  
    // access Gists with default call adapter
    @GET("gists") 
    Call<List<Gist>> getGists(); // Retrofit Default

    // create new Gist with RxJava call adapter
    @POST("gists")
    Observable<ResponseBody> createGist(@Body Gist gist); // Handle by RxJavaCallAdapterFactory
}

The Node.js community also has a similar problem. For TypeORM, Eager relations use Array, Lazy relations use Promise.
https://github.com/typeorm/typeorm/blob/05259795f0ff4a0241647ccfb621f2a5f55b3d34/docs/eager-and-lazy-relations.md

[mp911de]

In contrast to HTTP, relations need to be fetched from the same connection to ensure transactional isolation. In addition, R2DBC is a streaming API which means that the entire SELECT result needs to be consumed from the server first, before we can issue additional queries. Spring Data R2DBC doesn't collect the results into a List first but converts and emits results as they are received.

[pmaedel]

Relationships with eager fetching is already possible when one writes their own queries and then uses R2dbcConverter, though that usecase could see improvement: #448

I guess with some documentation and examples, people could get by without builtin support quite comfortably.
I would furthermore argue, that the approach of defining the specific join query & model for the respectively needed relationship is more reasonable altogether then trying to find a one-size-fits-all solution as we know from JPA where the relationships and their fetch types are defined within a single model. This removes flexiblity to decide per use case whether a join is needed or not.

Just one short example of how many different patterns are possible and possibly desired to query for in an exclusive manner:

@Table("student") data class Student(val id: Long?, val name: String)
@Table("teacher") data class Teacher(val id: Long?, val name: String)
@Table("seminar") data class Seminar(val id: Long?, val teacher: Long, val student: Long, val name: String)

//possiblities: 
data class SeminarJoined(val seminar : Seminar, val students: List<Student>, val teacher: Teacher)
data class SeminarsOfTeacher(val teacher: Teacher, val seminars: List<Seminar>)
data class SeminarsOfStudent(val student: Student, val seminars: List<Seminar>)
data class TeachersOfStudent(val student: Student, val teachers: List<Teacher>)
data class StudentsOfTeacher(val teacher: Teacher, val students: List<Student>)

[jiangtj]

I hope Fluent API can support join()