How to make recursive query in SQLite?

Question

if my data structure is like this

parentA
-------parentAA
--------------parentAAA
---------------------childA

if i can get "childA.name" . how can i know all the parent name till the top level. so it will be like this > parentA/parentAA/parentAAA/childA

what is the best way to do this ?

i'm working with SQLite and JAVA/android .. thanks in adv.

____________ EDIT

Okay guys, thanks for all of u . so i just make it by repeating "select query". BOTTOM-UP this is the method i create

public String getPath(int id, int type) {
        StringBuilder pathBuilder = new StringBuilder();
        String sql = null;
        int parentId = 0;

        if (id == 0) {
            pathBuilder.insert(0, "/root/");
            return pathBuilder.toString();
        }

        if (type == LayerManagementActivity.PARENT) {
            do {
                sql = "SELECT id, name, parent_id from parents_table where id="
                        + id;
                Cursor c = mDatabase.rawQuery(sql, null);
                if (c.moveToFirst()) {
                    parentId = c.getInt(2);
                    id = c.getInt(0);
                    pathBuilder.insert(0, "/" + c.getString(1));
                    c.close();
                }
                id = parentId;
            } while (parentId != 0);

            pathBuilder.insert(0, "/root");
            pathBuilder.append("/");

        } else if (type == LayerManagementActivity.CHILD) {
            sql = "SELECT id, name, folder_id FROM childs_table WHERE id=" + id;
            Cursor c = mDatabase.rawQuery(sql, null);
            if (c.moveToFirst()) {
                pathBuilder.append(c.getString(1));
                id = c.getInt(0);
                int folderId = c.getInt(2);
                String path = getPath(folderId, LayerManagementActivity.PARENT);
                pathBuilder.insert(0, path);
            }
            c.close();
        }
        Log.d("crumb", pathBuilder.toString());
        return pathBuilder.toString();
    }

Pls do not use recursive function its makes your application performance down — Dixit Patel, Jul 08 '13 at 05:08
SQLite does not support recursive queries directly (no CTE or CONNECT BY) - there will need to be as many selects (as joins or in multiple queries) per level in an approach that uses a *parent-child* relationship link. Other approaches which do not need recursion to answer this query include *materialized paths* and *nested sets* (search terms). — user2246674, Jul 08 '13 at 05:08
@DixitPatel Recursive functions are often fine (and SQLite has *very low latency*) - what really kills performance is failing to use indexes over large sets; or failing to use set joins and indexes for such: e.g. if *every* node was asked about *every* parent it had and some other SQL information needed to be gathered based on this - that could turn out very icky! But for a single query (at least as presented), not so bad. — user2246674, Jul 08 '13 at 05:13
okay, so if we can not use recursive function in sqlite, what's the alternatives ? — Khairil Ushan, Jul 08 '13 at 05:59

score 9 · Answer 1 · answered Mar 06 '14 at 12:25

9

In this SQLite Release 3.8.3 On 2014-02-03 has been added support for CTEs. Here is documentation WITH clause Example:

WITH RECURSIVE
cnt(x) AS (
 SELECT 1
 UNION ALL
 SELECT x+1 FROM cnt
  LIMIT 1000000
)
SELECT x FROM cnt;

answered Mar 06 '14 at 12:25

Roman Nazarevych

7,513
4
62
67

score 2 · Answer 2 · answered Aug 27 '14 at 19:47

I have a table called project with a column named rates. The rates column is a string that holds a JSON array. To split this string into rows that I can use in an IN statement to get rows from the related table, I use this for the IN part

WITH
 split(s, p) AS (
 SELECT substr(printf("%s%s", ss, ","), instr(ss, ",")+1), trim(substr(ss, 0, instr(ss, ","))) from ( select replace(replace(rates,"[",""), "]","") ss from project where rowid = 1)
 UNION ALL
 SELECT substr(s, instr(s, ",")+1), trim(substr(s, 0, instr(s, ","))) FROM split
 where p!=""
 )
 select p from split where p!=""

score 1 · Answer 3 · answered Jul 08 '13 at 08:29

1

You can use nested set model. Nested sets have big advantage that they can be implemented in most SQL engines using simple, non-recursive SQL queries.

answered Jul 08 '13 at 08:29

mvp

111,019
13
122
148

Interesting that I've never heard of this. A not to anyone looking at this that this model is read-optimized, and requires a near O(N) for writes. – heneryville Apr 17 '15 at 16:29
@heneryville: if you use [Nested Intervals](http://www.sigmod.org/publications/sigmod-record/0506/p47-article-tropashko.pdf), insertion performance issue is easily solvable. – mvp Apr 21 '15 at 00:34

score 0 · Accepted Answer · answered Jul 08 '13 at 05:31

0

SQLite doesn't support recursive CTEs (or CTEs at all for that matter),

there is no WITH in SQLite. Since you don't know how deep it goes, you can't use the standard JOIN trick to fake the recursive CTE. You have to do it the hard way and implement the recursion in your client code:

Grab the initial row and the sub-part IDs.
Grab the rows and sub-part IDs for the sub-parts.
Repeat until nothing comes back.

answered Jul 08 '13 at 05:31

Karan Mavadhiya

1,042
8
23

"Repeat until nothing comes back." how can we do that, is it by query ? or programmatically?? – Khairil Ushan Jul 08 '13 at 05:57
@RR12 If you go from the bottom up, you stop when the current entry does not have a parent. If you go from the top down, you stop when the current entry does not have children. – CL. Jul 08 '13 at 06:52
Generally For Recursive Nature you need to go through bottom up. – Karan Mavadhiya Jul 08 '13 at 07:10

How to make recursive query in SQLite?

4 Answers4

Linked