What is the proper aproach for agregational like queries?

Question

looking into Java query approach or Kotlin based DNQ can't see how to make queries similar to 'group by' ... What is a proper approach for such queries? E.g. when I have invoices entities and I would like to group these by company name and sum of sales.

Please describe in more detail what is the company name -- just string property, or link to a dictionary value? — Vyacheslav Lukianov, Jul 02 '19 at 12:03
Just string property. I just went through txn.getAll(..) and grouped values using Java 8 streams. Took cca. 1.5s compared to 600ms in MongoDB (used aggregations) and 120ms in PosrgresSQL(used SQL groupy by clause) for the same data. Around 230K items in database. — David Marko, Jul 03 '19 at 11:29
It probably would be faster to go though `((PersistentStoreTransaction)txn).findWithPropSortedByValue(..)` instead of `txn.getAll(..)`. This method returns entities having specified property sorted by the property value, so groupping code may be easier. — Vyacheslav Lukianov, Jul 03 '19 at 16:12

score 0 · Accepted Answer · answered Jul 01 '19 at 00:45

Something like this can help you achieve an emulated "GROUP BY":

  entityStore.executeInTransaction(
      new StoreTransactionalExecutable() {
        @Override
        public void execute(@NotNull final StoreTransaction txn) {
          txn.getEntityTypes().forEach(type -> {
            EntityIterable result = txn.getAll(type);
            long count = result.count();
          });
        }
      });

Essentially what this does is it queries all entity types and then do a count, similar to what GROUP BY does. From here you can just create a Map or something to put both the entity type and count in the map.

What is the proper aproach for agregational like queries?

1 Answers1