Are there plans to support Oracle dialect specific hierarchical queries (connect by)?

[platzhersh]

First off: I really appreciate all the work that has been put into this package and even more that it even supports oracle (as one of very few of the available query builders out there).

I am working on a project where we do use Oracle. We are rewriting some of our code and are using ts-sql-query in our new stack. It is really great, especially since it brings type-safety, whereas before, we were just writing plain sql strings.

We make excessive use of hierarchical queries in our existing code (https://docs.oracle.com/cd/B12037_01/server.101/b10759/queries003.htm). And so far I haven't really found a nice way to rewrite those queries into ts-sql-query to get the benefits of the type safety, since it would basically mean writing SQL Fragments?

We can get the same functionality using Recursive Selects. However, comparing a recursive select to a hierarchical query with the same functionality, we found out, that the hierarchical querry can be up to 60% more performant.

Are there any plans to start supporting hierarchical queries for Oracle in the future?

[juanluispaz]

Hi!!!

I was not aware of this functionality in Oracle; each database has different features; I got surprised time to time.

Can you add some query examples that look like something closer to what will be your real usage.

I will need read a little bit about this; I'm not sure if I need to add an additional syntax in ts-sql-query or just generate the proper optimized query when possible in oracle, what do you think?

Share with me your ideas on this and how ts-sql-query API will look like and I will implement it for you.

ts-sql-query already implement several database specific features; no issue to add something for oracle only.

[platzhersh]

Thank you for that very quick and very elaborate answer.

Here is the example we tried for comparing performance:

select ICODE as id,
                PCODE as parentId,
                NAME as name,
                KAT as category,
                STATUS as state FROM METAKAT
START WITH ICODE = 'd9d2b141-d3dc-4816-b2fb-79135f2b1c67'
CONNECT BY PRIOR PCODE = ICODE
fetch next :LIMIT rows only

And the (to our understanding) corresponding recursive select with the same output:

with recursive_select_1(id, parentId, name, category, state) as (
    select
        *
    from
        (
            select
                ICODE as id,
                PCODE as parentId,
                NAME as name,
                KAT as category,
                STATUS as state
            from
                METAKAT
            where
                ICODE = 'd9d2b141-d3dc-4816-b2fb-79135f2b1c67'
        )
    union all
    select
        METAKAT.ICODE as id,
        METAKAT.PCODE as parentId,
        METAKAT.NAME as name,
        METAKAT.KAT as category,
        METAKAT.STATUS as state
    from
        METAKAT
        join recursive_select_1 on recursive_select_1.parentId = METAKAT.ICODE
)
select
    id as "id",
    parentId as "parentId",
    name as "name",
    category as "category",
    state as "state"
from
    recursive_select_1

fetch next :LIMIT rows only

I am not an Oracle expert either. So this CONNECT BY was new to me aswell. We mainly use it for lookups. The Oracle Documentation link I posted in the opening post is a bit dry to read, but actually well documented.

[platzhersh]

This article might be a bit easier to get into it: https://www.dcodeman.com/connect-by-and-with-recursive-queries-in-oracle/ It also links a more digestible documentation on hierarchical queries: https://www.dcodeman.com/docs/oracle-sql/8-hierarchical-query

[platzhersh]

I've tried writing a temporary solution using rawFragments, but I can't seem to inject it at the right position with the available extension points:

export const connectByPrior = (
    connection: DBConnection,
    parentCol: string,
    currentCol: string,
    startWith?: string
): SelectCustomization<DB<'DBConnection'>> => {
    const parent = connection.const(parentCol, 'string');
    const current = connection.const(currentCol, 'string');

    if (!startWith) {
        return { afterQuery: connection.rawFragment`CONNECT BY PRIOR ${parent} = ${current}` };
    }

    const start = connection.const(startWith, 'string');
    return { afterQuery: connection.rawFragment`START WITH ${start} CONNECT BY PRIOR ${parent} = ${current}` };
};

using like

connection
            .selectFrom(metakatDbTable)
            .select(selection)
            .limit(10)
            .customizeQuery(
                connectByPrior(connection, 'ICODE', 'PCODE' /*, "ICODE = 'd9d2b141-d3dc-4816-b2fb-79135f2b1c67'"*/)
            )
            .executeSelectMany()

Gives me an error, because CONNECT BY would have to be before the fetch next :0 rows. Is there a way to get this query statement in the right order using the existing extension points?

[platzhersh]

I will need read a little bit about this; I'm not sure if I need to add an additional syntax in ts-sql-query or just generate the proper optimized query when possible in oracle, what do you think?

I think it would be an additional command, not just optimisation, as I think you usually want to explicitly use it or explicitly not :-)

[juanluispaz]

Hi,

I've been reviewing the documentation you shared.

Sql Fragment will not help you because no extension point is exposed for this kind of modification.

Conceptual things I identified:

• There is a where (called start with) to filter the initial cases; that is the where that appears in the currently supported recursive queries.
• There is a fake table prefixed with the keyword prior without a dot.
• The is a where clause used to connect child and parents (called connect by), that is the boolean condition in the arrow function in the current recursive syntax supported by ts-sql-query.
• There is a where clause that filters the outcome of the recursive query; in the sql standard, there is no way to make the recursive query in a single select; but, ts-sql-query offers a shortcut syntax to have that; in that shortcut syntax that looks like the Oracle query that where is not supported yet, but something possible; maybe I should extend the model to support it.
• The order of the clauses is odd, especially the previously mentioned one that doesn't follow Sql clause order structure.
• There is siblings modifier in the order by clause.
• There are pseudolomuns like level or connect_by_path

Current ts-sql-query syntax:

const recursiveChildrenCompany = connection.selectFrom(tCompany)
    .where(tCompany.id.equals(10)) // Oracle's start with clause
    .select({
        id: tCompany.id,
        name: tCompany.name,
        parentId: tCompany.parentId
    }).recursiveUnionAllOn((parent) => { // Parent is the PRIOR fake modified table
        return parent.id.equals(tCompany.parentId) // Oracle's connect by clause
    }).executeSelectMany()

This is what I have in mind as a possibility how it will looks like:

const recursiveChildrenCompany = connection.selectFrom(tCompany)
    .where(tCompany.id.equals(10)) // Oracle's start with clause
    .select({
        id: tCompany.id,
        name: tCompany.name,
        parentId: tCompany.parentId
    }).recursiveConectBy((prior) => { // PRIOR modifier function, prior doesn't operate over previous projected values, then it cannot behave like parent view in the already supported example; probably you will like to call it parent or child to make it more clear
        return prior(tCompany.id).equals(tCompany.parentId) // Oracle's connect by clause
    })
    .filterWhere( // Not sure about name, any idea?
       booleanExpression // Oracle's misplaced where clause
    )
    .executeSelectMany()

With the order by modifier, I'm not sure what to do; I can add a customisation modifier to you add it manually in customizeQuery, or I can add another variant to recursiveConectBy like recursiveConectByOrderingSiblingOnly

In the case of the pseudocolumn you will be able to use it using sql fragments

What do you think? Give me feedback, and I can start implementing it as an experimental feature specific for Oracle.

[platzhersh]

The screenshot was not matching the code examples, I updated it. (I had no order clause in the code example).

Your identified points and the possible implementation look very good! I'll try look into them in more detail later.

Sql Fragment will not help you because no extension point is exposed for this kind of modification.

In the case of the pseudocolumn you will be able to use it using sql

Thanks for these hints & clarifications

[juanluispaz]

There is another possibility, closer to Oracle's syntax (that is good), that will look like this:

const recursiveChildrenCompany = connection.selectFrom(tCompany)
    .where(booleanExpression) // Oracle's misplaced where clause
    .select({
        id: tCompany.id,
        name: tCompany.name,
        parentId: tCompany.parentId
    })
    .startWith(tCompany.id.equals(10)) // Oracle's start with clause
    .connectedBy((prior) => { // PRIOR modifier function, prior doesn't operate over previous projected values, then it cannot behave like parent view in the already supported example; probably you will like to call it parent or child to make it more clear
        return prior(tCompany.id).equals(tCompany.parentId) // Oracle's connect by clause
    })
    // ...
    .executeSelectMany()

In this version, it will be closer to Oracle syntax, making it easier to get what the query does if you know Oracle syntax; I always try to keep the syntax as close as possible to the generated sql.

The startWith is optional

There will be two options for connected by:

• connectedBy
• connectedByNoCycle

Not sure what to do with the order by modifier, one is put in your hand as sql customisation using a sql fragment, another possibility will be an optional function called orderingSiblingOnly that must be immediately after the connectedBy (I'm afraid it will create issues later); another possibility is add other two versions of the connectedBy function:

• connectedByOrderingSiblingOnly
• connectedByNoCycleOrderingSiblingOnly

I think this will be better, but worldly

Be aware, because ts-sql-query allows you to differ the where til the end of the query, this will be supported as well, allowing you to write something like (that in my mind it has more sense):

const recursiveChildrenCompany = connection.selectFrom(tCompany)
    .select({
        id: tCompany.id,
        name: tCompany.name,
        parentId: tCompany.parentId
    })
    .startWith(tCompany.id.equals(10)) // Oracle's start with clause
    .connectedBy((prior) => { // PRIOR modifier function, prior doesn't operate over previous projected values, then it cannot behave like parent view in the already supported example; probably you will like to call it parent or child to make it more clear
        return prior(tCompany.id).equals(tCompany.parentId) // Oracle's connect by clause
    })
    .where(booleanExpression) // Oracle's misplaced where clause in a more logical position
    // ...
    .executeSelectMany()

What do you think?

[juanluispaz]

I will need some feedback from you:

1. Which option to pick, the first one (like the current ts-sql-query recursive query) or the second one (closer to Oracle's syntax). I prefer the second option
2. What to do with the order by modifier: a. Separated orderingSiblingOnly function b. connectedBy variation functions (connectedByOrderingSiblingOnly, connectedByNoCycleOrderingSiblingOnly). I prefer this option c. Manually managed with sql fragment

Any pseudocolumn will require sql fragment (you can build your own reusable sql fragment)

Let me know what you and your coworkers would prefer.

[platzhersh]

This does look even better 👍

Be aware, according to documentation it is also possible to have multiple connect by, but you need at least one with prior

The whole order by siblingstopic indeed looks a bit complex to represent. I need to look into that one myself, i'll get back to you.

[juanluispaz]

Be aware, according to documentation it is also possible to have multiple connect by, but you need at least one with prior

That is odd; it is not reflected in the official documentation of the select syntax; it looks more like different examples in that part of the documentation in the screenshot. Additionally, it is possible to have connected by without prior according to some examples displayed in the links you shared.

Let me know when you make up your mind.

[platzhersh]

You are right, it is a list of different examples :-) Thanks for noticing. It's just that the first example has multiple comparisons, of which at least 1 needs to be marked with the PRIOR Flag

[platzhersh]

Thanks again for all these fast and detailed replies.

I will need some feedback from you:

1. Which option to pick, the first one (like the current ts-sql-query recursive query) or the second one (closer to Oracle's syntax). I prefer the second option

I prefer the second option too, since it's closer to the effectively generated SQL

2. What to do with the order by modifier: a. Separated orderingSiblingOnly function b. connectedBy variation functions (connectedByOrderingSiblingOnly, connectedByNoCycleOrderingSiblingOnly). I prefer this option c. Manually managed with sql fragment

Option a) would be the prettiest in my opinion, if it would be possible to only allow it directly after .connectBy() - I don't know the implementation details of your package, so that is up to you to comment. If that would not be possible, I think b) would be OK too.

Question to option c: how would you model this with a manually managed sql fragment?

[juanluispaz]

Good,

Let go for the closer Oracle's syntax.

Regarding the order by identifier, in that way, it will look like this:

const recursiveChildrenCompany = connection.selectFrom(tCompany)
    .select({
        id: tCompany.id,
        name: tCompany.name,
        parentId: tCompany.parentId
    })
    .startWith(tCompany.id.equals(10)) // Oracle's start with clause
    .connectedBy((prior) => { // PRIOR modifier function, prior doesn't operate over previous projected values, then it cannot behave like parent view in the already supported example; probably you will like to call it parent or child to make it more clear
        return prior(tCompany.id).equals(tCompany.parentId) // Oracle's connect by clause
    })
    .orderingSiblingOnly()
    .groupBy(....)
    .orderBy(....)
    .executeSelectMany()

But this will not work:

const recursiveChildrenCompany = connection.selectFrom(tCompany)
    .select({
        id: tCompany.id,
        name: tCompany.name,
        parentId: tCompany.parentId
    })
    .startWith(tCompany.id.equals(10)) // Oracle's start with clause
    .connectedBy((prior) => { // PRIOR modifier function, prior doesn't operate over previous projected values, then it cannot behave like parent view in the already supported example; probably you will like to call it parent or child to make it more clear
        return prior(tCompany.id).equals(tCompany.parentId) // Oracle's connect by clause
    })
    .groupBy(....)
    .orderBy(....)
    .orderingSiblingOnly() // Error
    .executeSelectMany()

The last one is hard to archive in a type-safe way because it can only appear if the connectedBy was used and it was not previously called; it will make more sense to be after the orderBy only if there is orderBy; I will need to explore it, but I think maybe, I can place after the orderBy. Do you think after the orderBy will be better?

Regarding option c, I will need to create a new extension point to support it in the customizeQuery function; but I don't like it so much because it will not have another use besides this specific keyword.

Let me know your impressions on this.

[juanluispaz]

Option a) would be the prettiest in my opinion, if it would be possible to only allow it directly after .connectBy() - I don't know the implementation details of your package, so that is up to you to comment. If that would not be possible, I think b) would be OK too.

ts-sql-query is written like a DSL, where there are several intermediate interfaces that only allow you to use the proper next functions; any allowed clause-order is directly build-in in the library; this is in this way to ensure everything is correct; you didn't forget to include a table, you don't generate multiple entries for the same clause, or destroy the previous one; in general ensure the safety of the one that builds the query, but the cost is to have more type definitions for the intermedia state.

The easiest for me is to place the orderingSiblingOnly immediately after the connectedBy because, at that moment, I know I'm in that state; then, I can make a specific interface that represents that situation. But, in ts-sql-query my drivers are not an easy implementation of the package; my driver is easy, understandable and safe queries, even when that makes more complicated the things inside ts-sql-query.

Said that, I'm not sure what you would prefer i. orderingSiblingOnly immediately after connectedBy ii. orderingSiblingOnly immediately after orderBy only when connectedBy was used. I like this more.

Let me know what you think makes more sense.

[platzhersh]

Said that, I'm not sure what you would prefer i. orderingSiblingOnly immediately after connectedBy ii. orderingSiblingOnly immediately after orderBy only when connectedBy was used. I like this more.

To me option a sounds more intuitive, especially if there would not be a global orderBy. Also naming wise maybe we could stick to the naming of the actual commands: connectBy and orderSiblingsBy

[juanluispaz]

Sorry, I'm getting lost.

ts-sql-query offers you several options to build the order by clause:

    orderBy(column: string, mode?: OrderByMode): this
    /** Allows to specify an order by dynamically, it is parsed from the provided string */
    orderByFromString(orderBy: string): this
    orderByFromStringIfValue(orderBy: string | null | undefined): this

You can call these methods as many times as you want to extend the order by clause, which will be:

order by firstCall, secondCall, ...

In Oracle what happens is there is an optional modifier that needs to appear inside the "order by" text, but not per each clause, that will be:

order SIBLINGS as firstCall, secondCall, ...

The thing is, I need to know that I must generate that modifier; usually, modifiers are placed at the end (there are several in ts-sql-query), as an optional call, but here it is in the middle of the beginning text. Additionally, that modifier only is used if there is a generated order by clause.

My first option, which I like most, will be to have a method at the end of the order that introduces the modifiers, only available when there is an order by. It will look like this:

const recursiveChildrenCompany = connection.selectFrom(tCompany)
    .select({
        id: tCompany.id,
        name: tCompany.name,
        parentId: tCompany.parentId
    })
    .startWith(tCompany.id.equals(10)) // Oracle's start with clause
    .connectedBy((prior) => { // PRIOR modifier function, prior doesn't operate over previous projected values, then it cannot behave like parent view in the already supported example; probably you will like to call it parent or child to make it more clear
        return prior(tCompany.id).equals(tCompany.parentId) // Oracle's connect by clause
    })
    .groupBy(....)
    .orderBy(....)
    .orderBy(....) // Second order by
    .orderingSiblingsOnly()
    .executeSelectMany()

It crossed my mind a second option: create an alternative version of the order by function called orderSiblingsBy..., and when you call one of them, you can only call another orderSiblingsBy... to extend the order by clause. This option is, maybe, closer to Oracle. It will look like this:

const recursiveChildrenCompany = connection.selectFrom(tCompany)
    .select({
        id: tCompany.id,
        name: tCompany.name,
        parentId: tCompany.parentId
    })
    .startWith(tCompany.id.equals(10)) // Oracle's start with clause
    .connectedBy((prior) => { // PRIOR modifier function, prior doesn't operate over previous projected values, then it cannot behave like parent view in the already supported example; probably you will like to call it parent or child to make it more clear
        return prior(tCompany.id).equals(tCompany.parentId) // Oracle's connect by clause
    })
    .groupBy(....)
    .orderSiblingsBy(....)
    .orderSiblingsBy(....) // Second order by
    .executeSelectMany()

There is a third option, which is to place the modifier method immediately after the connectedBy, but this is the farthest way to Oracle syntax, and you will need to place a modifier of the order by far away where you are really using it. It will look like this:

const recursiveChildrenCompany = connection.selectFrom(tCompany)
    .select({
        id: tCompany.id,
        name: tCompany.name,
        parentId: tCompany.parentId
    })
    .startWith(tCompany.id.equals(10)) // Oracle's start with clause
    .connectedBy((prior) => { // PRIOR modifier function, prior doesn't operate over previous projected values, then it cannot behave like parent view in the already supported example; probably you will like to call it parent or child to make it more clear
        return prior(tCompany.id).equals(tCompany.parentId) // Oracle's connect by clause
    })
    .orderingSiblingsOnly()
    .groupBy(....)
    .orderBy(....)
    .orderBy(....) // Second order by
    .executeSelectMany()

Another issue with this option will be the fact you can call the order by modifier without having order by in your query, that modifier will do nothing, and that is counterintuitive:

const recursiveChildrenCompany = connection.selectFrom(tCompany)
    .select({
        id: tCompany.id,
        name: tCompany.name,
        parentId: tCompany.parentId
    })
    .startWith(tCompany.id.equals(10)) // Oracle's start with clause
    .connectedBy((prior) => { // PRIOR modifier function, prior doesn't operate over previous projected values, then it cannot behave like parent view in the already supported example; probably you will like to call it parent or child to make it more clear
        return prior(tCompany.id).equals(tCompany.parentId) // Oracle's connect by clause
    })
    .orderingSiblingsOnly() // Not used
    .groupBy(....)
    .executeSelectMany()

What do you think?

I want to implement this feature this weekend.

[platzhersh]

Thank you so much for elaborating!

I will answer in more detail tomorrow morning, but i still wanted to reply quickly already.

The second option seems the best to me for mainly one reason: it allows to use a regular orderBy, as well as a orderSiblingsBy in the same sql statement - which is a possible and valid use case afaik.

I will look into it again in more detail tomorrow though, as promised.

Have a nice evening for now

Juan Luis Paz @.***> schrieb am Do., 30. März 2023, 15:41:

Sorry, I'm getting lost.

ts-sql-query offers you several options to build the order by clause:

orderBy(column: string, mode?: OrderByMode): this
/** Allows to specify an order by dynamically, it is parsed from the provided string */
orderByFromString(orderBy: string): this
orderByFromStringIfValue(orderBy: string | null | undefined): this

You can call these methods as many times as you want to extend the order by clause, which will be:

order by firstCall, secondCall, ...

In Oracle what happens is there is an optional modifier that needs to appear inside the "order by" text, but not per each clause, that will be:

order SIBLINGS as firstCall, secondCall, ...

The thing is, I need to know that I must generate that modifier; usually, modifiers are placed at the end (there are several in ts-sql-query), as an optional call, but here it is in the middle of the beginning text. Additionally, that modifier only is used if there is a generated order by clause.

My first option, which I like most, will be to have a method at the end of the order that introduces the modifiers, only available when there is an order by. It will look like this:

const recursiveChildrenCompany = connection.selectFrom(tCompany) .select({ id: tCompany.id, name: tCompany.name, parentId: tCompany.parentId }) .startWith(tCompany.id.equals(10)) // Oracle's start with clause .connectedBy((prior) => { // PRIOR modifier function, prior doesn't operate over previous projected values, then it cannot behave like parent view in the already supported example; probably you will like to call it parent or child to make it more clear return prior(tCompany.id).equals(tCompany.parentId) // Oracle's connect by clause }) .groupBy(....) .orderBy(....) .orderBy(....) // Second order by .orderingSiblingsOnly() .executeSelectMany()

It crossed my mind a second option: create an alternative version of the order by function called orderSiblingsBy..., and when you call one of them, you can only call another orderSiblingsBy... to extend the order by clause. This option is, maybe, closer to Oracle. It will look like this:

const recursiveChildrenCompany = connection.selectFrom(tCompany) .select({ id: tCompany.id, name: tCompany.name, parentId: tCompany.parentId }) .startWith(tCompany.id.equals(10)) // Oracle's start with clause .connectedBy((prior) => { // PRIOR modifier function, prior doesn't operate over previous projected values, then it cannot behave like parent view in the already supported example; probably you will like to call it parent or child to make it more clear return prior(tCompany.id).equals(tCompany.parentId) // Oracle's connect by clause }) .groupBy(....) .orderSiblingsBy(....) .orderSiblingsBy(....) // Second order by .executeSelectMany()

There is a third option, which is to place the modifier method immediately after the connectedBy, but this is the farthest way to Oracle syntax, and you will need to place a modifier of the order by far away where you are really using it. It will look like this:

const recursiveChildrenCompany = connection.selectFrom(tCompany) .select({ id: tCompany.id, name: tCompany.name, parentId: tCompany.parentId }) .startWith(tCompany.id.equals(10)) // Oracle's start with clause .connectedBy((prior) => { // PRIOR modifier function, prior doesn't operate over previous projected values, then it cannot behave like parent view in the already supported example; probably you will like to call it parent or child to make it more clear return prior(tCompany.id).equals(tCompany.parentId) // Oracle's connect by clause }) .orderingSiblingsOnly() .groupBy(....) .orderBy(....) .orderBy(....) // Second order by .executeSelectMany()

Another issue with this option will be the fact you can call the order by modifier without having order by in your query, that modifier will do nothing, and that is counterintuitive:

const recursiveChildrenCompany = connection.selectFrom(tCompany) .select({ id: tCompany.id, name: tCompany.name, parentId: tCompany.parentId }) .startWith(tCompany.id.equals(10)) // Oracle's start with clause .connectedBy((prior) => { // PRIOR modifier function, prior doesn't operate over previous projected values, then it cannot behave like parent view in the already supported example; probably you will like to call it parent or child to make it more clear return prior(tCompany.id).equals(tCompany.parentId) // Oracle's connect by clause }) .orderingSiblingsOnly() // Not used .groupBy(....) .executeSelectMany()

What do you think?

I want to implement this feature this weekend.

— Reply to this email directly, view it on GitHub https://github.com/juanluispaz/ts-sql-query/issues/98#issuecomment-1490327320, or unsubscribe https://github.com/notifications/unsubscribe-auth/AALA7QZUFLQ6ZWFTIBGCMOLW6WERNANCNFSM6AAAAAAWFK7SPA . You are receiving this because you authored the thread.Message ID: @.***>

[juanluispaz]

I don't see that possibility in Oracle's documentation; you will need to pick one of them; but not combine between them. That is why I'm thinking more about the first option.

[platzhersh]

You are right once again, I was wrong :) Sorry for the confusion.

Therefore I agree with going for the first option! Thanks a lot for your continuous feedback!

And I am looking forward to try it out once implemented :-) If you want any testing / code reviewing / more feedback, just let me know, I'll be happy to try and help out as best as I can.

[juanluispaz]

Good,

I will implement it that way; when it is ready, I will publish a release to you to make additional tests in your environment and provide any feedback on this.

[juanluispaz]

Released ts-sql-query 1.52.0 with public support to Oracle connect by syntax for recursive queries. Please, let me know your feedback.

[platzhersh]

Thank you! Very much appreciated! I will try look into it until the end of the week!

[platzhersh]

Works great, thank you so much!