0

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.

Malcolm Crum
  • 4,345
  • 4
  • 30
  • 49

1 Answers1

0

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.

Malcolm Crum
  • 4,345
  • 4
  • 30
  • 49
  • it is unlikely that the join wold have an effect on cached children. In any case, your solution can be wrapped inside an accessor method in the class ClassSession for cleaner code. If you create a simple project on Github that reproduces this issue, I can tinker and find the exact reason. In any case, there are many ways to kill a cat with JavaLite :) – ipolevoy Jul 17 '23 at 21:48
  • You're right - when I tried to build a repro I realised a crucial mistake I made in my code. In my original code where I had this problem, I had `select *` but in my code above I have `select class_sessions.*`. The latter works fine. Sorry to waste your time! – Malcolm Crum Jul 18 '23 at 01:03
  • Glad it worked out for you! – ipolevoy Jul 18 '23 at 05:43