How to rewrite that query to prisma?

const qb: QueryBuilder<UserModel> = (orm.em.fork() as any).createQueryBuilder(UserModel, 'user');

      const result: Array<{ id: number; username: string; value: number }> = await orm.em
        .fork()
        .getConnection()
        .execute(
          qb
            .select(['user.id', 'user.username'])
            .where({ username: { $nin: ignored } })
            .join('user.tips', 'tips')
            .addSelect('COALESCE(SUM("tips"."inMainCurrencyAmount"), 0) as "value"')
            .offset(offset)
            .limit(limit)
            .groupBy('id')
            .getKnexQuery()
            .orderBy('value', 'desc')
            .toQuery(),
        );

In this case,

sum

is not supported directly for relations:

'COALESCE(SUM("tips"."inMainCurrencyAmount"), 0) as "value"'

So you will need to stick to a raw query for this one.