Feature: `.findMany` & `.saveMany`

[schmidsi]

Proposal

The injected entity API should also expose bulk operations like findMany, saveMany. This seems like a simple addition to me that would enable better bulk operations and indexing-time aggregations like storing a rank, percentiles, etc.

By the example of the Eden Network Subgraph, that would enable them to save a lot of database reads and writes:

Instead of (simplified ranking logic from them):

let stakers = network.stakers.map<Staker | null>(id => Staker.load(id)).filter(staker => staker != null);

let sortedStakers = stakers((a, b) => 
    b.staked.div(WEI).minus(a.staked.div(WEI)).toI32()
);

sortedStakers.forEach((staker, index) => {
    staker.rank = BigInt.fromI32(index);
    staker.save();
});

They could do something like:

let stakers = Stakers.findMany({ orderBy: { staked: 'desc' } });

let rankedStakers = sortedStakers.map((staker, index) => {
    staker.rank = BigInt.fromI32(index);
    return staker;
});

Stakers.saveMany(rankedStakers);

Currently, they have ~7000 stakers. That addition would save them 14k-2 database operations.

Current behaviour

In order to update multiple entities in one mapping, subgraph developers are forced to store the ids of all entities that they would like to update in an array on a global singleton entity. That leads to unnecessary big arrays in the database and unnecessary amount of reads and writes.

Affected subgraphs

• Eden Network
• Superfluid
• ...

Inspiration

I personally think the Prisma V2 ORM is pretty well designed: https://www.prisma.io/docs/reference/api-reference/prisma-client-reference#findmany

[schmidsi]

Alternatives

Rank, percentile and other aggregations could also be specified in the schema and then automatically be stored like @aggregate(fn: "rank"). They might be more performant but this proposal is probably quicker to implement and would also enable other use-cases.

[chef-huan]

We meet similar limitations in our case

Problem - Our SC allows users to block their tokens for some amount of time. We want to calculate how many tokens are unlocked already unlocked. 
 Possible solution - Update all user entities in each block, to check if tokens are already unlocked based on the locking timestamp. 

From the entity side, I can fetch only by ID, not fetchAll. One of possible hint is to store all user ids in a separate entity. In handleBlock fetch all ids, iterate through them, and fetch one by one User.


type User @entity {
    "ID (hash)"
    id: ID!
    …
    pool: CakePool
}

type CakePool @entity {
    "ID (hash)"
    id: ID!
    …
    usersWithLockedCake: [String!]!
}

export function handleBlock(event: ethereum.Block): void {
  log.info("HandleBlock. Timestamp - {}", [event.timestamp.toString()]);

  let cakePool = CakePool.load("1");
  if (cakePool !== null) {
    let users = cakePool.usersWithLockedCake;
    if (users.length !== 0) {
      let userCount = users.length;
      log.info("HandleBlock. Users found. Length - {}", [userCount.toString()]);
      for (let i = 0; i < userCount; i++) {
        let user = User.load(users[i]);
        if (user !== null) {
          …
          }
        }
      }
    } else {
      log.error("HandleBlock. No users found.", []);
    }
    cakePool.save();
  }
  log.info("HandleBlock. End. Timestamp - {}", [event.timestamp.toString()]);
}

[github-actions[bot]]

Looks like this issue has been open for 6 months with no activity. Is it still relevant? If not, please remember to close it.

[Data-Nexus]

We're running into a similar issue with Silo where we want to be able to aggregate a users Collateral vs their Debt to get a liquidation percentage (if > 90% LTV then it's time to liquidate them).

Since token prices change regularly it would be nice to be able to have access bulk operations such as:

let positions = Position.findMany({ where: {token: {USDC_ADDRESS} } });

let newPositions = updatedStakers.map((position, index) => {
    position.collateralUSD = position.collateralBalance.times(token.lastPriceUSD)
    position.debtUSD = position.collateralBalance.times(token.lastPriceUSD)
    position.liquidationRisk = position.collateralUSD.div(position.debtUSD)
    return position;
});

Position.saveMany(newPositions);