How to get only distinct values from an @Embedded relationship using asscociateBy table?

Is it possible to generate a list of only the distinct objects (from the child entity) using @Embedded and asscociatedBy = Junction() table?

I have several separated entities (EntityActivityLog, EntityComponent, EntityGroup) associated by the linking table (EntityLinkGroupToActivity).

Essentially each ActivityLog has multiple components, which in turn have multiple groups. (it goes on but for the purposes of this question I'll stop there)

At the moment Im working with the simplified data set as follows:

EntityActivityLog (activityLogId, activityName) contains 2x unique activities
EntityComponent (componentId, componentName) contains 5x unique components.
EntityLinkGroupToActivity (activityId, componentId, groupId) contains 9x rows of data. The first activityLog has 3 unique components, and the second activityLog has 2 components.

[activityLog1, component1,group1]

[activityLog1, component1,group2]

[activityLog1, component1,group3]

[activityLog1, component2,group1]

[activityLog1, component4,group1]

[activityLog2, component1,group1] etc...

Using the following code I expected a pojo containing a single embedded activity object and a list of unique component entities. Each componentId would be associated with the activityLogId in the linking table.

data class ActivityLogWithComponentList (
    @Embedded
    val activityLog: EntityLogActivity,
    @Relation(
        parentColumn = "activityLogId",
        entity = EntityComponent::class,
        entityColumn = "componentId ",
        associateBy = Junction(
            EntityLinkGroupToActivity::class,
            parentColumn = "activityLogId",
            entityColumn = "componentId ",
        )
    )
    val componentList: List<EntityComponent>
){}

However, in the result for the first activityLog there are 9x components.

POJO List Results:

component1, component1, component1, component2, component4...

I can understand the logic with the 'junction' of the linking table containing all the componentIds as does the component entity table. Thus each row of the junction is always fulfilled. However this is not the outcome Im looking for and haven’t seen an alternative to associateBy = junction().

Ideally I am aiming for only the distinct component items in the list: component1, component2, component4

Any suggestions would be appreciated on how to achieve this using this embedded approach.

Additional Notes:

I dont believe I can break up the linking entity without introducing unwanted complexity.
I have tried including 'Distinct' in the SQL query, but as the query only references the entity tables which are already distinct this has had no effect on the list result.

Solution

Coding DISTINCT will have no effect as for an @Relation what room does is build run it's own sub query to get ALL children of the parent.

However, with a few extra Dao's this can be achieved:-

First 2 queries (you may have the first already):-

@Query("SELECT * FROM entityactivitylog")
    abstract fun getAllActivityLogs(): List<EntityActivityLog>
    @Query("SELECT DISTINCT entitycomponent.* " + /*<<<<< ONLY NEED TO GET COMPONENT columns */
            "FROM entitylinkgrouptoactivity " +
            "JOIN entitycomponent ON entitycomponent.componentId = entitylinkgrouptoactivity.componentId " +
            "WHERE activityLogId=:activityLogId")
    abstract fun getDistinctComponentsForAnActivity(activityLogId: Long): List<EntityComponent>

The 2nd, as can be seen will get distinct components via the link table (an alternative to DISTINCT would be a GROUP BY clause, which has the potential to be more useful)

Then a function (note that an abstract class has been used for the Dao's as then a function with a body can be utilised). This function masquerades as a query to allow the @Transaction annotation, it is:-

@Query("")
@Transaction
fun getDistinct(): List<ActivityLogWithComponentList> {
    val rv: ArrayList<ActivityLogWithComponentList> = ArrayList()
    val currentActivityLog: EntityActivityLog
    for (a in getAllActivityLogs()) {
        rv.add(
            ActivityLogWithComponentList(
                activityLog = a,
                componentList = getDistinctComponentsForAnActivity(a.activityLogId!!)
            )
        )
    }
    return rv
}

Using the above along with (run on the main thread for convenience and brevity):-

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

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

        val g1ID = dao.insert(EntityGroup(groupName = "Group1"))
        val g2ID = dao.insert(EntityGroup(groupName = "Group2"))
        val g3ID = dao.insert(EntityGroup(groupName = "Group3"))
        val c1ID = dao.insert(EntityComponent(componentName = "Component1"))
        val c2ID = dao.insert(EntityComponent(componentName = "Component2"))
        val c3ID = dao.insert(EntityComponent(componentName = "Component3"))
        val c4ID = dao.insert(EntityComponent(componentName = "Component4"))
        val a1ID = dao.insert(EntityActivityLog(activityName = "Activity1"))
        val a2ID = dao.insert(EntityActivityLog(activityName = "Activity2"))

        dao.insert(EntityLinkGroupToActivity(activityLogId = a1ID, componentId = c1ID, groupId =  g1ID))
        dao.insert(EntityLinkGroupToActivity(activityLogId = a1ID, componentId = c1ID, groupId = g2ID))
        dao.insert(EntityLinkGroupToActivity(activityLogId = a1ID, componentId = c1ID, groupId = g3ID))
        dao.insert(EntityLinkGroupToActivity(activityLogId = a1ID, componentId = c2ID, groupId = g1ID))
        dao.insert(EntityLinkGroupToActivity(activityLogId = a1ID, componentId = c4ID, groupId = g1ID))
        dao.insert(EntityLinkGroupToActivity(activityLogId = a2ID, componentId = c1ID, groupId = g1ID))

        for(al: ActivityLogWithComponentList in dao.getOriginal()) {
            Log.d(TAG+"RUN1","Activity Log is ${al.activityLog.activityName} it has ${al.componentList.size} components")
            for (c: EntityComponent in al.componentList) {
                Log.d(TAG,"\tComponent is ${c.componentName}")
            }
        }

        for (al: ActivityLogWithComponentList in dao.getDistinct()) {
            Log.d(TAG+"RUN2","Activity Log is ${al.activityLog.activityName} it has ${al.componentList.size} components")
            for (c: EntityComponent in al.componentList) {
                Log.d(TAG,"\tComponent is ${c.componentName}")
            }
        }
    }
}

The results in the log are:-

Original

2021-12-13 20:13:43.949 D/DBINFORUN1: Activity Log is Activity1 it has 5 components
2021-12-13 20:13:43.949 D/DBINFO:   Component is Component1
2021-12-13 20:13:43.949 I/chatty: uid=10145(a.a.so70330069kotlinroomrelationships) identical 1 line
2021-12-13 20:13:43.949 D/DBINFO:   Component is Component1
2021-12-13 20:13:43.949 D/DBINFO:   Component is Component2
2021-12-13 20:13:43.949 D/DBINFO:   Component is Component4
2021-12-13 20:13:43.949 D/DBINFORUN1: Activity Log is Activity2 it has 1 components
2021-12-13 20:13:43.949 D/DBINFO:   Component is Component1

Distinct/Unique

2021-12-13 20:13:43.961 D/DBINFORUN2: Activity Log is Activity1 it has 3 components
2021-12-13 20:13:43.961 D/DBINFO:   Component is Component1
2021-12-13 20:13:43.961 D/DBINFO:   Component is Component2
2021-12-13 20:13:43.961 D/DBINFO:   Component is Component4
2021-12-13 20:13:43.961 D/DBINFORUN2: Activity Log is Activity2 it has 1 components
2021-12-13 20:13:43.961 D/DBINFO:   Component is Component1