query parents in a tree

Question

I have database table Task as below.

SELECT _id,name,parentId FROM Task;

_id         name                  parentId  
----------  --------------------  ----------
4           Software Development            
5           Machine Learning                
6           Programing            4         
7           Build System          4         
8           version control       4         
9           Android App Developm  4         
10          Udacity Cource        5         
11          Mathematics           5         
12          skLearn docs          5         
13          problem solving       6         
14          breakdown             13        
15          language              6         
16          c                     15        
17          c++                   15        
18          java                  15        
19          kotlin                15        
20          gradle                7         
21          bazel                 7         
22          git                   8         
23          svn                   8

There are all tasks and their sub tasks in one table relating with respective parent task using _id(primary key) and parentId.

e.g. task name 'java' has _id = 18 and parentId = 15 means 'java' is sub task of _id = 15 which is 'language'.

Again 'language' has _id = 15 and parentId = 6 means 'language' is sub task of _id = 6 which is 'Programing'.

Same 'Programing' is sub task of 'Software development'.

And 'Software development' is sub task of null.

so I require one query which give output like below for input _id = 18(i.e. 'java') that is list of parent,parent of parent tasks... to the top for a sub task.

_id      name              parentId
4    Software Development   null
6    Programing             4
15   language               6
18   java                  15

Currently I am able to take this output using 4 query in a loop.

SELECT _id,name,parentId FROM task WHERE _id = 18

in next iteration _id would be value of parentId from output of above query

which is time consuming so can we have better solution for this.

The answer is a CTE (common table expression). Check out the WITH statement. https://sqlite.org/lang_with.html THis is a common way of querying tree structures in SQL (which is basically what you have) — Gabe Sechan, Oct 08 '17 at 07:18
https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL — Phantômaxx, Oct 08 '17 at 08:46

CL. · Accepted Answer · 2017-10-09T06:36:21.677

1

To go up in a tree requires a recursive common table expression:

WITH RECURSIVE parents(id, name, parentid, level) AS (
  SELECT _id, name, parentid, 1
  FROM Task
  WHERE _id = 18

  UNION ALL

  SELECT Task._id, Task.name, Task.parentid, level + 1
  FROM Task
  JOIN parents ON Task._id = parents.parentid
)
SELECT id, name, parentid
FROM parents
ORDER BY level DESC;

This is not supported before Android Lollipop (API level 21).

edited Oct 09 '17 at 06:36

answered Oct 08 '17 at 13:19

CL.

173,858
17
217
259

Thanks for you answer! I am getting output with above query is 18 | java | 15 instead of desired output. your approach seems write but still something is missing! – Kevan Oct 09 '17 at 04:59
'WITH' support from sqlite 3.8.3 or later – Huỳnh Ngọc Bang Oct 09 '17 at 08:03

score 0 · Answer 2 · answered Oct 09 '17 at 07:59

I also have business like that, and I combine sql with java code to resolve this problem. Just something like that:

    public ArrayList<String> getRecursiveReverse(String parentId) throws Exception {
    StringBuffer  sqlObject = new StringBuffer();
    sqlObject.append("SELECT T.TABLE_ID ");
    sqlObject.append("FROM   TABLE_NAME T ");
    sqlObject.append("WHERE  1 = 1 ");
    sqlObject.append("       AND T.STATUS = 1 ");
    sqlObject.append("       AND T.PARENT_ID = ? ");

    Cursor c = null;
    String[] params = { parentId };
    ArrayList<String> listIdArray = new ArrayList<String>();
    if (!StringUtil.isNullOrEmpty(parentId)) { 
            listIdArray.add(parentId);
    }
    try {
        c = rawQuery(sqlObject.toString(), params);
        if (c != null) {
            if (c.moveToFirst()) {
                do {
                    String tableId = CursorUtil.getString(c, "TABLE_ID");

                    ArrayList<String> tempArray = getShopRecursiveReverse(tableId);
                    listIdArray.addAll(tempArray);
                } while (c.moveToNext());
            }
        }
    } finally {
        try {
            if (c != null) {
                c.close();
            }
        } catch (Exception e) {
            MyLog.w(getTAG(), GlobalUtil.getCurrentMethodName(), e);
        }
    }
    return listIdArray;
}

And from sqlite version 3.8.3 support WITH clause. Maybe you can using with 'WITH clause' — Huỳnh Ngọc Bang, Oct 09 '17 at 08:02

query parents in a tree

2 Answers2