androidandroid-roomandroid-room-relationandroid-room-migrationandroid-room-embedded

Can I explicite use JOIN in DAO instead of using @Relationship

I'm using Room Database to build my Android app and want to gather some suggestion from community.

Let's say I have the Note Entity and the Label Entity. Both of them has the n - n relationship with the other. So when I want to return a List of Labels with its Notes or List of Notes with its Labels I have to write the custom query in DAO and define the data class to store the returned value.

@Entity(tableName = "notes")
data class Note(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "id")
    val id: Int = 0,
    @ColumnInfo(name = "title")
    val title: String,
    @ColumnInfo(name = "content")
    val content: String,
    @ColumnInfo(name = "created_at")
    val createAt: Long,
    @ColumnInfo(name = "updated_at")
    val updatedAt: Long
)

@Entity(
    tableName = "labels",
    indices = [androidx.room.Index(value = ["name"], unique = true)]
)
data class Label(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "id")
    val id: Int = 0,
    @ColumnInfo(name = "name")
    val name: String
)

@Entity(tableName = "note_labels",
    indices = [androidx.room.Index(value = ["note_id", "label_id"], unique = true)])
data class NoteLabel(
    @PrimaryKey(autoGenerate = true)
    val id: Int  = 0,
    @ColumnInfo(name = "note_id")
    val noteId: Int,
    @ColumnInfo(name = "label_id")
    val labelId: Int
)

My question is Should I use the annotation @Relation or manually write the JOIN query in DAO? I though both of them are using to return the Map of value and its related data.

So, Which one is better in term of documentation, clarity and maintainability?

I've tried this and it returned the correct value

    @Query("SELECT * FROM notes " +
            "JOIN note_labels ON notes.id = note_labels.note_id " +
            "JOIN labels ON note_labels.label_id = labels.id")
    suspend fun getAllNotesWithLabels(): Map<Note, List<Label>>

    @Query("SELECT * FROM labels JOIN note_labels ON labels.id = note_labels.label_id JOIN notes ON note_labels.note_id = notes.id")
    suspend fun getAllLabelsWithNotes(): Map<Label, List<Note>>

Should I switch to use @Relation or keeping this approach?

Solution

  • Should I switch to use @Relation or keeping this approach?

    You don't need to and there is no hard and fast rule (although Room only documents the use of @Relation). A third option could be to utilise a mix of both, which may suit.

    @Relation is a convenience way of mimicking a JOIN it has the advantage that it is simple for when it can be used.

    @Relation, instead of building SQL with JOIN(s) instead builds a function that returns the parent(s) and for each parent runs a subquery that retrieves ALL children of the relationship. Thus there are multiple executions so additionally the expectation that @Transaction is used for the @Query so the underlying function code is wrapped within a single transaction (and thus more complex underlying code).

    The main issue with @Relation is that ALL children are retrieved for a parent irrespective of query that does not return all children.

    e.g. if you had:-

    @Query("SELECT * FROM notes " +
        "JOIN note_labels ON notes.id = note_labels.note_id " +
        "JOIN labels ON note_labels.label_id = labels.id " +
        "WHERE labels.id > 10 /*<<<<<<<<<< ADDED*/")

    Then with @Relation labels with an id that is 10 or less, even though excluded by the primary query (as coded), would be included if the parent were still present in the output from the query (e.g. the parent Note had a relationship with a labels that were both 10 or less and greater than 10).

    Of course utilising @Relation requires a POJO (2 if you want Notes with related Labels and Labels with related Notes).

    Of course you could process the returned objects to determine which should be used.

    Another disadvantage of using @Relation with an intermediate (cross reference / associative / mapping table) is the complexity of the @Relation requiring the associateBy parameter and the Junction that defines the columns to be used from the intermediate table.

    If the intermediate/associative/mapping table has other columns than for the relationship/association/mapping then extracting that value requires more complexity.

    However, if not mapping (where I believe the parent has multiple children appended) then not using @Relation can be complex as the result of the JOIN(s) is the cartesian product and thus perhaps many rows per parent.

    Additional Considerations

    If you are looking for correctness then you should perhaps consider the following points.

    Point 1, include an index on the 2nd column of the intermediate/associative/mapping table so:-

    @ColumnInfo(name = "label_id", index = true)
val labelId: Int

    Point 2, use Long not Int for id columns as the generated value can be greater than what an Int can hold.

    Point 3, do not use @Autoincrement as this introduces the inefficiency of requiring/using an additional table sqlite_seqeunce which has to be accessed and maintained and thus increases the resource utilisation unnecessarily.

    So for example:-

    @Entity(tableName = "notes")
data class Note(
    @PrimaryKey(/*autoGenerate = true*/)
    @ColumnInfo(name = "id")
    val id: Long? = null, /* Long defaulting to null as without autoGenerate (= AUTOINCREMENT) 0 would be taken as 0 and thus UNIQUE conflict */
    @ColumnInfo(name = "title")
    val title: String,
    @ColumnInfo(name = "content")
    val content: String,
    @ColumnInfo(name = "created_at")
    val createAt: Long,
    @ColumnInfo(name = "updated_at")
    val updatedAt: Long
)

    DEMO

    Taking your code, applying the 3 points and:-

    1. adding the 2 POJO's with @Relation annotation, and
    2. adding functions to insert into the 3 tables, and
    3. adding functions to extract via the POJO's, and
    4. adding a basic @Database annotated abstract class, and
    5. adding some activity code to:-
      1. insert some Notes (20), labels (5) and relationships for all Notes (random amount of Labels per Note)
      2. extract via the 4 @Querys 2 as per you code and the extra 2 to extraxt via the @Relation annotated POJOs.
        1. Note the shorter SQL for the latter 2 (i.e. no JOINs needed)

    Then :-

    class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: AllDAOs
    val TAG = "DBINFO"
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        db = TheDatabase.getInstance(this)
        dao = db.getAllDAOs()

        for (i in 1..20) {
            dao.insert(Note(title = "N${i}", content = "blah for note ${i}", createAt = System.currentTimeMillis(), updatedAt = System.currentTimeMillis()))
        }
        for (i in 1..5) {
            dao.insert(Label(name = "LBL${i}"))
        }
        for (i in 1..20) {
            for (ii in 1..(Random.nextInt().absoluteValue.mod(4) + 1)) {
                dao.insert(NoteLabel(noteId = i, labelId = ii))
            }
        }

        for (nwl in dao.getAllNotesWithLabels()) {
            val sb = StringBuilder()
            for (l in nwl.value) {
                sb.append("\n\tID is ${l.id} Name is ${l.name}")
            }
            Log.d(TAG+"_STG1","Note ID is ${nwl.key.id} Title is ${nwl.key.title} it has ${nwl.value.size} labels. They are:-${sb}")
        }

        for (lwn in dao.getAllLabelsWithNotes()) {
            val sb = StringBuilder()
            for (n in lwn.value) {
                sb.append("\n\tID is ${n.id} Name is ${n.title} etc")
            }
            Log.d(TAG+"_STG2","Label ID is ${lwn.key.id} Name is ${lwn.key.name} it has ${lwn.value.size} notes. They are:-${sb}")
        }

        for (nwl in dao.getAllNotesWithLabelsViaAtRelation()) {
            val sb = StringBuilder()
            for (l in nwl.labels) {
                sb.append("\n\tID is ${l.id} Name is ${l.name}")
            }
            Log.d(TAG+"STG3","Note ID is ${nwl.note.id} Title is ${nwl.note.title} it has ${nwl.labels.size} labels. They are:-${sb}")
        }

        for (lwn in dao.getAllLabelsWithNotesViaAtRelation()) {
            val sb = StringBuilder()
            for (n in lwn.Notes) {
                sb.append("\n\tID is ${n.id} Title is ${n.title} etc")
            }
            Log.d(TAG+"STG4","Label ID is ${lwn.label.id} Name is ${lwn.label.name} it has ${lwn.Notes.size} notes. They are:-${sb}")
        }
    }
}

@Entity(tableName = "notes")
data class Note(
    @PrimaryKey(/*autoGenerate = true*/)
    @ColumnInfo(name = "id")
    val id: Long? = null, /* Long defaulting to null as without autoGenerate (= AUTOINCREMENT) 0 would be taken as 0 and thus UNIQUE conflict */
    @ColumnInfo(name = "title")
    val title: String,
    @ColumnInfo(name = "content")
    val content: String,
    @ColumnInfo(name = "created_at")
    val createAt: Long,
    @ColumnInfo(name = "updated_at")
    val updatedAt: Long
)

@Entity(
    tableName = "labels",
    indices = [androidx.room.Index(value = ["name"], unique = true)]
)
data class Label(
    @PrimaryKey(/*autoGenerate = true*/)
    @ColumnInfo(name = "id")
    val id: Long? = null,
    @ColumnInfo(name = "name")
    val name: String
)

@Entity(tableName = "note_labels",
    indices = [androidx.room.Index(value = ["note_id", "label_id"], unique = true)])
data class NoteLabel(
    @PrimaryKey(/*autoGenerate = true*/)
    val id: Long?  = null,
    @ColumnInfo(name = "note_id")
    val noteId: Int,
    @ColumnInfo(name = "label_id"/*, index = true*/)
    val labelId: Int
)

data class NoteWithItsLabels(
    @Embedded
    val note: Note,
    @Relation(
        entity = Label::class,
        entityColumn = "id",
        parentColumn = "id",
        associateBy = Junction(
            NoteLabel::class,
            parentColumn = "note_id",
            entityColumn = "label_id"
        )
    )
    val labels: List<Label>
)

data class LabelWithItsNotes(
    @Embedded
    val label: Label,
    @Relation(
        entity = Note::class,
        entityColumn = "id",
        parentColumn = "id",
        associateBy = Junction(
            NoteLabel::class,
            parentColumn = "label_id",
            entityColumn = "note_id"
        )
    )
    val Notes: List<Note>
)

@Database(entities = [Note::class,Label::class,NoteLabel::class], version = 1, exportSchema = false)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDAOs(): AllDAOs

    companion object {
        private var instance: TheDatabase?=null
        fun getInstance(context: Context): TheDatabase {
            if (instance==null) {
                instance=Room.databaseBuilder(context,TheDatabase::class.java,"the_database.db")
                    .allowMainThreadQueries()
                    .build()
            }
            return instance as TheDatabase
        }
    }
}

@Dao
interface AllDAOs {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(note: Note): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(label: Label): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(noteLabel: NoteLabel): Long

    @Query("SELECT * FROM notes " +
            "JOIN note_labels ON notes.id = note_labels.note_id " +
            "JOIN labels ON note_labels.label_id = labels.id")
    /*suspend*/ fun getAllNotesWithLabels(): Map<Note, List<Label>>

    @Query("SELECT * FROM labels JOIN note_labels ON labels.id = note_labels.label_id JOIN notes ON note_labels.note_id = notes.id")
    /*suspend*/ fun getAllLabelsWithNotes(): Map<Label, List<Note>>

    @Transaction
    @Query("SELECT * FROM notes")
    fun getAllNotesWithLabelsViaAtRelation(): List<NoteWithItsLabels>

    @Transaction
    @Query("SELECT * FROM labels")
    fun getAllLabelsWithNotesViaAtRelation(): List<LabelWithItsNotes>
}

    When Run a result, as output to the log, was :-

    2024-05-30 06:20:57.432 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFO_STG1: Note ID is 1 Title is N1 it has 3 labels. They are:-
        ID is 1 Name is LBL1
        ID is 2 Name is LBL2
        ID is 3 Name is LBL3
2024-05-30 06:20:57.432 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFO_STG1: Note ID is 2 Title is N2 it has 3 labels. They are:-
        ID is 1 Name is LBL1
        ID is 2 Name is LBL2
        ID is 3 Name is LBL3
2024-05-30 06:20:57.433 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFO_STG1: Note ID is 3 Title is N3 it has 2 labels. They are:-
        ID is 1 Name is LBL1
        ID is 2 Name is LBL2
2024-05-30 06:20:57.433 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFO_STG1: Note ID is 4 Title is N4 it has 3 labels. They are:-
        ID is 1 Name is LBL1
        ID is 2 Name is LBL2
        ID is 3 Name is LBL3
2024-05-30 06:20:57.433 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFO_STG1: Note ID is 5 Title is N5 it has 3 labels. They are:-
        ID is 1 Name is LBL1
        ID is 2 Name is LBL2
        ID is 3 Name is LBL3
2024-05-30 06:20:57.433 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFO_STG1: Note ID is 6 Title is N6 it has 2 labels. They are:-
        ID is 1 Name is LBL1
        ID is 2 Name is LBL2
2024-05-30 06:20:57.433 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFO_STG1: Note ID is 7 Title is N7 it has 1 labels. They are:-
        ID is 1 Name is LBL1
2024-05-30 06:20:57.433 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFO_STG1: Note ID is 8 Title is N8 it has 1 labels. They are:-
        ID is 1 Name is LBL1
2024-05-30 06:20:57.433 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFO_STG1: Note ID is 9 Title is N9 it has 2 labels. They are:-
        ID is 1 Name is LBL1
        ID is 2 Name is LBL2
2024-05-30 06:20:57.434 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFO_STG1: Note ID is 10 Title is N10 it has 3 labels. They are:-
        ID is 1 Name is LBL1
        ID is 2 Name is LBL2
        ID is 3 Name is LBL3
2024-05-30 06:20:57.434 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFO_STG1: Note ID is 11 Title is N11 it has 2 labels. They are:-
        ID is 1 Name is LBL1
        ID is 2 Name is LBL2
2024-05-30 06:20:57.434 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFO_STG1: Note ID is 12 Title is N12 it has 2 labels. They are:-
        ID is 1 Name is LBL1
        ID is 2 Name is LBL2
2024-05-30 06:20:57.434 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFO_STG1: Note ID is 13 Title is N13 it has 2 labels. They are:-
        ID is 1 Name is LBL1
        ID is 2 Name is LBL2
2024-05-30 06:20:57.434 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFO_STG1: Note ID is 14 Title is N14 it has 3 labels. They are:-
        ID is 1 Name is LBL1
        ID is 2 Name is LBL2
        ID is 3 Name is LBL3
2024-05-30 06:20:57.434 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFO_STG1: Note ID is 15 Title is N15 it has 1 labels. They are:-
        ID is 1 Name is LBL1
2024-05-30 06:20:57.434 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFO_STG1: Note ID is 16 Title is N16 it has 3 labels. They are:-
        ID is 1 Name is LBL1
        ID is 2 Name is LBL2
        ID is 3 Name is LBL3
2024-05-30 06:20:57.435 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFO_STG1: Note ID is 17 Title is N17 it has 4 labels. They are:-
        ID is 1 Name is LBL1
        ID is 2 Name is LBL2
        ID is 3 Name is LBL3
        ID is 4 Name is LBL4
2024-05-30 06:20:57.435 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFO_STG1: Note ID is 18 Title is N18 it has 3 labels. They are:-
        ID is 1 Name is LBL1
        ID is 2 Name is LBL2
        ID is 3 Name is LBL3
2024-05-30 06:20:57.435 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFO_STG1: Note ID is 19 Title is N19 it has 4 labels. They are:-
        ID is 1 Name is LBL1
        ID is 2 Name is LBL2
        ID is 3 Name is LBL3
        ID is 4 Name is LBL4
2024-05-30 06:20:57.435 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFO_STG1: Note ID is 20 Title is N20 it has 1 labels. They are:-
        ID is 1 Name is LBL1
2024-05-30 06:20:57.441 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFO_STG2: Label ID is 1 Name is LBL1 it has 20 notes. They are:-
        ID is 1 Name is N1 etc
        ID is 2 Name is N2 etc
        ID is 3 Name is N3 etc
        ID is 4 Name is N4 etc
        ID is 5 Name is N5 etc
        ID is 6 Name is N6 etc
        ID is 7 Name is N7 etc
        ID is 8 Name is N8 etc
        ID is 9 Name is N9 etc
        ID is 10 Name is N10 etc
        ID is 11 Name is N11 etc
        ID is 12 Name is N12 etc
        ID is 13 Name is N13 etc
        ID is 14 Name is N14 etc
        ID is 15 Name is N15 etc
        ID is 16 Name is N16 etc
        ID is 17 Name is N17 etc
        ID is 18 Name is N18 etc
        ID is 19 Name is N19 etc
        ID is 20 Name is N20 etc
2024-05-30 06:20:57.442 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFO_STG2: Label ID is 2 Name is LBL2 it has 16 notes. They are:-
        ID is 1 Name is N1 etc
        ID is 2 Name is N2 etc
        ID is 3 Name is N3 etc
        ID is 4 Name is N4 etc
        ID is 5 Name is N5 etc
        ID is 6 Name is N6 etc
        ID is 9 Name is N9 etc
        ID is 10 Name is N10 etc
        ID is 11 Name is N11 etc
        ID is 12 Name is N12 etc
        ID is 13 Name is N13 etc
        ID is 14 Name is N14 etc
        ID is 16 Name is N16 etc
        ID is 17 Name is N17 etc
        ID is 18 Name is N18 etc
        ID is 19 Name is N19 etc
2024-05-30 06:20:57.442 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFO_STG2: Label ID is 3 Name is LBL3 it has 10 notes. They are:-
        ID is 1 Name is N1 etc
        ID is 2 Name is N2 etc
        ID is 4 Name is N4 etc
        ID is 5 Name is N5 etc
        ID is 10 Name is N10 etc
        ID is 14 Name is N14 etc
        ID is 16 Name is N16 etc
        ID is 17 Name is N17 etc
        ID is 18 Name is N18 etc
        ID is 19 Name is N19 etc
2024-05-30 06:20:57.442 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFO_STG2: Label ID is 4 Name is LBL4 it has 2 notes. They are:-
        ID is 17 Name is N17 etc
        ID is 19 Name is N19 etc
2024-05-30 06:20:57.456 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFOSTG3: Note ID is 1 Title is N1 it has 3 labels. They are:-
        ID is 1 Name is LBL1
        ID is 2 Name is LBL2
        ID is 3 Name is LBL3
2024-05-30 06:20:57.457 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFOSTG3: Note ID is 2 Title is N2 it has 3 labels. They are:-
        ID is 1 Name is LBL1
        ID is 2 Name is LBL2
        ID is 3 Name is LBL3
2024-05-30 06:20:57.457 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFOSTG3: Note ID is 3 Title is N3 it has 2 labels. They are:-
        ID is 1 Name is LBL1
        ID is 2 Name is LBL2
2024-05-30 06:20:57.457 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFOSTG3: Note ID is 4 Title is N4 it has 3 labels. They are:-
        ID is 1 Name is LBL1
        ID is 2 Name is LBL2
        ID is 3 Name is LBL3
2024-05-30 06:20:57.457 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFOSTG3: Note ID is 5 Title is N5 it has 3 labels. They are:-
        ID is 1 Name is LBL1
        ID is 2 Name is LBL2
        ID is 3 Name is LBL3
2024-05-30 06:20:57.457 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFOSTG3: Note ID is 6 Title is N6 it has 2 labels. They are:-
        ID is 1 Name is LBL1
        ID is 2 Name is LBL2
2024-05-30 06:20:57.457 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFOSTG3: Note ID is 7 Title is N7 it has 1 labels. They are:-
        ID is 1 Name is LBL1
2024-05-30 06:20:57.458 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFOSTG3: Note ID is 8 Title is N8 it has 1 labels. They are:-
        ID is 1 Name is LBL1
2024-05-30 06:20:57.458 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFOSTG3: Note ID is 9 Title is N9 it has 2 labels. They are:-
        ID is 1 Name is LBL1
        ID is 2 Name is LBL2
2024-05-30 06:20:57.459 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFOSTG3: Note ID is 10 Title is N10 it has 3 labels. They are:-
        ID is 1 Name is LBL1
        ID is 2 Name is LBL2
        ID is 3 Name is LBL3
2024-05-30 06:20:57.459 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFOSTG3: Note ID is 11 Title is N11 it has 2 labels. They are:-
        ID is 1 Name is LBL1
        ID is 2 Name is LBL2
2024-05-30 06:20:57.459 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFOSTG3: Note ID is 12 Title is N12 it has 2 labels. They are:-
        ID is 1 Name is LBL1
        ID is 2 Name is LBL2
2024-05-30 06:20:57.459 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFOSTG3: Note ID is 13 Title is N13 it has 2 labels. They are:-
        ID is 1 Name is LBL1
        ID is 2 Name is LBL2
2024-05-30 06:20:57.460 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFOSTG3: Note ID is 14 Title is N14 it has 3 labels. They are:-
        ID is 1 Name is LBL1
        ID is 2 Name is LBL2
        ID is 3 Name is LBL3
2024-05-30 06:20:57.460 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFOSTG3: Note ID is 15 Title is N15 it has 1 labels. They are:-
        ID is 1 Name is LBL1
2024-05-30 06:20:57.460 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFOSTG3: Note ID is 16 Title is N16 it has 3 labels. They are:-
        ID is 1 Name is LBL1
        ID is 2 Name is LBL2
        ID is 3 Name is LBL3
2024-05-30 06:20:57.460 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFOSTG3: Note ID is 17 Title is N17 it has 4 labels. They are:-
        ID is 1 Name is LBL1
        ID is 2 Name is LBL2
        ID is 3 Name is LBL3
        ID is 4 Name is LBL4
2024-05-30 06:20:57.461 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFOSTG3: Note ID is 18 Title is N18 it has 3 labels. They are:-
        ID is 1 Name is LBL1
        ID is 2 Name is LBL2
        ID is 3 Name is LBL3
2024-05-30 06:20:57.461 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFOSTG3: Note ID is 19 Title is N19 it has 4 labels. They are:-
        ID is 1 Name is LBL1
        ID is 2 Name is LBL2
        ID is 3 Name is LBL3
        ID is 4 Name is LBL4
2024-05-30 06:20:57.462 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFOSTG3: Note ID is 20 Title is N20 it has 1 labels. They are:-
        ID is 1 Name is LBL1
2024-05-30 06:20:57.473 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFOSTG4: Label ID is 1 Name is LBL1 it has 20 notes. They are:-
        ID is 1 Title is N1 etc
        ID is 2 Title is N2 etc
        ID is 3 Title is N3 etc
        ID is 4 Title is N4 etc
        ID is 5 Title is N5 etc
        ID is 6 Title is N6 etc
        ID is 7 Title is N7 etc
        ID is 8 Title is N8 etc
        ID is 9 Title is N9 etc
        ID is 10 Title is N10 etc
        ID is 11 Title is N11 etc
        ID is 12 Title is N12 etc
        ID is 13 Title is N13 etc
        ID is 14 Title is N14 etc
        ID is 15 Title is N15 etc
        ID is 16 Title is N16 etc
        ID is 17 Title is N17 etc
        ID is 18 Title is N18 etc
        ID is 19 Title is N19 etc
        ID is 20 Title is N20 etc
2024-05-30 06:20:57.473 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFOSTG4: Label ID is 2 Name is LBL2 it has 16 notes. They are:-
        ID is 1 Title is N1 etc
        ID is 2 Title is N2 etc
        ID is 3 Title is N3 etc
        ID is 4 Title is N4 etc
        ID is 5 Title is N5 etc
        ID is 6 Title is N6 etc
        ID is 9 Title is N9 etc
        ID is 10 Title is N10 etc
        ID is 11 Title is N11 etc
        ID is 12 Title is N12 etc
        ID is 13 Title is N13 etc
        ID is 14 Title is N14 etc
        ID is 16 Title is N16 etc
        ID is 17 Title is N17 etc
        ID is 18 Title is N18 etc
        ID is 19 Title is N19 etc
2024-05-30 06:20:57.473 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFOSTG4: Label ID is 3 Name is LBL3 it has 10 notes. They are:-
        ID is 1 Title is N1 etc
        ID is 2 Title is N2 etc
        ID is 4 Title is N4 etc
        ID is 5 Title is N5 etc
        ID is 10 Title is N10 etc
        ID is 14 Title is N14 etc
        ID is 16 Title is N16 etc
        ID is 17 Title is N17 etc
        ID is 18 Title is N18 etc
        ID is 19 Title is N19 etc
2024-05-30 06:20:57.473 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFOSTG4: Label ID is 4 Name is LBL4 it has 2 notes. They are:-
        ID is 17 Title is N17 etc
        ID is 19 Title is N19 etc
2024-05-30 06:20:57.473 11084-11084/a.a.so78551137kotlimroomrelationornot D/DBINFOSTG4: Label ID is 5 Name is LBL5 it has 0 notes. They are:-

    NOTE the subtle difference between the approaches is that the @Relation approach will return a parent when there are no children, with the list of children empty (i.e. Label 5 has no related Notes). Of course, if you utilised the same query with the JOINs then the parent Label (5) that has no related notes would not be part of the output and thus would not be included and would thus produce the same result (i.e. no Label 5 with 0 related Notes).