Kotlin 从带有 Room 和 Flows 的 SQLite 表中获取祖先树

如果我理解正确的话.

所以5得4，4得3....直到1没有父母.那么我相信以下几点会起作用:-

@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>>

• 注:以上内容未在项目中编码，因此未经测试，因此可能包含一些错误.
• 这样做的好处是，所有这些都是在单个查询/数据库访问中完成的(可能您的问题之一是，您正在递归地返回流)

作为从SQLiteAngular (即忽略房间)进行的上述示例，请考虑以下带有注释的演示:-

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 */

结果(3项输出)如下:-

• 包含数据的项目表

• 第5项及其前身

• 项目3及其前身

关于CTE以及WITH子句和递归的更深入解释，请参阅https://sqlite.org/lang_with.html.