Question 419042 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How can I select a Long[] field into my pojo from a lateral join using arrayAgg()?

Here is an example of the query I’m trying to execute and the POJO I’m trying to populate.

public Optional<TeacherWithStudents> getTeachersWithStudentsForStudentId(long studentId) {
  return ctx().select(
                TEACHER.ID,
                TEACHER.NAME,
                DSL.field("student.ids", STUDENT_TEACHER_MAPPING.STUDENT_ID.getDataType().getArrayType()))
        .from(TEACHER)
        .leftJoin(DSL.lateral(
                DSL.select(DSL.arrayAgg(STUDENT_TEACHER_MAPPING.STUDENT_ID).as("ids"))
                        .from(STUDENT_TEACHER_MAPPING)
                        .where(STUDENT_TEACHER_MAPPING.TEACHER_ID.eq(TEACHER.ID))).as("student"))
        .on(DSL.condition(true))
        .limit(1)
        .fetchOptionalInto(TeacherWithStudents.class);

}

// TeacherWithStudents class

@Data
@AllArgsConstructor
public class TeacherWithStudents {

  long id;
  String name;
  Long[] ids;

}

Disclaimer: The query above is simplified to just the bare elements required to illustrate the problem. The real query joins on additional tables and references those tables in the where clause. If anyone is going to propose a completely different technique, please make sure it accommodates fetching multiple arrays of ids related to TEACHER with corresponding where/and clauses.

I have tried many different variations including

  1. passing Long[].class to DSL.field i.e.DSL.field("student.ids", Long[].class)

  2. This works when I fetch into a record, but not my own pojo

  3. I just get the following error: Caused by: org.jooq.exception.MappingException: An error occurred when mapping record to class [Ljava.lang.Long;

  4. Using DSL.jsonArrayAgg in place of DSL.arrayAgg in the hope that the jackson converter would take over as it does for similar use cases using multiset. In this case, I get an array with a single null value i.e. [null]

  5. I have attempted to use multiset in the SELECT clause which works, but then I’m doing the same correlated sub-query in the SELECT and in the JOIN to enable the where condition at the bottom

  6. I have tried a few variations of custom converters, but I can’t get one to work.

  7. I have tried fetching the Record, calling intoMap() and then using jackson to write to the pojo. This works, but is wasteful.

Based on what I’ve read, it’s expected that the more complex Collection classes e.g. Set<>, List<> are not directly supported (those were my first attempts) except when using multiset because of type erasure. That makes sense, but I am surprised I can’t get Long[].class to work. I have tried dozens of variations. Often, I can get the data to populate to a Long[] if I fetch into a Jooq record, but I can never get it to populate all the way to my defined POJO. I considered giving up and just writing a custom converter from record to POJO, but it seems like this is something that should be possible. I’m convinced I’m doing something wrong.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    This is always embarrassing, but I solved the problem simply by changing the name in the select clause. I was using students.student_ids and assuming that the mapper would correctly map to the studentIds field in the POJO. This is not the case. I changed it to reference only student_ids and it works as expected.

    Here is the final code

        public TeacherWithStudents getTeachersWithStudentsForStudentId() {

        TeacherWithStudents teacher = ctx().select(
                        TEACHER.ID,
                        TEACHER.NAME,
                        DSL.field("ids", STUDENT_TEACHER_MAPPING.STUDENT_ID.getDataType().getArrayType()))
                .from(TEACHER)
                .leftJoin(DSL.lateral(
                        DSL.select(DSL.arrayAgg(STUDENT_TEACHER_MAPPING.STUDENT_ID).as("ids"))
                                .from(STUDENT_TEACHER_MAPPING)
                                .where(STUDENT_TEACHER_MAPPING.TEACHER_ID.eq(TEACHER.ID))).as("student"))
                .on(DSL.condition(true))
                .limit(1)
                .fetchOneInto(TeacherWithStudents.class);

        System.out.println("TEACHER: " + teacher);
        return teacher;

    }

  2. 0

    The DSL.field("student.ids", Long[].class) variant should work, but you have to use it twice, also in that where clause:

    .where(DSL.field("student.ids", Long[].class).contains(studentId))

    See the Field::contains Javadoc. The operator by default works on string types. If jOOQ doesn’t know that the left operand is an array type, then it will use the string semantics by default. You have to tell jOOQ. The reason why you’re getting arrays containing null values might be just that you’re running a wrong query.

    Note that DSL.field("student.ids", STUDENT_TEACHER_MAPPING.STUDENT_ID.getDataType().array()) might be more formally correct, in case you ever change data types.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search