Join with coalesce
marekbe opened this issue · 1 comments
marekbe commented
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. :)