Skip to content
Advertisement

How can I filter composite ManyToMany POJOs by children POJO’s attributes?

I have two Room Entities like this ones:

…and a junction table for Many To Many relationship like this one:

…and some composite class for obtaining some kind of “composite POJO”:

…then, I have this kind of “composite DAO”:

The point of the question is…

The one that works returns the list as expected: with each TeacherWithCourses having the Teacher and the List of Courses. The second one does not: the resulting TeacherWithCourses objects have it’s Teacher properties loaded correctly, but the List<Courses> property has an empty list, although the complex SELECT query based on INNER JOINS filters as expected.

So, how can I obtain a list of complete TeacherWithCourses objects like in the first DAO method, but filtering by Courses ID instead?

Advertisement

Answer

I believe that your issue is due to the column names being duplicated and basically room selecting the incorrect values (I believe it uses the last so it would be using the course id column value for the teacher id).

That is the query (with the JOINS) will consist of columns:-

  • id (Teacher),
  • name (Teacher),
  • teacher_id,
  • course_id,
  • id (Course),
  • name (Course)

So let’s say you have the following in the database :-

enter image description here

enter image description here

enter image description here

And the following is used (LiveData not used to brevity and convenience) :-

Then the result, is as you report :-

However (Fix)

If you use different column names e.g. :-

  • and data is added that basically duplicates the original courses (same id #’s) so :-

  • enter image description here

Along with :-

  • noting that the teachercourses table is used just that the alternative Courses are linked (rather than create an altteachercourses table)

and :-

and then :-

i.e. instead of Course, AltCourse is used in an otherwise an identical, then the result is :-

As such the solution is to either

  1. use unique column names, or
  2. use the @Prefix annotation (parameter of the @Embedded) e.g. you could have

:-

and use :-

BUT you would then also need to use :-

Additional re comment :-

the only issue is that “ORDER BY” statement didn’t seem to affect this “child list” ‘s sorting. But that one might be subject for a new question.

The issue is due to how @Relationship works.

@Relationship works by getting ALL the @Relation objects of the parent(s) via underlying queries. Anything in the @Query that doesn’t affect the parents that are retrieved is not considered when retrieving the children. Hence, you have no control over the ORDER.

Perhaps consider a CourseWithTeachers approach, but then you have no control over the ORDER of the Teachers. The alternative is to use @Embedded for the parent and the child but you then have to process the result which is the caretesian product i.e. a result for each parent/child combination.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement