Custom join statement

[darwin67]

Is your feature request related to a problem? Please describe.

I'm using goqu for building Clickhouse queries, and it's not possible to create a custom join syntax that's not in a typical SQL syntax.

The specific join I like to do is Clickhouse's ARRAY JOIN where it's commonly used for nested data structures. https://clickhouse.com/docs/en/sql-reference/statements/select/array-join

This is what it'll likely look like when generated.

SELECT * FROM <table>
ARRAY JOIN Foobars AS Foobar
...

Describe the solution you'd like

I'd like to have an API that I can provide the exact JOIN syntax like the following.

goqu.Select(fields...).From(model).Join(sq.L("ARRAY JOIN Foobars AS Foobar"))

Describe alternatives you've considered A clear and concise description of any alternative solutions or features you've considered.

ArrayJoin method Actually providing an ArrayJoin method in the builder that will result in ARRAY JOIN <whatever>. But this seems unrealistic considering no other SQL databases uses this syntax.

Dialect

• [ ] postgres
• [ ] mysql
• [ ] sqlite3
• [x] clickhouse

Additional context Add any other context or screenshots about the feature request here.

Overall, it has been great using goqu for Clickhouse even though it's not officially supported. Because the query builder don't allow custom syntaxes, there are a couple of occasions that we need to resort to raw string construction, and this is one of those. So it'll be helpful to have more options, where the specific builder could allow the full syntax specified by the user instead.

[WingGithub]

I'm in the same boat. Trying to use clickhouse's LEFT ANY JOIN and it looks like it will not be supported even if a clickhouse dialect is created because the join type is not a standard join type supported in the const list of exp.JoinType.

[darwin67]

PR created here: https://github.com/doug-martin/goqu/pull/430