See on GitHub

    Perform Dynamic Group By Query

    Since RavenDB 4.0, the query optimizer supports dynamic group by queries and automatically creates auto map-reduce indexes.

    You can create a dynamic query that does an aggregation using the groupBy method.

    Supported aggregation operations include:

    • selectKey
    • selectSum
    • selectCount

    Group By Single Field 

    /** @var array<CountryAndQuantity> $orders */
$orders = $session->query(Order::class)
    ->groupBy("ShipTo.Country")
    ->selectKey("ShipTo.Country", "Country")
    ->selectSum(new GroupByField("Lines[].Quantity", "OrderedQuantity"))
    ->ofType(CountryAndQuantity::class)
    ->toList();
    from Orders
group by ShipTo.City
select ShipTo.City as Country, sum(Lines[].Quantity) as TotalQuantity

    Group By Multiple Fields 

    $results = $session->query(Order::class)
    ->groupBy("Employee", "Company")
    ->selectKey("Employee", "EmployeeIdentifier")
    ->selectKey("Company")
    ->selectCount()
    ->ofType(CountByCompanyAndEmployee::class)
    ->toList();
    from Orders
group by Employee, Company
select Employee as EmployeeIdentifier, Company, count()

    Select Composite GroupBy Key 

    /** @var array<CountOfEmployeeAndCompanyPairs> $orders */
$orders = $session->query(Order::class)
    ->groupBy("Employee", "Company")
    ->selectKey("key()", "EmployeeCompanyPair")
    ->selectCount("Count")
    ->ofType(CountOfEmployeeAndCompanyPairs::class)
    ->toList();
    from Orders 
group by Employee, Company
select key() as EmployeeCompanyPair, count()

    Group By Array

    By Array Values

    The following query groups by the Product property of the Lines collection, and calculates the count per ordered products.
    Underneath a fanout, an auto map-reduce index will be created to handle such a query. 

    /** @var array<ProductsInfo> $products */
$products = $session->query(Order::class)
    ->groupBy(GroupBy::array("Lines[].Product"))
    ->selectKey("key()", "Products")
    ->selectCount()
    ->ofType(ProductsInfo::class)
    ->toList();
    from Orders 
group by Lines[].Product
select Lines[].Product, count()

    Inside a single group by statement you can mix collection values and value of another property. 

    /** @var array<ProductInfo> $results */
$results = $session->advanced()->documentQuery(Order::class)
    ->groupBy("Lines[].Product", "ShipTo.Country")
    ->selectKey("Lines[].Product", "Product")
    ->selectKey("ShipTo.Country", "Country")
    ->selectCount()
    ->ofType(ProductInfo::class)
    ->toList();
    from Orders 
group by Lines[].Product, ShipTo.Country 
select Lines[].Product as Product, ShipTo.Country as Country, count()

    Grouping by multiple values from the same collection is supported as well:

    /** @var array<ProductInfo> $results */
$results = $session->query(Order::class)
    ->groupBy(GroupBy::array("Lines[].Product"), GroupBy::array("Lines[].Quantity"))
    ->selectKey("Lines[].Product", "Product")
    ->selectKey("Lines[].Quantity", "Quantity")
    ->selectCount()
    ->ofType(ProductInfo::class)
    ->toList();
    from Orders 
group by Lines[].Product, Lines[].Quantity 
select Lines[].Product as Product, Lines[].Quantity as Quantity, count()

    By Array Content

    Another option is to group by array content.
    The reduction key will be calculated based on all values of a collection specified in groupBy.

    /** @var array<ProductsInfo> $results */
$results = $session->query(Order::class)
    ->groupBy(GroupBy::array("Lines[].Product"))
    ->selectKey("key()", "Products")
    ->selectCount()
    ->ofType(ProductsInfo::class)
    ->toList();
    from Orders
group by array(Lines[].Product)
select key() as Products, count()

    Grouping by array content and a value of another property is supported by documentQuery:

    /** @var array<ProductsInfo> $results */
$results = $session->query(Order::class)
    ->groupBy(GroupBy::array("Lines[].Product"), GroupBy::field("ShipTo.Country"))
    ->selectKey("Lines[].Product", "Products")
    ->selectKey("ShipTo.Country", "Country")
    ->selectCount()
    ->ofType(ProductsInfo::class)
    ->toList();
    from Orders 
group by array(Lines[].Product), ShipTo.Country 
select Lines[].Product as Products, ShipTo.Country as Country, count()

    Grouping by multiple values from the same collection is also supported by documentQuery:

    /** @var array<ProductsInfo> $results */
$results = $session->query(Order::class)
    ->groupBy(GroupBy::array("Lines[].Product"), GroupBy::array("Lines[].Quantity"))
    ->selectKey("Lines[].Product", "Products")
    ->selectKey("Lines[].Quantity", "Quantities")
    ->selectCount()
    ->ofType(ProductsInfo::class)
    ->toList();
    from Orders 
group by array(Lines[].Product), array(Lines[].Quantity) 
select Lines[].Product as Products, Lines[].Quantity as Quantities, count()

    related articlesRelated Articles

    Session

    Indexes

    Home / Docs / Client Api / Session / Querying / How To Perform Group By Query