Quarkus Reactive Postgresql multiple queries

0
votes

I'm trying to construct a query against multiple tables with a one to many relationship.

Thew model I'm trying to return is:

{
  'dept_id' : 123456,
  'dept_name' : 'department 1',
  'employee_ids' : [9,  10, 11, 12, 13]
}

My initial query is gets the data of the parent object:

    public Uni<Department> getDepartmentById(Long deptId) {
        
        return client.preparedQuery("SELECT * from department where department_id = ?1")
                .execute(Tuple.of(deptId))
                .onItem().apply(RowSet::iterator)
                .onItem().apply(iterator -> iterator.hasNext() ? from(iterator.next()) : null);
    }

Though somehow, I need to add in the sub-query for the child ids, something like:

        return client.preparedQuery("SELECT * from department where department_id = ?1")
                .execute(Tuple.of(deptId))
                .onItem().apply(RowSet::iterator)
                .onItem().apply(iterator -> iterator.hasNext() ? from(iterator.next()) : null)
                .onItem().apply(dept -> {
                   client.preparedQuery("select emp_id from employee where dept_id = ?1")
                      .execute(Tuple.of(dept.getDeptId())
                   // for each returned append to an array then add to the department
                   // dept.setEmployeeIds(????)
                });

and then collect the employee_ids into single array to add to the department.

Do I use some Zip approach where I create the employee RowSet first then get the department or is there another transform I need to use to make this work?

2
javavert.xreactivequarkus

2 Answers

1
votes

Okay so I was able to solve this buy splitting up the functions and not trying to do it all at once:

    public Uni<Department> getDepartmentById(Long deptId) {
        log.debug("Looking up department by id {}", deptId);

        return client.preparedQuery("SELECT * from department where department_id = $1")
                .execute(Tuple.of(deptId))
                .onItem().apply(RowSet::iterator)
                .onItem().apply(iterator -> iterator.hasNext() ? from(iterator.next()) : null)
                .onItem().apply(this::getEmployeeIdsForDept).await().indefinitely();
    }


    public Uni<Department> getEmployeeIdsForDept(Department dept) {
        log.debug("Looking for users in department {}", dept.getDepartmentName());

        return client.preparedQuery("select emp_id from employee where dept_id =  = $1")
                .execute(Tuple.of(dept.getDepartmentId()))
                .onItem().apply(RowSet::iterator)
                .onItem().apply(iterator -> {
                    List<Long> temp = new ArrayList<>();
                    while (iterator.hasNext()) {
                        temp.add(iterator.next().getLong("emp_id"));
                    }
                    return temp;
                })
                .onItem().apply(results ->
                    Department.newBuilder().addAllEmployeeIds(results)
                            .setDepartmentId(dept.getDepartmentId())
                            .setDepartmentName(dept.getDepartmentName()).build()
                );
    }

The key thing here was the .await().indefinitely() which threw me since I wasn't sure it this is a blocking operation or not.

0
votes

you could extract data as you need from database without doing extra queries:

select json_build_object('dept_id', d.id, 'dept_name', d.dept_name 'employee_ids', array_agg(emp.id))
from department d join employee emp on emp.dept_id = d.id
where d.id = ?1
group by d.id;