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
– Dixit Patel, Commented Jul 8, 2013 at 5: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
– user2246674, Commented Jul 8, 2013 at 5: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
– user2246674, Commented Jul 8, 2013 at 5:13
okay, so if we can not use recursive function in sqlite, what's the alternatives ? — Khairil Ushan
– Khairil Ushan, Commented Jul 8, 2013 at 5:59

Roman Nazarevych · Accepted Answer · 2014-03-06 12:25:37Z

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 6, 2014 at 12:25

Roman Nazarevych

7,7335 gold badges64 silver badges70 bronze badges

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

Comments

codeartist · Accepted Answer · 2014-08-27 19:47:40Z

2

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!=""

answered Aug 27, 2014 at 19:47

codeartist

211 bronze badge

Comments

mvp · Accepted Answer · 2013-07-08 08:29:41Z

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 8, 2013 at 8:29

mvp

118k15 gold badges132 silver badges155 bronze badges

2 Comments

heneryville Over a year ago

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.

mvp Over a year ago

@heneryville: if you use Nested Intervals, insertion performance issue is easily solvable.

Karan Mavadhiya · Accepted Answer · 2013-07-08 05:31:54Z

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 8, 2013 at 5:31

Karan Mavadhiya

1,0628 silver badges24 bronze badges

3 Comments

Khairil Ushan Over a year ago

"Repeat until nothing comes back." how can we do that, is it by query ? or programmatically??

CL. Over a year ago

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

Karan Mavadhiya Over a year ago

Generally For Recursive Nature you need to go through bottom up.

Collectives™ on Stack Overflow

How to make recursive query in SQLite?

4 Answers 4

Comments

Comments

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related