sqlkata/querybuilder

Join with coalesce

marekbe opened this issue · 1 comments

SqlKata: v.2.3.7
Framework: .NET 6.0
Compiler: PostgresCompiler

I cannot figure out if there is any way to create join with coalesce.
When I try this:

var query = new Query("Users")
    .Join("TableA", j =>
    {
        j.On("coalesce(Users.Id, 'null')", "coalesce(TableA.UserId, 'null')");
        return j;
    });

I recive:

SELECT * FROM "Users" 
INNER JOIN "TableA" ON ("coalesce(Users"."Id, 'null')" = "coalesce(TableA"."UserId, 'null')")

but expected result is:

SELECT * FROM "Users" 
INNER JOIN "TableA" ON (coalesce("Users"."Id", 'null') = coalesce("TableA"."UserId", 'null'))

I can't use operator IS NOT DISTINCT FROM because of redshift (by the way this doesn't work in playground)
I don't want to use condition like: (x.Id IS NULL AND y.Id IS NULL) OR x.Id = y.Id because of performance
I also don't want to use any Raw method like here

I will be grateful for your response, tips etc. :)

solution from link is acceptable