Reputation: 67296
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
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
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.
and i got resultant like this :
Hope this will help you.
Upvotes: 1