Group by in FQL

[DBA]

Hi,
I am trying to do a simple Group By in FQL.
I have two collections in my Fauna DB:

Sellers and Orders. I want to make a list of all sellers with their orders listed after each, like this:

Seller 1

Order 1
Order 2

Seller2

Order 1

Seller3

Order1
Order2
Order3

In SQL I would probably do it like this:

SELECT Sellers.SellerName,Orders.OrderID AS OrdersID FROM Orders,Sellers

WHERE Orders.SellerID = Sellers.SellerID

GROUP BY SellerName

I have searched everywhere for an example in FQL so I can try it in the Fauna web shell, but have not found it anywhere.

[DBA]

With this FQL I get the orders matching the Seller Kurt, by I can’t figure out how to show the coresponding Sellers record?

Here I have one index for the Seller collection and one for the Order collection. It gets the correct Orders.

Map(
                  Paginate(
                    Join(
                    Match(Index("SellerByOrderName"), "Kurt"),
                    Index("OrderBySellerName")
                    )
                    )
                    ,
                  Lambda("ref",
                    Let(
                      {
                        Orders: Get(Var("ref"))
                      },
                      {
                        id: Select(["ref", "id"], Var("Orders")),
                        receivername: Select(["data", "receivername"], Var("Orders")),
                        name: Select(["data", "orderurl"], Var("Orders")),
                        position: Select(["data", "imageurl"], Var("Orders"))
                      }

                    )
                  )
                )

[DBA]

Maybe it would be easier to query if I put an array of Order refs in each Sellers collection? That way maybe I could just query the Sellers collection.

[ptpaterson]

Hi @DBA and welcome!

FQL doesn’t have declaritive syntax like GROUP BY, rather it works a lot more like a regular programming language. FQL has Map, Filter and Reduce functions.

The way you approach this depends on whether you are focused on either

1. fetching a Set of Sellers and also fetching their Orders, or
2. fetching a Set of Orders and grouping by Seller

This matters less with your example because you are not filtering on anything, so you effective want everything. But the approach you take will matter if you want to filter on Orders or on Sellers.

Sellers first

This is the easiest. Get all of the Sellers that you want and use Map to also fetch the orders.

Map(
  Paginate(Documents(Collection("Seller"))),
  Lambda(
    "ref",
    Merge(
      Get("ref"),
      {
        data: Merge(
          Select("data", Get(Var("ref")),
          {
            // adds a new `orders` field to Seller.data
            orders: Paginate(Match(Index("Order_by_seller"), Var("ref")))
            // or whatever you need to do to fetch the particular orders for this seller
          }
        )
      }
    )
  )
)

Orders first

You can take your Set of Orders that you want (in this case all of them, but you could filter or use an Index here) and use Reduce to achieve the group by.

I said FQL is a lot more like a regular programming language, so let’s look at an example of a group_by function in javascript and then translate that to FQL.

orders.reduce((result, order) => {
  const key = order.seller

  const existing_orders = result[key] ?? []

  return {
    ...result,
    [key]: [...existing_orders, order]
  }
}, {})

Here, we iterate over all of the items in the array and

• determine a key for each item
• if that key exists in the result object, then append the value to that key in the result
• if the key does not exist, add it to the result

We can do something similar in FQL

Reduce(
  Lambda(
    ['result', 'order'],
    Let(
      {
        key: Select(['seller'], Var('order')),
        existing_orders: Select(Var('key'), Var('result'), []),
      },
      Merge(
        Var("result"),
        ToObject([[Var("key"), Append(Var("order"), Var("existing_orders"))]])
      )
    )
  ),
  {},
  Var('orders')
)

This will return an object that looks like the following, where the keys are the Seller IDs.

{
  "355034656429572182": [
    { /* order1 ... */ },
    { /* order2 ... */ },
  ],
  "333736995637428290": [
    { /* order1 ... */ },
  ],
  "312254774755983424": [
    { /* order1 ... */ },
    { /* order2 ... */ },
    { /* order3 ... */ },
  ]
}

You can transform that however you like. ToArray and ToObject are helpful FQL functions for manipulating objects with arbitrary keys.

[DBA]

Ok, so then I loop over that object to get the rest of the Sellers info?

[ptpaterson]

Sure!

One thing you could do is transform that with ToArray, then Map over that to build an array of objects in the shape you want.

Let(
  {
    orders: Paginate(Match(Index("Orders_by_some_terms"), ...)),
    orders_grouped_by_seller_id: Reduce(...), // from previous post
  },
  Map(
    ToArray(Var("orders_grouped_by_seller_id")),
    Lambda(
      ["id", "orders"],
        
      // result like in the "Sellers first" example
      Merge(
        Get(Ref(Collection("Seller"), Var("id"))),
        {
          data: Merge(
            Select("data", Get(Ref(Collection("Seller"), Var("id")))),
            {
              orders: Var("orders")
            }
          )
        }
      )

    )
  )
)