Open mhamri opened 3 years ago
ODataQueryOptions does not support GroupBy
and aggregates as far as I know - I don't think that's an option.
The tests have examples using $filter
- (different syntax from your example).
$count=true is also supported without grouping though.
@BlaiseD not quite correct,
this is what will be executed on sql server if I use [EnableQuery]
exec sp_executesql N'SELECT
N''statusInfo'',
N''color'',
[selectParam].[Key_1],
N''displayName'',
[selectParam].[Key_2],
(
SELECT
Count(*)
FROM
(
SELECT DISTINCT
[$it].[Id]
FROM
(
SELECT
IIF([it_1].[PlannedStartDate] IS NOT NULL AND [it_1].[ActualStartDate] IS NULL, IIF(DateAdd(day, -1, GetUtcDate()) > [it_1].[PlannedStartDate], 1, 0), IIF([it_1].[PlannedEndDate] IS NOT NULL AND [it_1].[ActualEndDate] IS NULL AND DateAdd(day, -1, GetUtcDate()) > [it_1].[PlannedEndDate], 1, 0)) as [IsLate],
[it].[Color],
[it].[DisplayName],
[it_1].[Id]
FROM
[ActivityModule_Activity] [it_1]
LEFT JOIN [ActivityStandardModule_ActivityStatus] [it] ON [it].[DeletedAt] IS NULL AND [it_1].[ActivityStatusId] = [it].[Id]
WHERE
[it_1].[DeletedAt] IS NULL
) [$it]
WHERE
[$it].[IsLate] = @TypedProperty AND (([selectParam].[Key_1] = [$it].[Color] OR [selectParam].[Key_1] IS NULL AND [$it].[Color] IS NULL) AND ([selectParam].[Key_2] = [$it].[DisplayName] OR [selectParam].[Key_2] IS NULL AND [$it].[DisplayName] IS NULL))
) [t1]
)
FROM
(
SELECT
IIF([it_3].[PlannedStartDate] IS NOT NULL AND [it_3].[ActualStartDate] IS NULL, IIF(DateAdd(day, -1, GetUtcDate()) > [it_3].[PlannedStartDate], 1, 0), IIF([it_3].[PlannedEndDate] IS NOT NULL AND [it_3].[ActualEndDate] IS NULL AND DateAdd(day, -1, GetUtcDate()) > [it_3].[PlannedEndDate], 1, 0)) as [IsLate],
[it_2].[Color] as [Key_1],
[it_2].[DisplayName] as [Key_2]
FROM
[ActivityModule_Activity] [it_3]
LEFT JOIN [ActivityStandardModule_ActivityStatus] [it_2] ON [it_2].[DeletedAt] IS NULL AND [it_3].[ActivityStatusId] = [it_2].[Id]
WHERE
[it_3].[DeletedAt] IS NULL
) [selectParam]
WHERE
[selectParam].[IsLate] = @TypedProperty
GROUP BY
[selectParam].[Key_1],
[selectParam].[Key_2]
',N'@TypedProperty bit',@TypedProperty=0
and if you check the OData Specification sub-section 3.10, groupby it's already supported,
ODataQueryOption
collects the query arguments, but if you look at the code, you can see that the ApplyQuery is the one apply the aggregate and group-by.
so I'm pretty sure it's supported by Odata library, but it's handled in [EnableQuery]
appreciate it if you re-open the issue since it's not a wrong usage and it's a quite valid query that is already working in production.
and one more thing, the filter out of the $apply
is different from the normal $filter
. one happens after the group by and one generally on the result
That's a good point. The ODataQueryOptions page does not list it either. PRs are welcome to implement the Apply clause.
I am trying to implement the Apply clause, but I have some difficulty understanding the code. Where would be the prefered place to ask questions regarding this project? Do you have a zulip, discord, slack, or other some platform?
This is it. The library gets its data from OdataQueryOptions
and builds expressions from that data. Take OrderBy as an example.
ok to ask questions here.
Also added discussions tab.
Any update on this?
PRs are welcome.
@ViliamVadocz Any progress here? Want to collaborate on this one?
@ViliamVadocz Any progress here? Want to collaborate on this one?
No progress. I have not looked at this for more than a year. I am also not particularly interested in this project anymore.
Hi, I'm aware of this package still is in preview and sorry to file an issue. I am not sure you've worked on this or not or you have any plan to support this, if you share your plan it helps a lot for me to plan my next set of actions.
Being said, I have a case that filters and aggregates are not applied.
URL:
My Current Usage that this query works:
Updated usage:
and this is generated Expression
as you can see, no group by or count distinct is added