Supporting adapter-specific join types

[Karl Aksel Puulmann]

Hi!

Clickhouse supports a many of different join types which currently cannot be used via Ecto DSL: https://clickhouse.com/docs/en/sql-reference/statements/select/join

I'd love to build support for this, but unsure what an appropriate solution would look like. Would love some advice before opening a PR.

Would something like the following snippets make sense within ecto? 

- `from e in "table", left_any_join: s in "other", on: e.other_id == s.id`

- `from e in "table", left_any_join: s in "other", on: e.other_id == s.id`

- `from e in "table", asof_join: s in "other"

- ... 

How to deal with them being only supported by clickhouse? Raise by default, override behavior in adapter?

Past join type work can be found here:

- https://groups.google.com/g/elixir-ecto/c/1ts1tmv7UGo/m/c4hOhDiPAQAJ

- https://github.com/elixir-ecto/ecto/pull/4193

- https://github.com/plausible/ecto_ch/pull/76

Best

Karl

[Onorio Catenacci]

Since that's specific to one database engine, my initial thought would be that you either use native sql (via Fragments https://hexdocs.pm/ecto/Ecto.Query.html#module-fragments) or you could fork the Clickhouse driver and make the modifications yourself.  I think using Fragments is probably your best bet. Just a suggestion of course.

[José Valim]

Probably it makes the most sense to pass it as an additional option to join, since it is orthogonal to inner/left/etc. Otherwise there are just too many combinations.

--
You received this message because you are subscribed to the Google Groups "elixir-ecto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/31746733-ef40-43c3-a42f-487ffe2a9699n%40googlegroups.com.