search

serenity-is / Serenity

Business Apps Made Simple with Asp.Net Core MVC / TypeScript
https://serenity.is
MIT License
2.57k stars 796 forks source link

feat: add cross apply to SqlQuery #6608

Closed vooba closed 1 year ago

vooba commented 1 year ago

We implemented a Enum Selection List like descriebed in https://github.com/serenity-is/Serenity/issues/4390#issuecomment-735658731 . Now we need to add a quick filter to the grid with Enums defined in this list. Therefor we need to add a Cross Apply to the SQL Query, but it is not implemented in the Fluent SQL SqlQuery.

The SQL Query we need to implement:

SELECT DISTINCT<all table fields> 
FROM Row1 r1
CROSS APPLY OPENJSON (r1.Enum) WITH (EnumId int '$') c1
WHERE c1.EnumId IN (1,2)
volkanceylan commented 1 year ago

Hmm not sure CrossApply is necessary in your query, but anyway. Why did you remove parenthesis from CrossApply constructor? What is its difference with OuterApply in that matter?

vooba commented 1 year ago

Hmm not sure CrossApply is necessary in your query, but anyway.

Thanks for the advice! I have found three solution for my problem: http://sqlfiddle.com/#!18/fd1ab/18

Why did you remove parenthesis from CrossApply constructor?

CROSS APPLY in combination with OPENJSON gives an error with the parenthesis, but I found that a subquery needs the parenthesis.

What is its difference with OuterApply in that matter?

CROSS APPLY is like an INNER JOIN OUTER APPLY is like a LEFT OUTER JOIN

As I have found an other solution for my problem, I think there is no need to merge this pull request any more.