Filters: Aggregations

[michaelstaib]

Scroll down for the description :)

[PascalSenn]

Aggregations

This proposal is very Work In Progress

Introduction

Aggregations are very useful for querying big data sources. To count items or sum a value, it's not necessary to load all the data. Querying already aggregated data in the backend has less overhead.

Proposal

A new API Endpoint is generated for a collection. The type of this endpoint has different aggregation fields on them.

It will not be possible to cover all aggregation cases. We simply cannot create types for all possible scenarios.

Proposed are the following:

Example Objects

All examples will take place in the following domain:

public class Foo {
     public string role {get;set;}
     public int num {get;set;}
     public Bar bars {get;set;}
}

public class Bar {
     public string baz {get;set;}
     public int blub {get;set;}
}

count

{
  foosMeta {
    count
}

returns the amount of foo's in foos.

sum

{
  foosMeta {
    sum_num
}

returns the sum of all nums.

average

{
  foosMeta {
    sum_average
}

returns the average of all nums.

group_by

Example

{
  foosMeta {
    group_by {
      role {
        key
        value {
          bar {
            baz
          }
        }
      }
    }
  }
}

How others did it

Prisma (Issue prisma/prisma#1312)

# Count all posts with a title containing 'GraphQL'
query {
  postsConnection(where: {
    title_contains: "GraphQL"
  }) {
    aggregate {
      count
    }
  }
}
{
  allUsersConnection(where: {city: "Aarhus"}) {
    aggregate {
      avg {
        age
      }
      max {
        age
      }
    }
    edges {
      node { name, age }
    }
  }
}
{
  allUsersConnection(where: {city: "Aarhus"}) {
    aggregate {
      avg {
        age
      }
      max {
        age
      }
    }
    edges {
      node { name, age }
    }
  }
}

Horsura

query {
  article_aggregate {
    aggregate {
      count
      sum {
        rating
      }
      avg {
        rating
      }
      max {
        rating
      }
    }
    nodes {
      id
      title
      rating
    }
  }
}
query {
  author (where: {id: {_eq: 1}}) {
    id
    name
    articles_aggregate {
      aggregate {
        count
        avg {
          rating
        }
        max {
          rating
        }
      }
      nodes {
        id
        title
        rating
      }
    }
  }
}

Gatsby

### no example found

GraphCMS

### no example found

[angrymrt]

In your average example, I guess you mean average_num, instead of sum_average. How would this be configured on the c# side of things?

[PascalSenn]

@angrymrt You are right it would be average_num. This is just a proposal. I currently do not longer thing the meta endpoint makes that much sense
It definitely makes sense to integrate this into connections like Prisma does it.
But I also think we can aggregate endpoints at different places

We usually first work on the graphql API specification before we do the code API.
Probably via descriptor and type.
Also with UseSelection we would optimize these aggregations over IQueryable directly in the database.
At the moment I could think of Expressions that we then rip apart and recompile.

{
  allUsersConnection(where: {city: "Aarhus"}) {
    aggregate {
      avg {
        age
      }
      max {
        age
      }
    }
    edges {
      node { name, age }
    }
  }
}

[michaelstaib]

I currently think also that we could go down the rout of lodash for aggregations. We definitely will add support in the client for that.

[michaelstaib]

https://github.com/APIs-guru/graphql-lodash

[zliebersbach]

Hi all, just wondering what the status of this issue is, and if there hasn't been any progress I would love to offer some assistance. I just need to be pointed in the right direction 😄

[PascalSenn]

Hi @kkliebersbach
There was indeed no progress. we were playing around internally a bit but nothing made it to a contribution. Which approach would you want to take? the directive one or the type version on the connections?

[XardasLord]

I'm really waiting for this feature.

[shawndube]

I have to implement some kind of aggregation in the next 2 week and was going to try and do the Hasura syntax. @PascalSenn do you have any guidance on how to even define the ObjectTypes for that from a HotChocolate perspective? All my other current code is a code-first implementation so I'd like to stick with that if possible but its not clear to me how to define it. Did you maybe start a prototype that is available to look at?

[PascalSenn]

@shawndube You can have a look at the Data project. Filtering is a good example.
We usually implement everything in a really generic way. If you are going to implement this for a 1 time usecase i recommend you to just use normal object types with the fields on them that you need. It is probably a lot easier

[shakibstu]

@PascalSenn we are seriously waiting for that feature

[gojanpaolo]

Cross-posting my comment in #1260 (comment) which is also for aggregations but for sorting

Is there a way to have a graphql sort using aggregate of list field that is similar to the following C# code?

var sessionsWithAttendeesFirst = sessions.OrderByDescending(s => s.Attendees.Any());
var sessionsByMostAttendees = sessions.OrderByDescending(s => s.Attendees.Count());

I'm thinking something like this in graphql

sessions (order: { attendees: { any: DESC } } )
sessions (order: { attendees: { count: DESC } } )

[tjolr]

@PascalSenn do you know approximately when we can expect this feature to be implemented? E.g: is it closer to 1 month or 1 year?
Really looking forward to it! :-)

[michaelstaib]

@tjolr this feature can be easily done on your own with type extensions or type interceptors.

We have put aggregates towards the end of this year since there is really not a so big need to have this in the sense that it is easy to build this on your own.

However, we will do it and have scheduled it for the November release. No promises, it might be moved to an earlier or later release. We in general look at feedback of the community to determine the need of a feature.

What we could however do is write up a blog post outlining how one could implement this. @PascalSenn what do you think?

[tjolr]

@michaelstaib a blog post about how this can be done would be good! 👍 But we can't wait for the official implementation of the data aggregation as well!! :-) 💯

[chungonion]

Hi @ademchenko and @symeus-sgs and @LordLyng. I also came across the issue on aggregation fields, where I would like to count the total entries of a field (not just the numbers of entries returning in pagination). I am not sure whether my scenario is the same as your scenario, but at least I managed to get the aggregated field response that fit into my use case.

I will use the example at graphql-workshop fluent type configurations for explanation.

in Configure method, you may resolve a "custom field" with

descriptor  
      .Field("sessionsCount")  
      .ResolveWith<SpeakerResolvers>(t => t.GetSessionsCountAsync(default!, default!, default!, default))  
      .UseDbContext<ApplicationDbContext>();

and in the SpeakerResolvers class, add the GetSessionsCountAsync method

public async Task<IEnumerable<int>> GetSessionsCountAsync(
    Speaker speaker,
    [ScopedService] ApplicationDbContext dbContext,
    SessionByIdDataLoader sessionById,
    CancellationToken cancellationToken)
{
    int[] sessionIds = await dbContext.Speakers
        .Where(s => s.Id == speaker.Id)
        .Include(s => s.SessionSpeakers)
        .SelectMany(s => s.SessionSpeakers.Select(t => t.SessionId))
        .ToArrayAsync();

    return sessionsIds.Length; //Side note here, you can mess up with anykinds of aggreation you want
}

viola! the aggregated value will now be returned in the graphQL response.

[michaelstaib]

We are introducing a new projection engine with V13 that will open up the engine to do proper aggregations. We are still experimenting but it looks promising.

[ademchenko]

@chungonion , the issue is not about including into response some custom field which could be an aggregation field or any other one. The issue is to build the aggregation over the filtered ([UseFiltering]) collection- so, to reuse the actual filter to build the aggregated values. @michaelstaib could you or your teammates, please, explain how to do that in version 12, because version 13 is too far for us.

[PascalSenn]

@ademchenko

using System;
using System.Collections.Generic;
using System.Linq;
using HotChocolate;
using HotChocolate.Data;
using HotChocolate.Data.Filters.Expressions;
using HotChocolate.Data.Sorting.Expressions;
using HotChocolate.Resolvers;
using HotChocolate.Types;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;

namespace Test
{
    public class Startup
    {
        // This method gets called by the runtime. Use this method to add services to the container.
        // For more information on how to configure your application, visit https://go.microsoft.com/fwlink/?LinkID=398940
        public void ConfigureServices(IServiceCollection services)
        {
            services
                .AddRouting()
                .AddGraphQLServer()
                .AddQueryType<Query>()
                .AddTypeExtension<FooConntectionExtensions>()
                .AddFiltering()
                .AddSorting()
                .AddProjections();
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }

            app.UseWebSockets();
            app.UseRouting();

            app.UseEndpoints(endpoints =>
            {
                // By default the GraphQL server is mapped to /graphql
                // This route also provides you with our GraphQL IDE. In order to configure the
                // the GraphQL IDE use endpoints.MapGraphQL().WithToolOptions(...).
                endpoints.MapGraphQL();
            });
        }
    }

    [ExtendObjectType(WellKnownTypeNames.FooConnection)]
    public class FooConntectionExtensions
    {
        public int TotalOfNumbers([ScopedState(WellKnownContextData.Data)] IEnumerable<Foo> foo) =>
            foo.Sum(x => x.Number);

        public int MaxOfNumbers([ScopedState(WellKnownContextData.Data)] IEnumerable<Foo> foo) =>
            foo.Max(x => x.Number);

        public int MinOfNumbers([ScopedState(WellKnownContextData.Data)] IEnumerable<Foo> foo) =>
            foo.Min(x => x.Number);
    }

    public static class WellKnownContextData
    {
        public const string Data = nameof(Data);
    }

    public static class WellKnownTypeNames
    {
        public const string FooConnection = nameof(FooConnection);
    }

    public class Query
    {
        [UsePaging(ConnectionName = "Foo")]
        [UseProjection]
        [UseFiltering]
        [UseSorting]
        public IEnumerable<Foo> GetFoos(IResolverContext context)
        {
            IEnumerable<Foo> result = _data.Filter(context).Sort(context);

            context.ScopedContextData =
                context.ScopedContextData.SetItem(WellKnownContextData.Data, result);

            return result;
        }

        private static IEnumerable<Foo> _data = new Foo[]
        {
            new(Guid.NewGuid(), 1, "A"), new(Guid.NewGuid(), 2, "A"),
            new(Guid.NewGuid(), 4, "A"), new(Guid.NewGuid(), 3, "A")
        };
    }

    public record Foo
    {
        public Foo()
        {
        }

        public Foo(Guid id, int number, string name)
        {
            Id = id;
            Name = name;
            Number = number;
        }

        public Guid Id { get; init; }
        public int Number { get; init; }
        public string Name { get; init; }
    }
}

[ademchenko]

@PascalSenn does the same approach work for OffsetPaging?

[MaheshB0ngani]

@tjolr this feature can be easily done on your own with type extensions or type interceptors.

We have put aggregates towards the end of this year since there is really not a so big need to have this in the sense that it is easy to build this on your own.

However, we will do it and have scheduled it for the November release. No promises, it might be moved to an earlier or later release. We in general look at feedback of the community to determine the need of a feature.

What we could however do is write up a blog post outlining how one could implement this. @PascalSenn what do you think?

Waiting for this feature, or the blog post explaining how to do this on our end.

[michaelstaib]

@MaheshB0ngani look at the code examples pascal posted.

#924 (comment)

[tobias-tengler]

How you can add aggregation fields to the Connection type is also documented here.

[ademchenko]

@MaheshB0ngani which API for filtering do you use?
The new one or the old one?

[MaheshB0ngani]

Hi @michaelstaib Thank you for your quick response.

I was expecting a feature like group by on the fields the type and which has to be executed on the Database using Entity Framework and IQueryable.

 public IQueryable<User> GetUsers([Service] DbContext repository)
        => repository.Users; // many other tables are involved here using some joins.

User model may look like

User{
  Name,
  FavouriteFood,
  FavSport,
  FavTeam,
  State,
  Country,
}

I know the example I used might be silly. However, my original requirement is exactly similar.
I need a list with the following criteria (after lot more joins with other tables and some other logic)

SELECT FavTeam, COUNT(*) FROM Users
GROUP BY FavTeam

SELECT Country, FavTeam, COUNT(*) FROM Users
GROUP BY Country, FavTeam


SELECT Country, FavSport, COUNT(*) FROM Users
GROUP BY Country, FavSport


SELECT State, FavSport, COUNT(*) FROM Users
GROUP BY State, FavSport

and lot more, fully customizable from our UI. We need to be as generic as possible. I love the way HotChocolate will do filtering, Sorting and Pagination on top of EF & IQueryable. And also expected HotChocolate would have something out of the box for us to do Aggregations (Dynamic Group by) in very simple manner.

Note: I'm using Entity Framework Core to query the database and expecting the data to be queries on the Database and not in-memory after fetching into .Net application.
Also, there are other models apart of Users, so the the number of grouping combinations are very huge.

Please guide me with an example of how to implement these kind of dynamic grouping things.

[VarunSaiTeja]

Any update on this? When we can expect this?
@PascalSenn

[badsyntax]

We have a similar use case to @ademchenko and others in this thread, in that we don't want aggregates on the paginated result set, we want aggregates on the entire table, with filters applied.

Use case:

I want to render paginated JobParts table, where JobParts are assigned to a particular job. I also want to show the total cost for all JobParts assigned to a particular job.

For this use case we need aggregates applied using the filtered (non-paginated) results.

This is the approach we took (which is actually very similar to @PascalSenn's approach):

query {  
    jobParts(where: { jobId: { eq: 1 }}) {
        items {
            id
            cost
        }
        aggregates {
            cost {
                sum
            }
        }
    }
}

Aggregates are added to the CollectionSegment for JobParts, and are using async resolvers. The difficulty I faced was being able to "share" filters between parent and child resolvers. I was not able to find a solution where the child resolver could re-use parent resolver filters, so I solved this by sharing the IQueryable Expression via ScopedState.

Here's our parent JobParts resolver:

[ExtendObjectType(OperationTypeNames.Query)]
public class JobPartQuery
{
    [UseDbContext(typeof(TenantDbContext))]
    [Authorize(Policy = Auth.Authorization.Permissions.RepairsRead)]
    [UseOffsetPaging]
    [UseProjection]
    [UseFiltering]
    [UseSorting]
    [GraphQLDescription("Provides a list of Job Parts")]
    public IQueryable<JobPart> JobParts(
        [ScopedService] TenantDbContext dbContext,
        IResolverContext context
    )
    {
        var jobParts = dbContext.JobParts.AsNoTracking().Filter(context);

        // Required for aggregates
        context.SetScopedValue(JobPartContextData.JobPartsQueryExpression, jobParts.Expression);

        return jobParts;
    }
}

[ExtendObjectType("JobPartCollectionSegment")]
public class JobPartAggregatesCollectionSegment
{
    public JobPartAggregates Aggregates()
    {
        return new JobPartAggregates();
    }
}

We then bind aggregate fields to resolvers in our JobPartAggregatesType:

descriptor.Field(input => input.Cost)
    .Type<AggregateType<decimal, FloatType>>()
    .ResolveWith<JobPartAggregatesQuery>(r => r.Cost(default, default))
    .Description("JobPart Cost aggregates.");

In our aggregate resolver we create a new query using the Filtered Expression on a new dbContext:

public class JobPartAggregatesQuery
{
    [UseDbContext(typeof(TenantDbContext))]
    public async Task<Aggregate<decimal>> Cost(
        [ScopedService] TenantDbContext dbContext,
        [ScopedState(JobPartContextData.JobPartsQueryExpression)] Expression queryExpression
    )
    {
        var jobParts = dbContext
            .GetService<IAsyncQueryProvider>()
            .CreateQuery<JobPart>(queryExpression)
            .AsNoTracking();

        return await jobParts
            .GroupBy(x => 1)
            .Select(grp => new Aggregate<decimal>
            {
                Min = grp.Min(x => x.Cost),
                Max = grp.Max(x => x.Cost),
                Sum = grp.Sum(x => x.Cost)
            })
            .SingleAsync();
    }
}

It's a bit hacky but it works. Let me know if you have any suggestions to improve this.

[relfman-cmg]

Is there any way to use the queryExpression in a joined query? E.g.

var jobParts = dbContext
    .GetService<IAsyncQueryProvider>()
    .CreateQuery<JobPart>(queryExpression)
    .AsNoTracking();

var q = from jp in jobParts
    join somethingElse in dbContext.SomethingElse on somethingElse.Id equals jp.Id
    group new { jp, somethingElse } by true
    select new Aggregate<decimal>
    {
        Min = grp.Min(x => x.somethingElse.Number),
        Max = grp.Max(x => x.somethingElse.Number),
        Sum = grp.Sum(x => x.somethingElse.Number)
    })
    
return await q.SingleAsync();

I get an exception that you cannot use 2 contexts. Is there a way to "hook" into the original context?

[IharYakimush]

It is possible with custom expression visitor that will update expression tree to use single db context.

private sealed class ReplaceContextVisitor<T> : ExpressionVisitor
    where T : class
{
    private readonly DbContext _context;
    private readonly bool _replaceRoot;

    public ReplaceContextVisitor(DbContext context, bool replaceRoot)
    {
        this._context = context ?? throw new ArgumentNullException(nameof(context));
        this._replaceRoot = replaceRoot;
    }

    protected override Expression VisitExtension(Expression node)
    {
        if (node is EntityQueryRootExpression root)
        {
            if (this._replaceRoot)
            {
                if (node.Type.IsAssignableTo(typeof(IQueryable<T>)))
                {
                    Expression<Func<IQueryable<T>>> qwe = () => this._context.Set<T>();

                    return qwe.Body;
                }
            }
            else
            {
                var replacement = (EntityQueryRootExpression)this._context.Set<T>().AsQueryable().Expression;

                return replacement.UpdateEntityType(root.EntityType);
            }
        }

        return base.VisitExtension(node);
    }

    public override Expression? Visit(Expression? node)
    {
        if (node?.Type == this._context.GetType())
        {
            return Expression.Constant(this._context);
        }

        return base.Visit(node);
    }
}

usage:

bool replaceRoot = false; // may be true depending on use case.
Expression replaced = new ReplaceContextVisitor<T>(context, replaceRoot).Visit(queryExpression)!;
var jobParts = dbContext
    .GetService<IAsyncQueryProvider>()
    .CreateQuery<JobPart>(replaced)
    .AsNoTracking();

[vplemyannik]

Any updates? I'm looking forward for this feature

[gianmariaconti]

Any updates?

[superrnovae]

Any updates on this?

How can I apply the aggregations after the projection? I haven't found any examples yet.