Need help creating sum aggregation operation in HotChocolate Filters

Question

This question is based on the discussion in https://github.com/ChilliCream/hotchocolate/issues/924 - which is also where I've taken my inspiration.

I have a system wherein i keep a list of employees. Each employee has a WorkHours property on them representing how many hours thy work each week.

I also have a collection of tasks that needs to be solved by beforementioned Employees.

The association is handled via an Allocation class. This class holds two unix timestamps Start and End representing in which period of time an Employee is working on a specific Task. Furthermore they have a HoursPerWeek property representing how many hours each week the Employee has to spent on the give Task, the HoursPerWeek is necessary as an Employee can be associated with multiple tasks within the same time period as long as the sum of the Allocation.HoursPerWeek doesn't exceed the Employee.WorkHours.

Essentially i would like to achieve something along the lines of this LINQ query

var ts = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();
employees.Where(e =>
    e.Allocations
        .Where(a => a.Start < ts && a.End > ts)
        .Sum(a => a.HoursPerWeek)
    < e.WorkHours);

Which would effectively give me any employees that has leftover WorkHours in this point in time.

I havent been able to reference the employee.WorkHours directly in my query but i amde an attempt at just making it work comparing to doubles. This is how far I've gotten by now

public class CustomFilterConventionExtension : FilterConventionExtension
{
    protected override void Configure(IFilterConventionDescriptor descriptor)
    {
        descriptor.Operation(CustomFilterOperations.Sum)
            .Name("sum");

        descriptor.Configure<ListFilterInputType<FilterInputType<Allocation>>>(descriptor =>
        {
            descriptor
                .Operation(CustomFilterOperations.Sum)
                .Type<ComparableOperationFilterInputType<double>>();
        });

        descriptor.AddProviderExtension(new QueryableFilterProviderExtension(
            y =>
            {
                y.AddFieldHandler<EmployeeAllocationSumOperationHandler>();
            }));
    }
}

public class EmployeeAllocationSumOperationHandler : FilterOperationHandler<QueryableFilterContext, Expression>
{
    public override bool CanHandle(ITypeCompletionContext context, IFilterInputTypeDefinition typeDefinition,
        IFilterFieldDefinition fieldDefinition)
    {
        return context.Type is IListFilterInputType &&
               fieldDefinition is FilterOperationFieldDefinition { Id: CustomFilterOperations.Sum };
    }

    public override bool TryHandleEnter(QueryableFilterContext context, IFilterField field, ObjectFieldNode node, [NotNullWhen(true)] out ISyntaxVisitorAction? action)
    {
        var ts = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();
        var property = context.GetInstance();

        Expression<Func<ICollection<Allocation>, double>> expression = _ => _
            .Where(_ => _.Start < ts && _.End > ts)
            .Sum(_ => _.HoursPerWeek);
        var invoke = Expression.Invoke(expression, property);
        context.PushInstance(invoke);
        action = SyntaxVisitor.Continue;
        return true;
    }
}

services.AddGraphQLServer()
    .AddQueryType<EmployeeQuery>()
    .AddType<EmployeeType>()
    .AddProjections()
    .AddFiltering()
    .AddConvention<IFilterConvention, CustomFilterConventionExtension>()
    .AddSorting();

with that in place i then attempted to write my query

employees (where: {allocations: {sum: {gt: 5}}}) {
  nodes{
    id, name
  }
}

This implementation however keeps throwing an Exception becuase it's unable to cast from System.Obejctto System.Generic.IEnumerable.

in the final version however i would like to be able to query not with a const number but with the Employee WorkHours like so

employees (where: {allocations: {sum: {gt: workHours}}}) {
  nodes{
    id, name
  }
}

Anyone who can assist in creating suck a FilterOperation? Maybe you've made something similar, or know that it is in fact impossible.

I've put all my code in a GitHub repo if anyone would like to play around with it https://github.com/LordLyng/sum-filter-example

Morten Hau Lyng · Accepted Answer · 2021-04-21 19:54:56Z

I still haven't found a solution where i use actual aggregation. But i found a way to solve my specific issue at the database level instead of in GraphQL.

I ended up adding two props to my Entity public bool Available { get; set; } and public double AvailableHours { get; set; }. After adding these props i edited my EntityTypeConfiguration for the Employee Entity. Here i added the following lines. And please pardon my SQL, I'm far from fluent ;)

builder.Property(e => e.Available)
    .HasComputedColumnSql("CASE WHEN [WorkHours] > [dbo].[AllocSumForEmployee] ([Id]) THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END", stored: false)
    .ValueGeneratedOnAddOrUpdate();
builder.Property(e => e.AvailableHours)
    .HasComputedColumnSql("IIF([WorkHours] - [dbo].[AllocSumForEmployee] ([Id]) > 0, [WorkHours] - [dbo].[AllocSumForEmployee] ([Id]), 0)", stored: false)
    .ValueGeneratedOnAddOrUpdate();

a couple of things are going on here - Firstly, we are adding a computed column to our database schema. Secondly, we add the ValueGeneratedOnAddOrUpdate method to ensure these values aren't set by our application, but left to the database to handle.

The computed column method works in the context of a single row in a single table so by default it's now possible to query other tables or rows. Which is exactly what i needed in my case to resolve my issue.

The eagle-eyed observer may have already noticed that I've made use of something that looks an awful lot like a method in my computed columns. And in fact, is it just that.

I knew i would do a lot of Allocation lookups based on Start and End so i indexed those and create my migration by running dotnet ef migrations add "<migration name">. All that was left to do for the magic to do its thing was to modify the generated Migration.

The edited migration ended up looking as follows

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql(@"
        CREATE FUNCTION [dbo].[AllocSumForEmployee] (@id nvarchar(36))
        RETURNS Float
        AS 
        BEGIN
        RETURN 
            (SELECT COALESCE(SUM([HoursPerWeek]), 0) FROM [Allocations]
                WHERE 
                    [Start] < DATEDIFF_BIG(MILLISECOND,'1970-01-01 00:00:00.000', SYSUTCDATETIME()) AND 
                    [End] > DATEDIFF_BIG(MILLISECOND,'1970-01-01 00:00:00.000', SYSUTCDATETIME()) AND
                    [EmployeeId] = @id)
        END
    ");

    migrationBuilder.AddColumn<bool>(
        name: "Available",
        table: "Employees",
        type: "bit",
        nullable: false,
        computedColumnSql: "CASE WHEN [WorkHours] > [dbo].[AllocSumForEmployee] ([Id]) THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END",
        stored: false);

    migrationBuilder.AddColumn<double>(
        name: "AvailableHours",
        table: "Employees",
        type: "float",
        nullable: false,
        computedColumnSql: "IIF([WorkHours] - [dbo].[AllocSumForEmployee] ([Id]) > 0, [WorkHours] - [dbo].[AllocSumForEmployee] ([Id]), 0)",
        stored: false);

    migrationBuilder.CreateIndex(
        name: "IX_Allocations_End",
        table: "Allocations",
        column: "End");

    migrationBuilder.CreateIndex(
        name: "IX_Allocations_Start",
        table: "Allocations",
        column: "Start");
}

protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.DropIndex(
        name: "IX_Allocations_End",
        table: "Allocations");

    migrationBuilder.DropIndex(
        name: "IX_Allocations_Start",
        table: "Allocations");

    migrationBuilder.DropColumn(
        name: "Available",
        table: "Employees");

    migrationBuilder.DropColumn(
        name: "AvailableHours",
        table: "Employees");

    migrationBuilder.Sql("DROP FUNCTION [dbo].[AllocSumForEmployee]");
}

The "only" things i added that wasn't auto generated is the first part of the Up method and the last part of the Down method. Effectively registering a user-defined function in Up and removing said function in Down.

My functions returns the sum of HoursPerWeek all Allocations for a given Employee where Start (stored as unix timestamp in ms) is before now (DATEDIFF_BIG(MILLISECOND,'1970-01-01 00:00:00.000', SYSUTCDATETIME()) is a way to get now as a unix timestamp in ms in T-SQL), and where End is after now (i.e. active allocations).

This effectively means that the computation of the computed properties are done on the database and HotChocolte is none the wiser. And it allows me to do queries like

query {
  employees (where: {available: {eq: true}}) {
    nodes {
      id, name, available, availableHours
    }
  }
}

or even

query {
  employees (where: {availableHours: {gt: 15}}) {
    nodes {
      id, name, available, availableHours
    }
  }
}

Hope this helps other people trying to solve inline computation with aggregations!

The code for the alternative approach is available here https://github.com/LordLyng/sum-filter-example/tree/computed-prop-on-entity

Collectives™ on Stack Overflow

Need help creating sum aggregation operation in HotChocolate Filters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related