Is there any support for UNION/INTERSECT/EXCEPT queries in ecto or any way to pass queries to a fragment instead of embedding fragments inside a query or do I need to delve down to base SQL itself? I was looking for INTERSECT for a particularly large query that requires a couple joins for each INTERSECT or would require 6 joins without INTERSECT, which becomes difficult to read and I like to avoid that when possible.
1 Like
For note, I already wrote it with the giant 6 JOIN query, this is mostly a curiosity thing at this point.
Ecto does not support it right now, but it’s definitely something we (or at least I) want to support in the future. If you have a good proposal on how to support it, I’ll be eager to hear it 
4 Likes
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…
1 Like
This is something we could consider. Please open an issue on ecto.
2 Likes
Done.
1 Like
