Support oneOf for relations

[23doors]

Basic info (please complete the following information):

• ObjectBox version: 2.0.0
• Flutter/Dart SDK: Flutter 3.7.7
• Null-safety enabled: yes
• Reproducibility: always
• OS: macOs, but it shoudlnt exactly matter

Steps to reproduce

Dart version lacks any kind of eager loading of relations so I wanted to batch get them rather then fetching it one by one.

Example: ObjectA with ToOne relation to ObjectB.

final q = (db.store.box<ObjectA>().query()..link(ObjectA_.relation, ObjectB_.id.oneOf([1])))
    .build();
final res = q.find();

getting

Unsupported operation: Unsupported type for IN: 11

Similarly:

final q = (db.store.box<ObjectA>().query(ObjectA_.relation.oneOf([1])).build();
final res = q.find();

Same error.

Expected behavior

Return ObjectA list where relation is filtered by oneOf.

[greenrobot-team]

Thanks for reporting. If I remember correctly a relation property only supports the equals condition, e.g. this should work:

(db.store.box<ObjectA>().query()..link(ObjectA_.relation, ObjectB_.id.equals(1)))

Edit: I guess this is then a request to support eager loading of relations.

[23doors]

I guess you meant .equals(1)?

This doesnt necessarily affect eager loading but also when I want to get all objectA associated with several objectBs.

So e.g. looking for something like:

(db.store.box<ObjectA>().query()..link(ObjectA_.relation, ObjectB_.id.equals([1,2,3,4,5])))

And this wouldn't work currently to my knowledge.

[greenrobot-team]

If your actual use case is just to eager load the relation, you could create an async transaction that queries the owning objects as desired and then just access each ToOne to load it before returning the results. E.g. something like:

List<ObjectA> eagerLoad(Store store, Object param) {
  final results = store.box<ObjectA>().getAll();
  for (var a in results) {
    a.objectB;
  }
  return results;
}
final aWithB = await env.store.runInTransactionAsync(TxMode.read, eagerLoad, null);

[23doors]

Unless I am unaware of some of internals of this library, that's not a great example and lacks any kind of standard early loading optimizations.

Imagine that in your case you have 10 000 objectA and 3 objectB. You would end up with 10 000 queries of object B while only 1 would suffice. Even if querying is still fast enough, that's just very suboptimal. Normally eager loading would:

1. Get filtered objectA.
2. Assuming joins are not supported, gather objectB ids from (1) and fetch them.
3. Match now two lists with each other.

And sure, you can do that on your own (although it becomes a huge hassle to do manually in ToMany relations, nested relations etc). But still we're missing the point here. I was mostly providing a use case for id.oneOf() and this issue wasn't specifically meant to be about missing eager loading support in dart version (which is available in other implementations though).

Another use case.

• CarCompany
• CarModel

Say we selected e.g. 10 CarCompanies. Now we want to get top 10 rated models they manufactured that have automatic gearbox.

Normally you would do in pseudo sql: select * from carmodel where gear=automatic and company_id in (1,2,3,4,5,6,7,8,9,10) order by rate limit 10

And with objectbox you can do most of it - except for filtering by company_id. And to achieve this, currently it forces someone to duplicate company_id as another Int field just so one can use .oneOf() (and keep these in sync). Which is more of an ugly workaround. And for ToMany, it becomes even more difficult to solve.

[greenrobot-team]

Thanks for clarifying what you actually need. The current way to do this with ObjectBox is then as I hinted at in my previous comment: run multiple queries inside a transaction (for improved performance) and collect/reduce the results as needed.

A primitive example using the entities you have given:

void main() async {
  final store = Store(getObjectBoxModel());
  final topRatedCars = await store
      .runInTransactionAsync(TxMode.read, getTopRatedCarModelsOf, [1, 2, 3]);
  store.close();
}

// Runs in a database transaction
// Note: due to a Dart bug this callback should be a top-level or static function.
// See the runInTransactionAsync docs for details.
List<CarModel> getTopRatedCarModelsOf(Store store, List<int> companyIds) {
  final box = store.box<CarModel>();
  final builder =
      box.query(CarModel_.gear.equals("automatic")).order(CarModel_.rate)
        // Use link() if the entity is owning the relation, or
        // use backlink() if it is not.
        ..backlink(CarCompany_.model, CarCompany_.id.equals(0));
  final query = builder.build();

  final results = List<CarModel>.empty(growable: true);
  for (final companyId in companyIds) {
    query.param(CarCompany_.id).value = companyId;
    final topRatedForCompany = query.find();
    // Process results, e.g. here just adding:
    results.addAll(topRatedForCompany);
  }

  query.close();
  return results;
}

We might look into providing a oneOf/IN condition for relations. However, as it is typically an easy performance trap (e.g. the model or the number of IN values changes leading to much higher resource usage to run such a query) not sure we want to do this.

Edit: for anyone interested having in this, please thumbs up the first post!

[23doors]

Thanks for clarifying!

[gcostaapps]

Hi, I came here after trying to use the .oneOf with a ToOne link, as I noticed that this is not possible and seeing this issue and the #340 I tried to get the ids on the transaction as this:

final tagsIds = await database.store.runInTransactionAsync(
        TxMode.read, getTagsIdsFromNotebooks, notebooksIds);

List<int> getTagsIdsFromNotebooks(Store store, List<int> notebooksIds) {
    final box = store.box<TagDTO>();
    final builder = box.query(TagDTO_.notebook.equals(0));
    final query = builder.build();

    final tagsIds = <int>[];
    for (final notebookId in notebooksIds) {
      query.param(TagDTO_.notebook).value = notebookId;
      final ids = query.findIds();
      tagsIds.addAll(ids);
    }
    query.close();

    return tagsIds;
  }

Where notebooksIds is a list of int and the notebook inside the TagDTO is a ToOne relation. I'm using object box 2.1.0, so I thought it should work. Am I missing something?

PS: If I use just the runTransaction it works

The error t hat I'm getting is this one:

flutter: │ Invalid argument(s): Illegal argument in isolate message: (object is a Pointer)
flutter: │  <- Instance of 'Box<NotebookFolderDTO>' (from package:objectbox/src/native/box.dart)
box.dart:1
flutter: │  <- Instance of 'NotebookFolderLocalDatasource' (from package:revise_mobile/infra/notebook/notebook_folder_local_datasource.dart)
notebook_folder_local_datasource.dart:1
flutter: │  <- Context num_variables: 1
flutter: │  <- Closure: (Store, List<int>) => List<int> from Function 'getTagsIdsFromNotebooks':. (from dart:core)
flutter: │  <- Context num_variables: 2
flutter: │  <- Closure: (Store, List<int>) => FutureOr<List<int>> (from dart:core)
flutter: │  <- Instance of '_RunAsyncIsolateConfig<List<int>, List<int>>' (from package:objectbox/src/native/store.dart)

[greenrobot-team]

@gcostaapps Is the getTagsIdsFromNotebooks function static or top-level? See https://pub.dev/documentation/objectbox/latest/objectbox/Store/runAsync.html for details (pointed to from https://pub.dev/documentation/objectbox/latest/objectbox/Store/runInTransactionAsync.html).

[gcostaapps]

Sorry @greenrobot-team, it wasn't static or top-level, after fixing it it worked. Thanks!

[greenrobot-team]

@gcostaapps Good to hear. I'll hide these comments then as they do not really help other users with this issue.