How to zip nested lists with reactor and R2dbc

Question

I have 3 tables in a postgres data base and am using R2dbc to query and connect them in a relational manner.

I have 3 entity classes (possibly shouldn't be data classes, but shouldn't effect the example)

@Entity
@Table(name = "parent", schema = "public", catalog = "Test")
data class MyParentObject(
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Id
    @org.springframework.data.annotation.Id
    @Column(name = "id")
    var id: Int = 0,

    @Transient
    var childData: List<MyChildObject>? = null
)

@Entity
@Table(name = "child", schema = "public", catalog = "Test")
data class MyChildObject(
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Id
    @org.springframework.data.annotation.Id
    @Column(name = "id")
    var id: Int = 0,

    @Column(name = "parent_id")
    var parentId: Int? = null

    @Transient
    var grandchildData: List<MyGrandchildObject>? = null
)

@Entity
@Table(name = "grandchild", schema = "public", catalog = "Test")
data class MyGrandchildObject(
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Id
    @org.springframework.data.annotation.Id
    @Column(name = "id")
    var id: Int = 0
    
    @Column(name = "child_id")
    var childId: Int? = null
)

parent is one-to-many to child which is one-to-many of grandchild. parent_id and child_id act like fkeys.

I have a RestController which can return all Parent data populated with all child Data through these methods

fun viewAllParents(): Mono<MutableList<MyParentObject>> =
    parentRepository.findAll()
        .flatMap { Mono.just(it).addChildData(it.id) }
        .collectList()

fun Mono<MyParentObject>.addChildData(id: Int): Mono<MyParentObject> =
    this.zipWith(childRepository.getAllByParentIdEquals(id).collectList())
        .map {
            it.t1.childData = it.t2
            it.t1
        }

And I have another RestController that can return all ChildData with all Grandchild data (much the same as above) through these methods

fun viewAllChildren(): Mono<MutableList<MyChildObject>> =
    childRepository.findAll()
        .flatMap { Mono.just(it).addGrandchildData(it.id) }
        .collectList()

fun Mono<MyChildObject>.addGrandchildData(id: Int): Mono<MyChildObject> =
        this.zipWith(childOfChildRepository.getAllByChildIdEquals(id).collectList())
            .map {
                it.t1.childOfChildData = it.t2
                it.t1
            }

What I can't do and is my question, is how do I get viewAllParents() to also populate with Grandchild data. Do I need to convert var grandchildData: List<MyGrandchildObject> to a Flux and zip it with a new flux from the grandchildRepository? Or Am I looking at this the wrong way?

Any pointers would be much appreciated.

Answer 1

I really liked the challenge of hierarchial data fetch using reactor. I don't know Kotlin but i have tried to reproduce the problem using java. I couldn't create a PostgreSQL table with the parent -> child -> grandChild hierarchy but i tried to simulate something similar via webclient( basically the logic would remain same). This is my code and this is what i tried to do and was able to get the result what you intended : https://github.com/harryalto/reactive-springwebflux

The crux of the solution is in the Handler code where i am using to build a sub flow based on list of childs and using that to tie together all

public Flux<Parent> getFamiliesHierarchy() {

        return getAllParents()
            .flatMap(parent ->
                    getAllChildsList(parent.getId())
                            .flatMap(childList -> getChildsWithKids(childList))
                            .map(childList -> parent.toBuilder().children(childList).build()
                            )

            );
    }

Below is the complete code

@Component
@Slf4j
public class FamilyHandler {

    @Autowired
    private WebClient webClient;

    public Flux<Parent> getAllParents() {
        return webClient
            .get()
            .uri("parents")
            .retrieve()
            .bodyToFlux(Parent.class);
    }

    public Mono<List<Child>> getAllChildsList(final Integer parentId) {
         ParameterizedTypeReference<List<Child>> childList = 
             new ParameterizedTypeReference<List<Child>>() {};
        return webClient
            .get()
            .uri("childs?parentId=" + parentId)
            .retrieve()
            .bodyToMono(childList);
    }

    public Flux<GrandChild> getGrandKids(final Integer childId) {
         return webClient
            .get()
            .uri("grandKids?childId=" + childId)
            .retrieve()
            .bodyToFlux(GrandChild.class);
    }

    public Mono<List<GrandChild>> getGrandKidsList(final Integer childId) {
         ParameterizedTypeReference<List<GrandChild>> grandKidsList = 
         new ParameterizedTypeReference<List<GrandChild>>() {};
         return webClient
            .get()
            .uri("grandKids?childId=" + childId)
            .retrieve()
            .bodyToMono(grandKidsList);
    }

    private Mono<List<Child>> getChildsWithKids(final List<Child> childList) {
        return Flux.fromIterable(childList).flatMap(child ->
                Mono.zip(Mono.just(child), getGrandKidsList(child.getId()))
                        .map(tuple2 ->        tuple2.getT1().toBuilder().grandChildren(tuple2.getT2()).build())
        ).collectList();
    }

    public Flux<Parent> getFamiliesHierarchy() {

        return getAllParents()
            .flatMap(parent ->
                    getAllChildsList(parent.getId())
                            .flatMap(childList -> getChildsWithKids(childList))
                            .map(childList -> parent.toBuilder().children(childList).build()
                            )

            );
    }

}`

I used json-server for mocking the server

and below is my db.json file

  {
       "parents":[
      {
         "id": 1,
         "name" : "Parent1",
         "path":"1"
      },
      {
         "id": 2,
         "name" : "Parent2",
         "path":"2"
      }
     ],
     "childs":[
     {
         "id": 1,
         "parentId": 1,
         "name": "child1Parent1",
         "path":"1.1"
     },
     {
         "id":2,
         "parentId": 1,
         "projectName": "child2Parent1",
         "path":"1.2"

     },
     {
         "id":3,
         "parentId": 2,
         "projectName": "child1Parent2",
         "path":"2.1"

     },
     {
         "id":4,
         "parentId": 2,
         "projectName": "child2Parent2",
         "path":"2.2"

      }
   ],
   "grandKids":[
   {
     "id":1,
     "childId": 2,
     "projectName": "grandKid1child2Parent1",
     "path":"1.2.1"

   },
   {
     "id":3,
     "childId": 2,
     "projectName": "grandKid1child2Parent1",
     "path":"1.2.2"

  },
  {
     "id":2,
     "childId": 4,
     "projectName": "grandKid1child1Parent2",
     "path":"2.2.1"

  },
  {
     "id":4,
     "childId": 4,
     "projectName": "grandKid1child1Parent2",
     "path":"2.2.2"

  },
  {
     "id":5,
     "childId": 3,
     "projectName": "grandKid1child1Parent2",
     "path":"2.1.1"

  }
  
  ]
}

This is my controller code

@RestController
@Slf4j
public class FamilyController {

    @Autowired
    private FamilyHandler familyHandler;

    @GetMapping(FAMILIES_ENDPOINT_V1)
    public Flux<Parent> viewAllParents() {
         return familyHandler.getFamiliesHierarchy();
    }

}

We can easily retrofit the code for r2DBC repository.

-- UPDATE -- I was able to create the sample data and created an equivalent with R2DBC Here's the link to the gist