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

Reply via email to