How do I combine these to 2 views ...

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

How do I combine these to 2 views ...

Shane Dev
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?

-Shane
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How do I combine these to 2 views ...

Simon Slavin-3


On 26 Nov 2017, at 8:02am, Shane Dev <[hidden email]> wrote:

> Any ideas to achieve this?

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

<https://www.w3schools.com/sql/sql_union.asp>

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How do I combine these to 2 views ...

Clemens Ladisch
Simon Slavin wrote:
> On 26 Nov 2017, at 8:02am, Shane Dev <[hidden email]> 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?

<http://www.sqlite.org/lang_with.html#withorderby>


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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       <parent1>
2       1       1        <leaf1>
3       1       1        <parent2>
4       3       2         <leaf2>
5       1       1        <parent3>
6       5       2         <parent4>
7       6       3          <leaf4>
8       5       2         <leaf3>

sqlite> select id, par, line from vparent_closetag;
id      par     lev     line
1               0       </parent1>
3       1       1        </parent2>
5       1       1        </parent3>
6       5       2         </parent4>

I have tried many different UNION and WITH statements but I can't get the
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 help would be appreciated.

On 26 November 2017 at 11:30, Clemens Ladisch <[hidden email]> wrote:

> Simon Slavin wrote:
> > On 26 Nov 2017, at 8:02am, Shane Dev <[hidden email]> 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?
>
> <http://www.sqlite.org/lang_with.html#withorderby>
>
>
> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How do I combine these to 2 views ...

Igor Tandetnik-2
In reply to this post by Shane Dev
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How do I combine these to 2 views ...

Clemens Ladisch
In reply to this post by Shane Dev
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users