On 11/26/2017 3:02 AM, Shane Dev wrote:
Hello,
I am try to combine the following 2 views - vtag and vparent_closetag
sqlite> select id, level, line from vtag;
id|lev|line
id level line
1 0 <parent1>
2 1 <leaf1>
3 1 <parent2>
4 2 <leaf2>
5 1 <parent3>
6 2 <parent4>
7 3 <leaf4>
8 2 <leaf3>
sqlite> select id, level, line from vparent_closetag;
id level line
1 0 </parent1>
3 1 </parent2>
5 1 </parent3>
6 2 </parent4>
desired result-
order level line
1 0 <parent1>
2 1 <leaf1>
3 1 <parent2>
4 2 <leaf2>
5 1 </parent2>
6 1 <parent3>
7 2 <parent4>
8 3 <leaf4>
9 2 </parent4>
10 2 <leaf3>
11 1 </parent3>
12 0 </parent1>
Any ideas to achieve this?
That's what I'd do. Run a query like this:
select t.id, t.level, t.line tag, ct.line closing_tag
from vtag t left join vparent_closetag ct using (id)
order by t.id;
In the application, run through the resultset in order, keep a stack of {level,
closing_tag} pairs for those rows where closing_tag is not null. Whenever level is
reduced from one row to the next (so level_current < level_previous), pop from the
stack and insert into the result all pairs from the top of the stack where level
>= level_current. At the end, pop and insert all the remaining stack elements (as
if there were a last row with level=0).
It might be possible to express this in SQL with a clever recursive CTE query,
but it'd be easier to implement this in application code; and would most likely
work much faster.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
