Recursive query with ordered values in SQLite Android

Question

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.

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!

Caumons · Accepted Answer · 2012-03-07 09:54:07Z

2

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

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;
}

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!

Hope it helps!

answered Mar 7, 2012 at 9:54

Caumons

9,64514 gold badges71 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

cesar Over a year ago

Just a heads-up, though I might have misinterpreted what you meant by recursive queries, I think you're wrong about SQLite not supporting recursive selects. I just tried something similar to SELECT * FROM (SELECT column FROM table) through SQLiteDatabase.rawQuery() and it worked for me. I'm sorry if this came out offensive or harsh, but I can't think of a way to make myself sound like less of a know-it-all.

cesar Over a year ago

I'm actually trying to find out what you meant by it. Did you mean that Android doesn't have methods that allow recursive queries, and that you'll have to use raw SQL code? Or did you mean something else? Again, I'm really sorry if I'm coming off as a know-it-all, but I can't think of a way to rephrase this. :P

Jonas Kalderstam Over a year ago

Sqlite does not support recursive queries. The example query you show cesar is not recursive, it is merely a query embedded inside another query, which is fully supported. A recursive query would call itself. Google "Sql CTE" for an example

Caumons Over a year ago

@Cesar As you may know, a recursive association i one that points to the origin class (self), so if you want to perform a query using the foreign key that points to the same table you are likely to do a recursive query. Your example, as stated by SciencyGuy is just a nested query, which is very common, does not mean any recursion at all. What I mean with my question & answer is that SQLite does not support recursive queries. And do not worry, we are here to help each other! :)

cesar Over a year ago

Oh okay, I guess I don't know what a recursive query is. My bad. Thanks for enlightening me.

Collectives™ on Stack Overflow

Recursive query with ordered values in SQLite Android

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related