Re: [sqlite] How do I combine these to 2 views ...

[Shane Dev]

So simple in hindsight, just add a second sort column 'close' to the union
and then traverse the tree - thanks.


On 26 November 2017 at 15:44, Clemens Ladisch  wrote:

> Shane Dev wrote:
> > Any ideas to achieve this?
>
> Use another CTE to bring all rows into the correct order.
> Then a standard tree walk:
>
> WITH RECURSIVE
> data AS (
>   SELECT id, line, par, 0 AS close
>   FROM vtag
>   UNION ALL
>   SELECT id, line, par, 1
>   FROM vparent_closetag
>   ORDER BY id, close  -- this relies on the ids being equal
> ),
> t AS (
>   SELECT id, line, 0 AS lev, close
>   FROM data
>   WHERE par IS NULL
>
>   UNION ALL
>
>   SELECT data.id, data.line, t.lev + 1, data.close
>   FROM t
>   JOIN data ON t.id = data.par
>AND t.close = 0
>   ORDER BY lev DESC
> )
> SELECT lev, line FROM t;
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I combine these to 2 views ...

[Clemens Ladisch]

Shane Dev wrote:
> Any ideas to achieve this?

Use another CTE to bring all rows into the correct order.
Then a standard tree walk:

WITH RECURSIVE
data AS (
  SELECT id, line, par, 0 AS close
  FROM vtag
  UNION ALL
  SELECT id, line, par, 1
  FROM vparent_closetag
  ORDER BY id, close  -- this relies on the ids being equal
),
t AS (
  SELECT id, line, 0 AS lev, close
  FROM data
  WHERE par IS NULL

  UNION ALL

  SELECT data.id, data.line, t.lev + 1, data.close
  FROM t
  JOIN data ON t.id = data.par
   AND t.close = 0
  ORDER BY lev DESC
)
SELECT lev, line FROM t;


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I combine these to 2 views ...

[Igor Tandetnik]

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   
2   1
3   1
4   2 
5   1
6   2 
7   3  
8   2 

sqlite> select id, level, line from vparent_closetag;
id  level   line
1   0   
3   1
5   1
6   2 

desired result-

order   level   line
1   0   
2   1
3   1
4   2 
5   1
6   1
7   2 
8   3  
9   2 
10  2 
11  1
12  0   

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

Re: [sqlite] How do I combine these to 2 views ...

[Shane Dev]

Yes, the parent ID column (par) is available in both views -

sqlite> select id, par, line from vtag;
id  par lev line
1   0   
2   1   1
3   1   1
4   3   2 
5   1   1
6   5   2 
7   6   3  
8   5   2 

sqlite> select id, par, line from vparent_closetag;
id  par lev line
1   0   
3   1   1
5   1   1
6   5   2 

I have tried many different UNION and WITH statements but I can't get the
desired result -

order   level   line
1   0   
2   1
3   1
4   2 
5   1
6   1
7   2 
8   3  
9   2 
10  2 
11  1
12  0   

Any help would be appreciated.

On 26 November 2017 at 11:30, Clemens Ladisch  wrote:

> Simon Slavin wrote:
> > On 26 Nov 2017, at 8:02am, Shane Dev  wrote:
> >> Any ideas to achieve this?
> >
> > Use the UNION keyword to combine the results of the two SELECT commands
>
> That would not order the close tags correctly.
>
> >> Any ideas to achieve this?
>
> Would it be possible to have parentid fields?
>
> 
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I combine these to 2 views ...

[Clemens Ladisch]

Simon Slavin wrote:
> On 26 Nov 2017, at 8:02am, Shane Dev  wrote:
>> Any ideas to achieve this?
>
> Use the UNION keyword to combine the results of the two SELECT commands

That would not order the close tags correctly.

>> Any ideas to achieve this?

Would it be possible to have parentid fields?




Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I combine these to 2 views ...

[Simon Slavin]

On 26 Nov 2017, at 8:02am, Shane Dev  wrote:

> Any ideas to achieve this?

Use the UNION keyword to combine the results of the two SELECT commands:



Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users