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

Where exists remove select component #581

Closed
xPudda opened this issue Jul 1, 2022 · 4 comments
Closed

Where exists remove select component #581

xPudda opened this issue Jul 1, 2022 · 4 comments

Comments

@xPudda
Copy link

xPudda commented Jul 1, 2022

Why in the WhereExists method there is a line of code that remove the select component and apply always a 1 costant?

// remove unneeded components
query = query.Clone().ClearComponent("select")
.SelectRaw("1");

I'm using SqlServer and I need to write an exists that through an except check some data.
In SqlServer it should be like this:

SELECT *
FROM   dbo.SALDI_DETTAGLIO AS sd
WHERE  NOT EXISTS ( SELECT sd2.STAB
                         , sd2.MAGA
                         , sd2.PROG
                    FROM   dbo.SALDI_DETTAGLIO AS sd2
                    WHERE  ( [sd].[STAB] = [sd2].[STAB]
                             AND [sd].[MAGA] = [sd2].[MAGA]
                             AND [sd].[RIDO] = [sd2].[PROG] )
                    EXCEPT
                    SELECT sd.STAB
                         , sd.MAGA
                         , sd.PROG );

Translated in SqlKata it should be:

new Query("dbo.SALDI_DETTAGLIO AS sd")
.WhereNotExists(
new Query("dbo.SALDI_DETTAGLIO AS sd2")
           .WhereColumns("sd.STAB", "=", "sd2.STAB")
           .WhereColumns("sd.MAGA", "=", "sd2.MAGA")
           .WhereColumns("sd.RIDO", "=", "sd2.PROG")
           .Select("sd2.STAB", "sd2.MAGA", "sd2.PROG")
           .CombineRaw("EXCEPT SELECT sd.STAB, sd.MAGA, sd.PROG"))

And the query is exactly the same as the sql. But actually, that line of code remove my select and apply 1:

SELECT *
FROM   dbo.SALDI_DETTAGLIO AS sd
WHERE  NOT EXISTS ( SELECT 1
                    FROM   dbo.SALDI_DETTAGLIO AS sd2
                    WHERE  ( [sd].[STAB] = [sd2].[STAB]
                             AND [sd].[MAGA] = [sd2].[MAGA]
                             AND [sd].[RIDO] = [sd2].[PROG] ));

As a workaround, i could use AddComponent method and pass the same new query as passed in the WhereNotExists and it works.

I think that line of code should be removed because it is wrong, let the outside decide what to select.

@ahmad-moussawi
Copy link
Contributor

ahmad-moussawi commented Jul 9, 2022

What is the advantage of passing a select in WHERE [NOT] EXISTS clause?
AFAK, the EXISTS look for the number of rows and totally ignore the columns, unless I am missing something here.

so to answer your question, SqlKata remove the SELECT part to reduce the query size

@xPudda
Copy link
Author

xPudda commented Jul 9, 2022

The exists run the subquery to test for the existence of rows as you said.

But, in the select you could do a lot of semantic operations like except, unions, other exists to check the existance, etc... to know if exists some records.
In my example, we have a table that has a foreign key in itself. In some way, we would like to ignore the records that have some values without making a cte or more complicated ways.

In fact, through the addComponent, it works.

@ahmad-moussawi
Copy link
Contributor

@xPudda now you can set the OmitSelectInsideExists = false in your compiler to prevent this behavior.

@xPudda
Copy link
Author

xPudda commented Oct 4, 2022

Thank you so much!

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

2 participants