Reduce and compute in FQL query

Help
1

Hi everyone,

I have a collection of todo list names and a second collection of item of the list.
I perform a query who return me the list of item with elements I need to compute.

   {
      data: [
        {
          exp: 57,
          resRef: Ref(Collection("Items"), "275703064172691981")
        },
        {
          exp: 57,
          resRef: Ref(Collection("Items"), "275703064172691981")
        },
        {
          exp: 23,
          resRef: Ref(Collection("Items"), "275703064172691981")
        },
        {
          exp: 22,
          resRef: Ref(Collection("Items"), "275703064172691981")
        },
        {
          exp: 12,
          resRef: Ref(Collection("Items"), "275703036630794764")
        },
        {
          exp: 10,
          resRef: Ref(Collection("Items"), "275703064172691981")
        },
        {
          exp: 10,
          resRef: Ref(Collection("Items"), "275703064172691981")
        },
        {
          exp: 10,
          resRef: Ref(Collection("Items"), "275703064172691981")
        },
        {
          exp: 9,
          resRef: Ref(Collection("Items"), "275703036630794764")
        },
        {
          exp: 5,
          resRef: Ref(Collection("Items"), "275703064172691981")
        },
        {
          exp: 3,
          resRef: Ref(Collection("Items"), "275703036630794764")
        }
      ]
    }

I need as a result to reduce the list and compute the exp to get a result like:

   {
      data: [
        {
          exp: 194,
          resRef: Ref(Collection("Items"), "275703036630794764")
        },
        {
          exp: 24,
          resRef: Ref(Collection("Items"), "275703064172691981")
        },
      ]
    }

I spend 2 days with Distinct, filter, and reduce, without success. If someone has an idea could be awesome. Thank you ::slight_smile:

2

Hi @Luc_L and welcome!
you can create an index like this:

CreateIndex(
    {
        name:'expByResRef',
        source:Collection('todos'),
        terms:[{field:['data','resRef']}],
        values:[{field:['data','exp']}]
    }
)

and use a query like below:

Map(
  Paginate(Documents(Collection('Items'))),
  Lambda('x',
        {
          exp:Sum(Paginate(Match('expByResRef',Var('x')))),
          resRef:Var('x')
        }
  )
)

hope this helps.

Luigi

4

Thank you @Luigi_Servini for your fast answer. I try to use your solution but I think I made an error in my first post to explain.
In my case I have 2 collections:

TodoList

{
  data:[
    {
      data: { name: "List 1" },
      ref: Ref(Collection("todo"), "275703064172691981")
    },
    {
      data: { name: "List 2" },
      ref: Ref(Collection("todo"), "275703036630794764")
    },
    {
      data: { name: "List 3" },
      ref: Ref(Collection("todo"), "112233445599288200")
    }
  ]
}

Item

{
      data: [
        {
          tag: ["help"],
          exp: 57,
          resRef: Ref(Collection("todo"), "275703064172691981")
        },
        {
          tag: ["FQL"],
          exp: 57,
          resRef: Ref(Collection("todo"), "275703064172691981")
        },
        {
          tag: ["help"],
          exp: 23,
          resRef: Ref(Collection("todo"), "275703036630794764")
        },
        {
          tag: ["help"],
          exp: 22,
          resRef: Ref(Collection("todo"), "275703036630794764")
        },
       {
          tag: ["graphQL"],
          exp: 12,
          resRef: Ref(Collection("todo"), "112233445599288200")
        },
   ]
}

I perform the first query to get only items of tag “help”

{
      data: [
         {
          tag: ["help"],
          exp: 57,
          resRef: Ref(Collection("todo"), "275703064172691981")
        },
         {
          tag: ["help"],
          exp: 23,
          resRef: Ref(Collection("todo"), "275703036630794764")
        },
        {
          tag: ["help"],
          exp: 22,
          resRef: Ref(Collection("todo"), "275703036630794764")
        },
      ]
    }

and after that, I need to “Distinct” by resRef and compute exp.

{
      data: [
         {
          tag: ["help"],
          exp: 57,
          resRef: Ref(Collection("todo"), "275703064172691981")
        },
         {
          tag: ["help"],
          exp: 45,
          resRef: Ref(Collection("todo"), "275703036630794764")
        },
      ]
    }

I hope it’s more clear like that.

5

From @Luigi_Servini answer I find my way with index. But I’m not sure if it’s cost-efficient :

So first I get Items from the tag “help”. It returns me todo Reference

Let(
  {
    setref: Map(
      ["help"],
      Lambda(
        "lemma",
        Let(
          {
            lemmaDoc: Match(Index("item_by_lemma"), Var("lemma"))
          },
          Var("lemmaDoc")
        )
      )
    ),

I reduce the list to remove duplicated references

    distinct: Distinct(
      Map(
        Paginate(Union(Var("setref"))),
        Lambda("x", Select([2], Var("x")))
      )
    ),

And I search with index Item again for specific to-do list reference and tag. I compute “exp” and return the result.

    compute: Map(
      Var('distinct'),
       Lambda(
        "todoRef",
        { exp: 
        Sum(
          Map(
            Map(
              ["help"],
              Lambda(
                "lemma",
                Sum(
                  Map(
                    Paginate(Union(Match(Index("exp_by_todoRef_and_lemma"), [ Var("lemma"), Var("todoRef")]))),
                      Lambda("x", Select([0], Var("x")))
                  )
                )
              )
            ),
            Lambda("x", Select([0], Var("x")))
          )
        ),
          todoRef: Var('todoRef')
        }
      )
    )
  },
 Var("compute")
)
6

Hi @Luc_L,
below something a bit more coincisive, but I guess using the same indexes:

 > Get(Index('resRefByTag'))
{ ref: Index("resRefByTag"),
  ts: 1599663243450000,
  active: true,
  serialized: true,
  name: 'resRefByTag',
  source: Collection("Items"),
  terms: [ { field: [ 'data', 'tag' ] } ],
  values: [ { field: [ 'data', 'resRef' ] } ],
  partitions: 1 }
> 
> Get(Index('expByresRef'))
{ ref: Index("expByresRef"),
  ts: 1599663424186000,
  active: true,
  serialized: true,
  name: 'expByresRef',
  source: Collection("Items"),
  terms: [ { field: [ 'data', 'resRef' ] } ],
  values: [ { field: [ 'data', 'exp' ] } ],
  partitions: 1 }

In the first index, you pass a tag and get back resRef. Then you pass the result to the second one for getting back exp

Map(
  Distinct(Paginate(Match("resRefByTag", "help"))),
  Lambda('ref',
      {
        resRef:Var('ref'),
        exp:Add(Select(['data'],Paginate(Match('expByresRef',Var('ref')))))
      }
  )
)

A bit more readable, but the logic is pretty the same.
Hope this help.

Luigi

1 Like