Queries: order by property of related entity

[DJafari]

is possible to sort parent items by child property ?

for example :

Author

• name
• birthday

Birthday

• date

i want list all authors, but when birthday != null must be sorting first

[greenrobot]

That's not supported yet. You would have to do this by hand at this point.

[smith6k]

Is it planned to allow queries of attributes of the relations to any level?

[DJafari]

do you planned to add this feature ? it's to be good that can sort with nested property, for example birthday.date

[greenrobot]

@smith6k If you are asking about the equivalent of a JOIN: yes this is planned soon-ish.

[sigfridod]

@greenrobot any update on this ?

[greenrobot]

@sigfridod It's a high priority task. Once no more fixes for 1.0 features are required, we'll look into it.

[JU5T1C3]

@greenrobot any updates so far?

[ExCx]

This and #497 are must have features for our application. We are using live data with paging library (ObjectBoxDataSource.Factory) so aggregating results or using filter is not an option.

We are trying to build a single query both doing filtering and sorting related to various child entity properties. If there is any other way to do this, please inform us. If not, we're begging you to implement this, sir.

[pgetsos]

Any updates on this?

[greenrobot-team]

Links are available since 2.0.0 to query based on properties of related entities. https://docs.objectbox.io/queries#add-query-conditions-for-related-entities-links

Not sure if this supports ordering by a property of a related entity though.

[RobbWatershed]

It doesn't seem to be supported, alas ☹️

Just tried sorting with a QueryBuilder<Entity1> using a field of Entity2 as my sorting criterion

queryBuilder.backlink(Entity2_.entity1).order(Entity2_.order);

and got in result

java.lang.IllegalStateException: This call is not supported on sub query builders (links) at io.objectbox.query.QueryBuilder.verifyNotSubQuery(QueryBuilder.java:242) at io.objectbox.query.QueryBuilder.order(QueryBuilder.java:294) at io.objectbox.query.QueryBuilder.order(QueryBuilder.java:260)

NB : Using ObjectBox 2.7.1 here

---

@sigfridod It's a high priority task. Once no more fixes for 1.0 features are required, we'll look into it.

@greenrobot Back to the drawing board, I guess ? 😁

[greenrobot]

Nice try. :smile_cat: If you read closely the context of "high priority task" were query links (aka joins), which shipped a long time ago...

The initial issue is not straight forward to implement, so the one work around I can offer is supply your own Comparator, which looks at the related entity and can be supplied via:

https://objectbox.io/docfiles/java/current/io/objectbox/query/QueryBuilder.html#sort(java.util.Comparator)

[RobbWatershed]

Thanks for the quick answer 😉

Using sort with a Comparator was my plan B, but it doesn't cut it either... because I'm feeding a LiveData<PagedList>, which requires calling Query.find(long offset, long limit). The resulting error is that one :

java.lang.UnsupportedOperationException: Does not work with a sorting comparator. Only find() supports sorting with a comparator.

at io.objectbox.query.Query.ensureNoComparator(Query.java:173) at io.objectbox.query.Query.ensureNoFilterNoComparator(Query.java:161) at io.objectbox.query.Query.find(Query.java:223) at io.objectbox.android.ObjectBoxDataSource.loadRange(ObjectBoxDataSource.java:92) at io.objectbox.android.ObjectBoxDataSource.loadInitial(ObjectBoxDataSource.java:77) at androidx.paging.PositionalDataSource.dispatchLoadInitial(PositionalDataSource.java:286) at androidx.paging.TiledPagedList.<init>(TiledPagedList.java:107) at androidx.paging.PagedList.create(PagedList.java:229) at androidx.paging.PagedList$Builder.build(PagedList.java:388) at androidx.paging.LivePagedListBuilder$1.compute(LivePagedListBuilder.java:206) at androidx.paging.LivePagedListBuilder$1.compute(LivePagedListBuilder.java:171) at androidx.lifecycle.ComputableLiveData$2.run(ComputableLiveData.java:101)

Considering my data model, my only choice right now would be to abandon PagedList, which is very disappointing 🥺

[RobbWatershed]

For those who are interested, here's the workaround I finally came up with. It doesn't implement the ability of LiveData to update datasets as they change, but it works correctly with PagedList, which is an okay tradeoff in my very own case.

---

Goal

Produce a LiveData<PagedList<Content>> ordered by GroupItem.order, where Content has a N..1 relationship with GroupItem.

In ObjectBox terms, the data model is as follows :

• GroupItem entity has public ToOne<Content> content;
• Content entity has @Backlink(to = "content") public ToMany<GroupItem> groupItems;

Vanilla way of implementing (does not work when ordering with GroupItem.order for the reasons stated in this issue)

1. Produce the adequate Query<Content>
2. Get what you need by calling LivePagedListBuilder<>(new ObjectBoxDataSource.Factory<>(Query<Content>), cfg).build()

Workaround

1. Produce a Query<GroupItem>
   • use query.order(GroupItem_.order)
   • use query.link(GroupItem_.content) to apply whatever filter should be applied to Content
2. Fetch all Content ID's with Stream.of(query.build().find()).map(gi -> gi.content.getTargetId()).toList()
3. Use an ObjectBoxPredeterminedDataSource (custom class; see code below) that will produce your LiveData<PagedList<Content>>.

This DataSource is fed with :

• the pre-determined list of ID's produced in step 2
• a fetcher function Function<List<Long>, List<I>> that will be used to fetch your objects in the DB. In my case, the fetcher is a good old store.boxFor(Content.class).get(idList)

My implementation of ObjectBoxPredeterminedDataSource is inspired by ObjectBoxDataSource : https://gist.github.com/RobbWatershed/d6360f797d33e63606f2902b7621bf6c