ActiveJDBC: Joined rows of findBySQL with join are empty

Question

I am performing a mildly complex query in a findBySQL call with ActiveJDBC. I receive results as expected, but when I query joind rows with getAll, the results are empty.

List<ClassSession> classSessions = ClassSession.findBySQL("""
        select class_sessions.*
        from class_sessions
                 join class_session_lesson_times on class_sessions.id = class_session_lesson_times.class_session_id
                 join class_session_lesson_instructors on class_session_lesson_times.id = class_session_lesson_instructors.class_session_lesson_time_id
        where class_session_lesson_instructors.instructor_id = ?
        order by class_session_lesson_times.lesson_time_epochms desc
        """, user.getPersonId()).include(ClassSessionLessonTime.class);
List<ClassSessionLessonTime> lts = classSessions.get(0).getAll(ClassSessionLessonTime.class);
List<ClassSessionLessonTime> lts2 = ClassSession.findBySessionSerialized(classSessions.get(0).getSessionSerialized()).getAll(ClassSessionLessonTime.class);

When this finishes, lts is empty, and lts2 has 18 elements (which is what I expected).

I think the presence of the joins in the findBySQL query interferes with the population of the cachedChildren field in Model.java, but I don't know how resolve this. I know I could make a Base.find() call and parse results by hand but this seems like a lot of work I'd like to avoid.

Answer 1

I have found a workaround:

List<ClassSession> classSessions = Base.findAll("""
    select class_sessions.*
    from class_sessions
             join class_session_lesson_times on class_sessions.id = class_session_lesson_times.class_session_id
             join class_session_lesson_instructors on class_session_lesson_times.id = class_session_lesson_instructors.class_session_lesson_time_id
    where class_session_lesson_instructors.instructor_id = ?
    order by class_session_lesson_times.lesson_time_epochms desc
    """, user.getPersonId())
.stream()
.map(row -> (ClassSession) new ClassSession().fromMap(row))
.toList();

The original issue seems like a gotcha but this solution works for me. Open to better solutions.