Sum and sort by total

Help
1

How should I return paged data sorted by value after a sum?

For example, documents within the collection have: “user_id, item_a, item_b, item_c”;
I can do something with the example below, add the items and generate the total, but I would not be able to return each page to the user sorted by total (keeping the order between the pages).

Map(
  Paginate(Documents(Collection("collection_name"))),
  Lambda(
    "x",
    Let(
      {
        user_id: Select(['data', 'user_id'], Get(Var("x"))),
        a: Select(['data', 'item_a'], Get(Var("x"))),
        b: Select(['data', 'item_a'], Get(Var("x"))),
        c: Select(['data', 'item_c'], Get(Var("x"))),
  
      },
      {
        user_id: Var("user_id"),
        total: Sum( [Var("a"), Var("b"), Var("c")] )
      }
    )
  )
)

The only way to do this is paging the entire database and displaying part of the data to the user at each request for the entire database???

In this case, I have no way of generating an index with a reverse “true” in total, because “total” is generated at the time of the sum.

Maybe it’s a simple question but I’m adapting to the “fauna” way of doing things, where the thinking is different from SQL. In sql it would be something very simple, I imagine it is the same here too.

Thanks for helping me

2

Hi @Roco!

Your query makes it look like it returns a separate total for each document. If that’s the case, an index binding might be the way to go.

An index binding is an FQL Lambda function that executes when a document is indexed (during creation or update). A binding must be a “pure” function, which means it cannot perform other reads or writes. That means that bindings are pretty simple, but they can operate on the fields in a document.

Unfortunately, we can’t use the Sum function in a binding because it operates on sets, which implies read operations. But we can use Add.

Here’s an example of what that would look like:

> CreateCollection({ name: "things" })
{
  ref: Collection("things"),
  ts: 1670370382700000,
  history_days: 30,
  name: 'things'
}

> CreateIndex({
  name: "thing_totals",
  source: {
    collection: Collection("things"),
    fields: {
      total: Query(
        Lambda(
          "doc",
          Add(
            Select(["data", "item_a"], Var("doc")),
            Select(["data", "item_b"], Var("doc")),
            Select(["data", "item_c"], Var("doc")),
          )
        )
      )
    }
  },
  values: [
    { field: ["data", "userid"] },
    { binding: "total" },
  ]
})
{
  ref: Index("thing_totals"),
  ts: 1670370547100000,
  active: true,
  serialized: true,
  name: 'thing_totals',
  source: {
    collection: Collection("things"),
    fields: {
      total: Query(Lambda("doc", Add(Select(["data", "item_a"], Var("doc")), Select(["data", "item_b"], Var("doc")), Select(["data", "item_c"], Var("doc")))))
    }
  },
  values: [ { field: [ 'data', 'userid' ] }, { binding: 'total' } ],
  partitions: 8
}

> Create(
  Collection("things"),
  { data: { userid: "one", item_a: 12, item_b: 34, item_c: 56 }}
)
{
  ref: Ref(Collection("things"), "350341516210733568"),
  ts: 1670370556010000,
  data: { userid: 'one', item_a: 12, item_b: 34, item_c: 56 }
}

> Create(
  Collection("things"),
  { data: { userid: "two", item_a: 1, item_b: 3, item_c: 5 }}
)
{
  ref: Ref(Collection("things"), "350341524004798976"),
  ts: 1670370563420000,
  data: { userid: 'two', item_a: 1, item_b: 3, item_c: 5 }
}

> Create(
  Collection("things"),
  { data: { userid: "three", item_a: 2, item_b: 4, item_c: 6 }}
)
{
  ref: Ref(Collection("things"), "350341530980975104"),
  ts: 1670370570070000,
  data: { userid: 'three', item_a: 2, item_b: 4, item_c: 6 }
}

> Paginate(Match(Index("thing_totals")))
{ data: [ [ 'one', 102 ], [ 'three', 12 ], [ 'two', 9 ] ] }

For this example, the results are ordered by userid because that field appears first in the values definition. If you delete the index and recreate it with the total binding appearing first in the values definition, the results are sorted by the total. You could then set reverse: true on the total binding to sort in descending order.

Fauna doesn’t provide “ad hoc” sorting. You always have to create an index that defines how sorting should work. That makes it less interactive than SQL, but your queries tend to perform better with an index that defines the sorting/fields you need.

1 Like
3

Exactly what I was looking for.
I understand the reasoning, thank you very much.

In the documentation there is a reference to “binding”, but I think that I still thinking a lot in the “SQL” way, I didn’t do my research in the best possible way.

Thanks.

1 Like
4

This solution works fine, but now I have an array as a return and I need to bind the userid to its name which is in the user collection documents.
map does not work now with this array that was returned from the index.
How would you bring each userid with its respective username from the other collection?

5

I need to bind the userid to its name which is in the user collection documents.

To do that, we can update the index to return the name too.

First, you have to delete the existing index and wait 60 seconds; Fauna has to purge the index across all of the regions in the Region Group before you can make a new index with the same name.

Delete(Index("thing_totals"))

Once 60 seconds have elapsed, let’s add the name field:

> CreateIndex({
  name: "thing_totals",
  source: {
    collection: Collection("things"),
    fields: {
      total: Query(
        Lambda(
          "doc",
          Add(
            Select(["data", "item_a"], Var("doc")),
            Select(["data", "item_b"], Var("doc")),
            Select(["data", "item_c"], Var("doc")),
          )
        )
      )
    }
  },
  values: [
    { field: ["data", "userid"] },
    { field: ["data", "name"] },
    { binding: "total" },
  ]
})

None of my sample documents had a name field, so:

> Update(Ref(Collection("things"), "350500848447521280"), { data: { name: "Frodo" }})
{
  ref: Ref(Collection("things"), "350500848447521280"),
  ts: 1670523107840000,
  data: { userid: 'one', item_a: 12, item_b: 34, item_c: 56, name: 'Frodo' }
}

> Update(Ref(Collection("things"), "350500866466251264"), { data: { name: "Bilbo" }})
{
  ref: Ref(Collection("things"), "350500866466251264"),
  ts: 1670523149750000,
  data: { userid: 'three', item_a: 2, item_b: 4, item_c: 6, name: 'Bilbo' }
}

> Update(Ref(Collection("things"), "350500857331057152"), { data: { name: "Smaug" }})
{
  ref: Ref(Collection("things"), "350500857331057152"),
  ts: 1670523202380000,
  data: { userid: 'two', item_a: 1, item_b: 3, item_c: 5, name: 'Smaug' }
}

With the new index:

> Paginate(Match(Index("thing_totals")))
{
  data: [
    [ 'one', 'Frodo', 102 ],
    [ 'three', 'Bilbo', 12 ],
    [ 'two', 'Smaug', 9 ]
  ]
}

map does not work now with this array that was returned from the index.

Sure it does. Maybe there is some problem with the query you used. Here’s an example, using the new index, to return only the name and sums:

> Map(
  Paginate(Match(Index("thing_totals"))),
  Lambda(
    ["userid", "name", "sum"],
    {
      name: Var("name"),
      sum: Var("sum")
    }
  )
)
{
  data: [
    { name: 'Frodo', sum: 102 },
    { name: 'Bilbo', sum: 12 },
    { name: 'Smaug', sum: 9 }
  ]
}

As you can see, you can compose the result almost any way you want.

6

But my users are in another collection.
I’ll give my example, using my real basis (I tried to simplify with an example basis, but I may have simplified it too much)
It’s a game, and adds specific items to generate a score.
Users are in another collection.

I’ve already managed to get very close to what I want by doing the way below: (I don’t know if it’s the most correct)

Map(
  Select(
    "data",
    Map(
      q.Paginate(q.Match(q.Index("statistics_best_scorers_by_season_id"), 4)),
      Lambda("x", 
        { score: Select(0,Var("x")), player_id: Select(1,Var("x")), team_id: Select(2,Var("x")) }

      )
    )
  ),
  Lambda(
    "score_data",
     
    {
      player: Select('data', Get(Match( Index('players_by_season_id_and_player_id'), 4, Select('player_id',Var("score_data"))) ) ),
      score: Select('score',Var("score_data"))
    }
  )
)

It’s still not the way I’d like it with the grouped data and with and without bringing unnecessary data, but I can now combine the score data with the user data.

*The number four in the index is a season id that I get from the application and I must put it in the search

7

I’ll give my example, using my real basis (I tried to simplify with an example basis, but I may have simplified it too much)

No worries. There are a lot of similarities, even if the actual problem differs a bit from the original example.

You have two Maps when you only need one. Realizing that a Lambda can destructure an array parameter makes it much easier. I’ve re-indented your example to make sure I’ve got all of the brackets in the right place, and I’ve used JS driver syntax throughout.

q.Map(
  q.Paginate(
    q.Match(
      q.Index("statistics_best_scorers_by_season_id"),
      4
    )
  ),
  q.Lambda(
    ["score", "player_id", "team_id"],
    {
      player: q.Select(
        'data',
        q.Get(
          q.Match(
            q.Index('players_by_season_id_and_player_id'),
            4,
            q.Var('player_id')
          )
        )
      ),
      score: q.Var("score"),
    }
  )
)
1 Like