Get tree of ancestors from a SQLite table with Room and Flows

Question

This is very tricky for me because I don't know if it can be done with a SQLite query or by recursively calling some function. It gets complicated because I also use Flow and LiveData with ViewModel

Basically, my entity looks like this

@Entity(
    foreignKeys = [
        ForeignKey(
            entity = Item::class,
            parentColumns = [ "id" ],
            childColumns = [ "parentId" ],
            onDelete = ForeignKey.CASCADE
        ),
    ],
    indices = [
        Index(value = [ "parentId" ]),
        Index(value = [ "sectionId" ])
    ],
)
class Item(
    @PrimaryKey(autoGenerate = true)
    val id: Long = 0,
    var parentId: Long? = null,
    val name: String
)

Now, imagine I have 5 items, each one parent of the next one

Item (id = 1, parentId = null, name = "Root item")
Item (id = 2, parentId = 1, name = "Item 2")
Item (id = 3, parentId = 2, name = "Item 3")
Item (id = 4, parentId = 3, name = "Item 4")
Item (id = 5, parentId = 4, name = "Item 5")

So, what I want is to get Item 5 and all its ancestors, that is Item 4 because it is its parent, Item 3 because it's the parent of Item 4 and so on to the first one (which has no parent, therefore it is where we stop)

At the moment I have this mess in my DAO and I am kinda stuck. How do you think this can be achieved?

@Dao
interface ItemDao {
    @Query("SELECT * FROM Item WHERE itemId = :itemId")
    fun getById(itemId: Long): Flow<Item>

    fun getAncestors(itemId: Long): Flow<List<Item>> = flow {
        val item = getById(itemId)

        item.collect { it ->
            if (it.parentId != null) {
                val parent = getAncestors(it.parentId!!)
                val items = combine(item, parent ) { i, p ->
                    val allItems: ArrayList<Item> = arrayListOf()
                    allItems.add(i)
                    allItems.add(p)

                    allItems
                }

                emitAll(items)
            } else {
                emitAll(item)
            }
        }
    }
}

This does not work (I don't get everything) and it's most likely because of the wrong use of flows. I need an alternative or a little help to understand this and get unstuck

Answer 1

If I understand correctly (I think you want predecessors(parents) of 5, rather than ancestors (children)).

So 5 will get 4, 4 will get 3 .... until 1 which has no parent. Then I believe the following will work:-

@Query("WITH cte1(id,parentId,name) AS (SELECT * FROM item WHERE id=:itemId UNION ALL SELECT parentId,(SELECT parentId FROM item WHERE item.id = cte1.parentId),(SELECT name FROM item WHERE item.id = cte1.parentId) FROM cte1 WHERE parentId IS NOT NULL  LIMIT 10 /*<<<<< just in case limit to 10 iterations */) SELECT * FROM cte1;")
fun getPredecessorsOfId(itemId): Flow<List<Item>>

• Note the above has not been coded within a project and is therefore untested, as such it may contain some errors.
• This has the advantage that it is all done within a single query/ database access (perhaps one of your issues is that you are recursively returning Flows)

As an example of the above from an SQLite perspective (i.e. ignoring Room) then consider the following demo with comments :-

DROP TABLE IF EXISTS item;
/* Create the demo table */
CREATE TABLE IF NOT EXISTS item(id INTEGER PRIMARY KEY, parentId INTEGER, name TEXT);
INSERT INTO item VALUES (1,null,'Root Item'),(2,1,'item2'),(3,2,'item3'),(4,3, 'item4'),(5,4,'item5');

SELECT * FROM item; /* Output 1 the item table in full */


/* Uses a Common Table Expression which is recursive due to the UNION with itself */
/* and will loop forever unless ended.*/
/* Common Table Expression can be considered as a temporary table that exists only for the duration of the query */
/* In this case the end is when the parentId is null due to WHERE parentId IS NOT NULL */
/* However, as a precaution, LIMIT 10 will limit to 10 iterations */
/* Output 2 example 1 */
WITH cte1(id,parentId,name) AS (
    SELECT * FROM item WHERE id = 5 
    UNION ALL SELECT 
        parentId, /* the id is the parentId of the previous row */
        (SELECT parentId FROM item WHERE item.id = cte1.parentId), /* parentId is obtained from the item table using the parentId of the previous row */
        (SELECT name FROM item WHERE item.id = cte1.parentId) /* likewise for the name */
        FROM cte1 
        WHERE parentId IS NOT NULL  
        LIMIT 10 /*<<<<< just in case limit to 10 iterations */
    )
SELECT * FROM cte1;

/* Output 3 using an id mid-list */
WITH cte1(id,parentId,name) AS (
    SELECT * FROM item WHERE id = 3 
    UNION ALL SELECT 
        parentId,
        (SELECT parentId FROM item WHERE item.id = cte1.parentId),
        (SELECT name FROM item WHERE item.id = cte1.parentId) 
        FROM cte1 
        WHERE parentId IS NOT NULL  
        LIMIT 10
    )
SELECT * FROM cte1;

DROP TABLE IF EXISTS item; /* Cleanup demo environment */

The results (3 outputs) are :-

• The Item table with your data

• Item 5 and it's predecessors

• Item 3 and it's predecessors

You may wish to refer to https://sqlite.org/lang_with.html for a more in-depth explanation of CTE's and the WITH clause and recursion.