在SQLite Android中使用有序值进行递归查询 [英] Recursive query with ordered values in SQLite Android

问题描述

我有一个具有递归关系的 group 表，因此每条记录都有一个 parent_id .给定一个小组，我需要获取其所有子小组中的所有 student (每个人属于一个小组)名称，但按学生姓名排序.

I have one group table with a recursive relation, so each record has a parent_id. Given a group, I need to get all the student (each belong to a group) names in all its subgroups, but ordered by student name.

您知道是否有任何简便"的方法?如果必须执行多个查询，则应该对不同游标的结果进行排序，但游标没有orderBy().

Do you know if there is any "easy" way to do it? If I have to do multiple queries, then I should order the results of the different Cursors, but Cursor has no orderBy().

有什么想法吗?非常感谢！

Any ideas? Thank you so much!

推荐答案

由于SQLite不支持递归查询，因此我通过两个步骤实现了select:

As SQLite does not support recursive queries I implemented the select with two steps:

首先，我有一个名为 getRecursiveDiningGroupIdsAsString()的方法，该方法递归检索所有组ID，其父ID是您通过参数传递的组ID.结果是一个字符串，形式为:(2，3，4)"，因此您以后可以在 IN 子句中使用它.该方法如下:

First, I have a method called getRecursiveDiningGroupIdsAsString() that retreives all the group ids recursively whose parent id is the one you pass by parameter. The result is a String in the form of: "(2, 3, 4)" so you can later use it in an IN clause. The method looks like:

public String getRecursiveDiningGroupIdsAsString(int depth, long diningGroupId) {
    Cursor childDiningGroups = mDatabase.query(
            "group",
            new String[] {"_id"},
            "parent_id = "+diningGroupId,
            null, null, null, null
    );
    String recursiveDiningGroupIds = "";
    while (childDiningGroups.moveToNext()) {
        long childDiningGroupId = childDiningGroups.getLong(childDiningGroups.getColumnIndex("_id"));
        recursiveDiningGroupIds += getRecursiveDiningGroupIdsAsString(depth+1, childDiningGroupId);
    }
    recursiveDiningGroupIds += diningGroupId;
    if (depth > 0) {
        recursiveDiningGroupIds += ", ";
    } else {
        recursiveDiningGroupIds = "("+recursiveDiningGroupIds+")";
    }
    return recursiveDiningGroupIds;
}

一旦有了所需的组ID，我就可以使用上一种方法返回的ID进行简单的查询，就是这样！

Once I have the group ids I need, I just do a simple query using the ids returned by the previous method and that is it!

希望有帮助！

这篇关于在SQLite Android中使用有序值进行递归查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！