union/unionAll static functions

[lorefnon]

It would be nice to have a union/unionAll function that accepts collections of sub queries

Example usage:

const subQueries = input.blocks.map((block: BlockUpdateInput) => {
    return conn
        .selectFrom(tSomeTable)
        .select({ /* ... */ })
})

const query = unionAllOf(subQueries)

Currently attempting create a unionAll of arbitrary number of sub-queries using array.reduce etc. often results in difficult to debug type mismatches even if select args are exactly same.

[juanluispaz]

I believe that maybe there is a better way to express this query using tuple comparison, in that way you will not need to execute several queries in your database (with union). What do you think?

Note: Tuple comparison are not yet implemented in ts-sql-query, but I can start implementing it if that is a solution for you.

[lorefnon]

Interesting - I wasnt aware of the tuple comparision feature. Thanks for sharing.

In my case based on the filters coming from API, I construct different joins inside the map and take a union all of all the results. So I'll need to see if converting it to a big join that can accomodate all possible filters and then doing a tuple comparision still remains efficient. I'll explore some more and get back here.

[lorefnon]

So I think tuple comparisions are a great addition to this lib, and will certainly be useful. I do have some queries which I can simplify using tuple comparisions.

For the specific use case for which I had opened the request, I think I need union all because my joins depend on the incoming filters - removing that makes the logic more complex. I usually don't have more than a few filters so from perf. perspective it is acceptable.

I have found that the following simplifies the type issues:

const getSingleQuery = (input: FilterInput, conn: DBConnection) =>
   conn
        .selectFrom(...)
        .join(...)
        .select({ ...  })

type SelQuerySingle = NonNullable<ReturnType<typeof getSingleQuery>>
type SelQueryUnionRes = ReturnType<SelQuerySingle["unionAll"]>

And then later:

    const query = input.filters.reduce((q: SelQuerySingle | SelQueryUnionRes | undefined, filter)  => {
        const curQ = getSingleQuery(filter, conn)
        if (q && curQ) return q.unionAll(curQ)
        else if (curQ) return curQ
        return q
    }, undefined)

    const rows = await query?.executeSelectMany()

This may not be ideal because SelQueryUnionRes does not take into account the types of params passed to unionAll, but the rows type is correctly inferred so good enough for me.

I primarily didn't want to use the various *ExecutableSelectExpression* types as I find them to be somewhat hard to work with.

[juanluispaz]

Hi,

I believe with Mapping constant values as view functionality; this requirement is covered. See an example using it in a Bulk update

Let me know what you think.

[lorefnon]

Hi, I remember this addition from the discussion in #52 but I am not quite sure how this applies to context of this issue, where I was requesting simplifying union query SELECTs.

[juanluispaz]

As far I understood, the union is caused by you having a list of objects with different values that you must use at the same time case by case; that is why I mentioned the tuple comparison (that you can get, in some way, with the values tables). But, reading your comments again, I see you mentioning having different joins.

Can you elaborate a little bit on a real usage of what you have in mind? It may be in the direction of a new feature that I'm exploring to allow the execution of multiple independent queries in a single database call.

Reading your comments, it sounds like they must be considered a single list of different outputs instead of a list of different (maybe unrelated) outputs.

[juanluispaz]

Question: If given the first query, I allow the union/unionAll to receive an array of compatible queries and all of them are unioned with the first one (the one that tells me the rules for the projections). Would that be useful for the case that all queries are related?

[juanluispaz]

Question: What about including a method that receives the array of values and a function that, for each element in the array, creates a query using optional joins and any other dynamic feature to build that case? Restriction: You must return in a single point, no multi-return (of if return), or any other situation that creates a union type (like assigning all possibilities to an inferred-type variable). Issue, what happens if the columns to return are from a different table in each case

Another idea is to create a template query to define the starting point but not generated in the final query. Issue: how to define the type of the array needed to union all of them.

Another idea: a type where you define the expected output that receives the TypeScript interface of each line, and that type you can use as the type for the array that can be received by a method in the connection that union all elements. This will likely be the most flexible; what do you think?

[lorefnon]

If given the first query, I allow the union/unionAll to receive an array of compatible queries and all of them are unioned with the first one (the one that tells me the rules for the projections). Would that be useful for the case that all queries are related?

Yes, I think if we could have this, this would be ideal.

Can you elaborate a little bit on a real usage of what you have in mind

Our use case is a bit niche. We have purchase records which are in three different sets of tables with different schema due to largely historical reasons. Due to change in use cases and vendor migrations over time it is not so easy to fully unify the schema.

So, what we do (to abstract out this in API layer) is select from the relevant tables based on requested date range and create a union of results. This dynamic union is what I was originally seeking to simplify.

What we currently do is create a static union of all possible combinations (which is just 3-4 for us) and in the cases that we know wouldn't match we conditionally prepend a 1=0 condition. This is of course a hack and the generated query looks more complex, but works fairly well in practice. So, while a first class support for dynamic unions would be a welcome addition in the library, we are not blocked on this.

[juanluispaz]

In this scenario, the maximum number of unions is defined in code (each union query is independently built by you); if that is the case, the one possibility will be the union methods accept an optional query. If the query is not present, then it is not included. This will allow to skip some queries instead of having 1=0 as where (if the first query is always there).

Please pay attention to the last idea I wrote before your comment.

[lorefnon]

Another idea: a type where you define the expected output that receives the TypeScript interface of each line, and that type you can use as the type for the array that can be received by a method in the connection that union all elements. This will likely be the most flexible; what do you think?

I think this makes sense but it is a bit abstract for me to fathom.

What is particularly non intuitive to me, is why does the return type of .select(...) depends on anything other than type of columns being selected. If the return type of select were to be only a function of type of columns being selected, it wouldn't matter what table/view they are coming from, so we'd simply be able to reduce on an array of select outputs.

Having said that, if me having to provide the type of the selection explicitly simplifies things, I totally don't mind.

the union methods accept an optional query

Yeah, I think this is also a convenient alternative. I take it this would be similar to the .onlyWhen we have for boolean expressions ?