CODEWITHSUNDEEP
androidmysqlsqldatabasesqlite
Lalit Poptani
Lalit Poptani

Reputation: 67296

Fetch data from database in a tree structure in sqlite

In my application I have a tree structured table and I want to know that if I can fetch the tree structure data from sqlite database by simply applying a single query. Currently I am lopping it manually using a for loop and able to meet my requirement but I am not statisfied with that approach as its more complex, may be time consuming, etc.

My table table structure is as below,

parent_id |  cat_id

  null    |   1
   1      |   2
   1      |   3
   1      |   4
   4      |   5
   4      |   6

So, now above is my table structure and I want to fetch the tree structure like, If I pass parent_id as 1 in my query then it should return 2,3,4,5,6 as 1 is parent of 1, 2, 3, 4 and 4 is parent of 5,6 and also 4 is child of 1

So, input and output expected by me,

input    output

  1     2,3,4,5,6
  4     5, 6

So, it there a simple way to acheive the above tree structure by using sqlite query?

OR

There is no way to acheive this using sqlite query and I need to follow the current method or way that I am using it by manually looping all the category ids.

Upvotes: 5

Views: 2461

Answers (2)

Lalit Poptani
Lalit Poptani

Reputation: 67296

I didn't find any solution from sqlite itself, so created an Algorithm that creates Tree structure by iterating over each id & over its sub_ids and write into database.

I had gone through Closure Tables which gives the expected result that I was looking as a solution

Some more references of Closure Table -

http://technobytz.com/closure_table_store_hierarchical_data.html http://karwin.blogspot.in/2010/03/rendering-trees-with-closure-tables.html

Upvotes: 1

Nirav Jain
Nirav Jain

Reputation: 5107

You can use group_concat method as it is available in sqlite.

For your question i have created one table called Trans and added parent_id and cat_id columns. 

select parent_id, group_concat(cat_id) from trans group by parent_id;

I have table like this i have mentioned below.

enter image description here

and i got resultant like this :

enter image description here

Hope this will help you.

Upvotes: 1

Related Questions