0

Issue

The order of the data returned is not changing using a Firestore multiple attribute composite index sort. This is compared to the original sort performed on one attribute. Currently, the data is returned being only sorted by timestamp with and without the composite index applied.

Expected

The data should sort in terms of the qualityScore attribute when using the composite index of the timestamp and qualityScore.

Implementation

The query results are consumed by the FirestorePagingOptions.Builder<Content>() method setQuery().

val options: FirestorePagingOptions<Content> = FirestorePagingOptions.Builder<Content>()
            .setLifecycleOwner(this)
            .setQuery(viewModel.contentFeedQuery, config, Content::class.java).build()

I'm reading the results from the onBindViewHolder of the FirestorePagingAdapter.

override fun onBindViewHolder(viewHolder: ViewHolder, position: Int, content: Content) {
            println("QUALITY_SCORE: " + content.timestamp + " " + content.qualityScore)
            viewHolder.bind(content)
        }

Original Sort

return FirestoreCollections.contentCollection
.collection(ALL_COLLECTION)
.orderBy(TIMESTAMP, DESCENDING)
.whereGreaterThanOrEqualTo(TIMESTAMP,getTimeframe(WEEK))

Result

2018-08-26 01:15:08.548 27668-27668/app.carpecoin I/System.out: QUALITY_SCORE: Fri Aug 24 15:17:26 PDT 2018 17.0
2018-08-26 01:15:08.574 27668-27668/app.carpecoin I/System.out: QUALITY_SCORE: Fri Aug 24 13:30:44 PDT 2018 17.0
2018-08-26 01:15:11.698 27668-27668/app.carpecoin I/System.out: QUALITY_SCORE: Fri Aug 24 11:16:47 PDT 2018 16.0
2018-08-26 01:15:11.728 27668-27668/app.carpecoin I/System.out: QUALITY_SCORE: Fri Aug 24 08:09:52 PDT 2018 18.0
2018-08-26 01:15:11.777 27668-27668/app.carpecoin I/System.out: QUALITY_SCORE: Fri Aug 24 07:38:36 PDT 2018 21.0
2018-08-26 01:15:11.804 27668-27668/app.carpecoin I/System.out: QUALITY_SCORE: Fri Aug 24 06:16:23 PDT 2018 20.0
2018-08-26 01:15:11.849 27668-27668/app.carpecoin I/System.out: QUALITY_SCORE: Thu Aug 23 15:39:59 PDT 2018 15.0
2018-08-26 01:15:11.890 27668-27668/app.carpecoin I/System.out: QUALITY_SCORE: Thu Aug 23 07:23:51 PDT 2018 1.0
2018-08-26 01:15:11.915 27668-27668/app.carpecoin I/System.out: QUALITY_SCORE: Wed Aug 22 16:15:02 PDT 2018 5.0
2018-08-26 01:15:11.947 27668-27668/app.carpecoin I/System.out: QUALITY_SCORE: Wed Aug 22 08:00:12 PDT 2018 22.0
2018-08-26 01:15:12.000 27668-27668/app.carpecoin I/System.out: QUALITY_SCORE: Tue Aug 21 17:28:03 PDT 2018 19.0
2018-08-26 01:15:12.050 27668-27668/app.carpecoin I/System.out: QUALITY_SCORE: Tue Aug 21 08:59:06 PDT 2018 6.0
2018-08-26 01:15:12.115 27668-27668/app.carpecoin I/System.out: QUALITY_SCORE: Tue Aug 21 08:17:53 PDT 2018 7.0
2018-08-26 01:15:12.167 27668-27668/app.carpecoin I/System.out: QUALITY_SCORE: Mon Aug 20 22:40:56 PDT 2018 9.0
2018-08-26 01:15:12.235 27668-27668/app.carpecoin I/System.out: QUALITY_SCORE: Mon Aug 20 06:58:18 PDT 2018 10.0
2018-08-26 01:15:12.318 27668-27668/app.carpecoin I/System.out: QUALITY_SCORE: Mon Aug 20 04:07:27 PDT 2018 12.0
2018-08-26 01:15:12.367 27668-27668/app.carpecoin I/System.out: QUALITY_SCORE: Sun Aug 19 21:08:31 PDT 2018 8.0
2018-08-26 01:15:12.410 27668-27668/app.carpecoin I/System.out: QUALITY_SCORE: Sun Aug 19 15:11:37 PDT 2018 14.0
2018-08-26 01:15:12.449 27668-27668/app.carpecoin I/System.out: QUALITY_SCORE: Sun Aug 19 03:35:52 PDT 2018 200.0

Composite Index Sort

return FirestoreCollections.contentCollection
.collection(ALL_COLLECTION)
.orderBy(TIMESTAMP, DESCENDING)
.orderBy(QUALITY_SCORE, DESCENDING)
.whereGreaterThanOrEqualTo(TIMESTAMP,getTimeframe(WEEK))

Result

2018-08-26 01:13:54.549 26943-26943/app.carpecoin I/System.out: QUALITY_SCORE: Fri Aug 24 15:17:26 PDT 2018 17.0
2018-08-26 01:13:54.579 26943-26943/app.carpecoin I/System.out: QUALITY_SCORE: Fri Aug 24 13:30:44 PDT 2018 17.0
2018-08-26 01:13:58.110 26943-26943/app.carpecoin I/System.out: QUALITY_SCORE: Fri Aug 24 11:16:47 PDT 2018 16.0
2018-08-26 01:13:58.205 26943-26943/app.carpecoin I/System.out: QUALITY_SCORE: Fri Aug 24 08:09:52 PDT 2018 18.0
2018-08-26 01:13:58.339 26943-26943/app.carpecoin I/System.out: QUALITY_SCORE: Fri Aug 24 07:38:36 PDT 2018 21.0
2018-08-26 01:13:58.420 26943-26943/app.carpecoin I/System.out: QUALITY_SCORE: Fri Aug 24 06:16:23 PDT 2018 20.0
2018-08-26 01:13:58.590 26943-26943/app.carpecoin I/System.out: QUALITY_SCORE: Thu Aug 23 15:39:59 PDT 2018 15.0
2018-08-26 01:13:58.840 26943-26943/app.carpecoin I/System.out: QUALITY_SCORE: Thu Aug 23 07:23:51 PDT 2018 1.0
2018-08-26 01:13:58.940 26943-26943/app.carpecoin I/System.out: QUALITY_SCORE: Wed Aug 22 16:15:02 PDT 2018 5.0
2018-08-26 01:13:59.041 26943-26943/app.carpecoin I/System.out: QUALITY_SCORE: Wed Aug 22 08:00:12 PDT 2018 22.0
2018-08-26 01:13:59.183 26943-26943/app.carpecoin I/System.out: QUALITY_SCORE: Tue Aug 21 17:28:03 PDT 2018 19.0
2018-08-26 01:13:59.360 26943-26943/app.carpecoin I/System.out: QUALITY_SCORE: Tue Aug 21 08:59:06 PDT 2018 6.0
2018-08-26 01:13:59.427 26943-26943/app.carpecoin I/System.out: QUALITY_SCORE: Tue Aug 21 08:17:53 PDT 2018 7.0
2018-08-26 01:13:59.467 26943-26943/app.carpecoin I/System.out: QUALITY_SCORE: Mon Aug 20 22:40:56 PDT 2018 9.0
2018-08-26 01:13:59.517 26943-26943/app.carpecoin I/System.out: QUALITY_SCORE: Mon Aug 20 06:58:18 PDT 2018 10.0
2018-08-26 01:13:59.567 26943-26943/app.carpecoin I/System.out: QUALITY_SCORE: Mon Aug 20 04:07:27 PDT 2018 12.0
2018-08-26 01:13:59.633 26943-26943/app.carpecoin I/System.out: QUALITY_SCORE: Sun Aug 19 21:08:31 PDT 2018 8.0
2018-08-26 01:13:59.703 26943-26943/app.carpecoin I/System.out: QUALITY_SCORE: Sun Aug 19 15:11:37 PDT 2018 14.0
2018-08-26 01:13:59.769 26943-26943/app.carpecoin I/System.out: QUALITY_SCORE: Sun Aug 19 03:35:52 PDT 2018 200.0

Composite Index Setup

I setup 2 composite indexes as I was testing various combinations in order to achieve a result of the data being returned sorted by qualityScore after a specific timestamp. enter image description here

AdamHurwitz
  • 9,758
  • 10
  • 72
  • 134

1 Answers1

1

You call .orderBy(TIMESTAMP, DESCENDING).orderBy(QUALITY_SCORE, DESCENDING). This means that the documents are first ordered by descending timestamp, and when those are the same, they are ordered by descending quality score.

Since all documents in the result set have a unique timestamp, that is the only visible result. Only when multiple documents have the same value for the first sort field, does the second sort field become relevant.

Frank van Puffelen
  • 565,676
  • 79
  • 828
  • 807
  • Thanks @Frank van Puffelen. My goal is to sort by `QUALITY_SCORE` filtering out everything past a specified `TIMESTAMP`. Is there a way I can order the query to accomplish this? If not, I can first query by `TIMESTAMP`, then sort by `qualityScore` on the client side. However, I would need to transform the final result of the sorted collection such as a List or Array back into a **Query** to pass into `FirestorePagingOptions.Builder`'s `setQuery()` method. Any ideas on how to transform a collection into a query if part 1 is not possible? – AdamHurwitz Aug 27 '18 at 19:35
  • 1
    Isn't that `return FirestoreCollections.contentCollection .collection(ALL_COLLECTION) .orderBy(QUALITY_SCORE, DESCENDING) .whereGreaterThanOrEqualTo(TIMESTAMP,getTimeframe(WEEK))`, so without the ordering on timestamp? – Frank van Puffelen Aug 27 '18 at 20:14
  • Unfortunately that query cannot be made. The error received states: **Invalid query. You have an inequality where filter (whereLessThan(), whereGreaterThan(), etc.) on field 'timestamp' and so you must also have 'timestamp' as your first orderBy() field, but your first orderBy() is currently on field 'qualityScore' instead.** – AdamHurwitz Aug 27 '18 at 20:52
  • As a result I attempted `.collection(ALL_COLLECTION) .orderBy(QUALITY_SCORE, DESCENDING) .orderBy(TIMESTAMP, DESCENDING) .whereGreaterThanOrEqualTo(TIMESTAMP,getTimeframe(WEEK))` however that error states `TIMESTAMP` must be the first `orderBy()`. When I correct for that and make the `TIMESTAMP` the first `orderBy()` that brings me back to the original issue above. – AdamHurwitz Aug 27 '18 at 20:57
  • 1
    Ah... in that case indeed it seems like it isn't possible to change the order. As usual this would be a performance consideration: since timestamp is your main criteria for filtering, doing that one first is (apparently) required to be able to guarantee performance. You'll have to reorder client-side. – Frank van Puffelen Aug 27 '18 at 20:58
  • Reordering client side is straightforward. However, how would one go about passing the data after the client side reordering into the `FirestorePagingOptions.Builder` as it's `setQuery()` method only accepts a **Query** as a parameter. – AdamHurwitz Aug 27 '18 at 21:01
  • 1
    You will have to create your own adapter for that. Mostly attach an observer, stuff the docs into a `List`, and pass that to an `ArrayAdapter` I'd think. – Frank van Puffelen Aug 27 '18 at 21:27
  • Awesome, I'll build out a PagedListAdapter and create a custom DataSource for the Firebase queries. https://developer.android.com/topic/libraries/architecture/paging/data#custom-data-source – AdamHurwitz Aug 27 '18 at 21:32
  • I added an answer if you'd like to see how I solved it. – AdamHurwitz Aug 27 '18 at 23:49
  • For anyone seeking the solution, check out my answer here as it was removed as a duplicate on this post: https://stackoverflow.com/questions/51859652/how-to-exclude-an-element-from-a-firestore-query/51973447?noredirect=1#comment91057976_51973447 – AdamHurwitz Aug 28 '18 at 17:17