Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Join with coalesce #611

Closed
marekbe opened this issue Sep 13, 2022 · 1 comment
Closed

Join with coalesce #611

marekbe opened this issue Sep 13, 2022 · 1 comment

Comments

@marekbe
Copy link

marekbe commented Sep 13, 2022

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. :)

@marekbe
Copy link
Author

marekbe commented Sep 13, 2022

solution from link is acceptable

@marekbe marekbe closed this as completed Sep 13, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant