Ecto: UNION/INTERSECT/EXCEPT support

[OvermindDL1]

Feature-request, was advised to post it here by @michalmuskala from: https://elixirforum.com/t/union-intersect-except-ecto-queries/1021

Copy/Paste the meat of my post from the above link:

If anything I'd just expect something like this:

iex> query1 = from blah blah blah
iex> query2 = from blorp blorp blorp
iex> query3 = union(^query1, ^query2)
iex> query4 = intersect(^query1, ^query2)
iex> query5 = except(^query1, ^query2)
iex> query6 = union_all(^query1, ^query2)
iex> query7 = intersect_all(^query1, ^query2)
iex> query8 = except_all(^query1, ^query2)

# Or:
iex> query1 = from blah blah blah
iex> query2 = from blorp blorp blorp
iex> query3 = union left: ^query1, right: ^query2
iex> query4 = intersect left: ^query1, right: ^query2
iex> query5 = except left: ^query1, right: ^query2
iex> query6 = union_all left: ^query1, right: ^query2
iex> query7 = intersect_all left: ^query1, right: ^query2
iex> query8 = except_all left: ^query1, right: ^query2

# Or:
iex> query1 = union left: from(blah blah blah), right: from(blorp blorp blorp)
iex> query2 = intersect left: from(blah blah blah), right: from(blorp blorp blorp)
iex> query3 = except left: from(blah blah blah), right: from(blorp blorp blorp)
iex> query4 = union_all left: from(blah blah blah), right: from(blorp blorp blorp)
iex> query5 = intersect_all left: from(blah blah blah), right: from(blorp blorp blorp)
iex> query6 = except_all left: from(blah blah blah), right: from(blorp blorp blorp)

Or maybe other keywords than left/right, but those make sense to me from a SQL/Set context

But, for example, union_all(left, right) could return something like %SQLSet{type: :union_all, left: left, right: right}

Then just pass a %SQLSet{type, left, right} struct or so with left/right keys to the adapter, which would then just basically do (in the case of PostgreSQL):

def get_sql(%SQLSet{type: typeq, left: leftq, right: rightq}) do
  left = get_sql(leftq)
  right = get_sql(rightq)
  settype = get_set_typename(typeq)
  # Maybe do something to verify matching output structures between left/right here
  left <> settype <> right
end

def get_set_typename(:union), do: " UNION "
def get_set_typename(:union_all), do: " UNION ALL "
def get_set_typename(:intersect), do: " INTERSECT "
def get_set_typename(:intersect_all), do: " INTERSECT ALL "
def get_set_typename(:except), do: " EXCEPT "
def get_set_typename(:except_all), do: " EXCEPT ALL "

Or something like that?

EDIT: Of course packing the SQLSet into the query struct somehow too...

[josevalim]

iex> query3 = union(^query1, ^query2)
iex> query4 = intersect(^query1, ^query2)
iex> query5 = except(^query1, ^query2)
iex> query6 = union_all(^query1, ^query2)
iex> query7 = intersect_all(^query1, ^query2)
iex> query8 = except_all(^query1, ^query

:+1: for the syntax above. I don't think we should pack it into the query struct though because it is not a regular query. For example, I can't compose after I write an union.

[michalmuskala]

How about adding those functions to something like Ecto.Query.Composition? Ecto.Query is usually imported and it already has quite a lot of functions, so I'd like to avoid adding yet another thing there.

It might also be possible to make those plain functions.

[josevalim]

I think they should be imported by default in our API. Any reason not to?

[michalmuskala]

I just don't think we should be importing a lot of functions that are rarely used. And I think those would be used rarely, compared to where or select.

[josevalim]

The reason I don't like this argument is that frequency of use is very
relative. Will UNION be used more than HAVING? And that generally for all
apps? I would prefer to have them all in one place than spread out
everywhere.

_José Valimwww.plataformatec.com.br
http://www.plataformatec.com.br/Founder and Director of R&D_

[michalmuskala]

Yeah, that makes sense. On the other hand with further things like or composing, etc, the query module keeps constantly growing.

[talentdeficit]

has anyone done any work on this? if not, i may pick it up

[sergiotapia]

Just had a use case that would be perfect for union support. https://elixirforum.com/t/best-way-to-join-results-of-two-queries/7500/2

[isaacsanders]

@michalmuskala @josevalim Would exporting (or re-exporting) functions in the top level API but having a separate struct (like @michalmuskala mentioned, Query.Composition, or something more accurate, like Query.SetOperation) be a way of not packing it into the query object, respecting that it is a different set of syntaxes than the FROM based queries?

[OvermindDL1]

Just remember that you can use, say, UNION or the others as a subquery too.

[josevalim]

@isaacsanders yes, we would need to introduce a new type of query and support it throughout Ecto (which is no easy feat).

[taavi]

Our resident DB expert at work suggested an improved query for my team…but it involves a UNION (and several combinations of WHERE clauses which would apply on each side…so hand-writing queries is not a viable option). And then using the UNION result as a subquery. ;) What's needed to help move this forward?

[hauleth]

I am also interested in that functionality as I would like to write wrapper for Postgres tablefunc module for my Ecto.OLAP library as it would be useful in some our queries to have possibility use pivot tables.

[josevalim]

Folks, we will close this issue since nobody has stepped up to work on it for a while.

At the same time, maybe a simpler solution is to have a helper that makes it easy to compose those queries into SQLs. For example:

{iodata_query, params} = Ecto.PGSet.union(some_ecto_query, another_ecto_query)
Repo.query(iodata_query, params)

Common table expressions could be done similarly:

{iodata_query, params} = Ecto.PGSet.with(query, table1: another_query, table2: yet_another_query)
Repo.query(iodata_query, params)

And the original query should access those tables as "table1" and "table2".

This should be much simpler than changing all of Ecto to support new data structures.

[yordis]

@josevalim I am having trouble following the thread and understanding why it was closed without adding the features into Ecto (also I failed to find Ecto.PGSet)

I am currently need to do some union in two queries so I would love to have this.

[michalmuskala]

We only keep open things that we plan to work on and it's not one of those features (you need to pick your battles). We're open to accepting PRs and providing guidance in case you'd like to work on this.

[yordis]

@michalmuskala would you mine to keep the issue open? Here is why.

When you search in issues it will show you by the default the open issues only if you keep this one open, people could find it and maybe contribute to the thread (still I want to tackle this when I find sometime)

[josevalim]

We only keep issues that the core team wants to actively work on. If we keep all issues that someone may want implemented, this becomes unmanageable and it will hurt our ability to maintain the codebase. This remained open by 1.5 years and we haven't seen any progress thus far.

It is not only about focusing on features that we want to implement but also about putting most of the focus on bugs.

[OvermindDL1]

Just as a data point, what I see most projects do for feature requests that they will accept PR's on but not do themselves is leave it open and use a 'HelpWanted' tag, which is easy for the core team to add as an ignored tag.

[hauleth]

@OvermindDL1 @josevalim @michalmuskala alternatively additional repo with RFC-like process for Ecto.

[acabreragnz]

I cannot understand why union implementation is ignored by the core team. Maybe this operator is less important than the others already implemented?

[josevalim]

@acabreragnz it is not being ignored. We will be glad to provide guidance and review but it is not our job to implement features companies and developers ask for. If the community is interested on this feature, they should get together and work on it. Other operators may be less important but they were added exactly because somebody worked on them.

[acabreragnz]

@josevalim thank you for your clarification and for the hard work to build Ecto.

[josevalim]

Union support is now in master thanks to the work of @feymartynov who provided an interesting solution to the problem (which is actually well aligned to the SQL specification). We don't have intersect/except support but the work in master is generic enough to support it easily. It should be mostly a matter of adding tests and docs.

[sergiotapia]

Thank you @feymartynov - great work!!!

[axelson]

For future reference here is the PR that adds UNION support: https://github.com/elixir-ecto/ecto/pull/2678
And here is the specific commit: https://github.com/elixir-ecto/ecto/commit/a901e38bbc0bf9167f42e58329d3c8bd3b2cf1eb