CODEWITHSUNDEEP
javascriptfirebasegoogle-cloud-firestore
uksz
uksz

Reputation: 18699

Order by with where inequality with limit on Firestore

Assuming that I have got the following simple query with where and orderBy and limit, that will return me public profile (hence where), that has been recently updated (hence orderBy).

db.collection('profile')
   .where('public_profile', '==', true)
   .orderBy('updated_at', 'desc')
   .limit(1)
   .onSnapshot(function(doc) {
        ...
   });

Now, I want to add some additional logic, meaning I want to only display profiles who have are below some kind of level. When I do the following:

db.collection('profile')
   .where('public_profile', '==', true)
   .orderBy('updated_at', 'desc')
   .where('level', '<=', 5)
   .limit(1)
   .onSnapshot(function(doc) {
        ...
   });

I get the error of:

Invalid query. You have a where filter with an inequality (lessThan, lessThanOrEqual, greaterThan, or greaterThanOrEqual) on field 'talent.level' and so you must also use 'level' as your first queryOrderedBy field, but your first queryOrderedBy is currently on field 'updated_at' instead.

As I understand, I need to add additional orderBy, giving me the following:

db.collection('profile')
   .where('public_profile', '==', true)
   .where('level', '<=', 5)
   .orderBy('level', 'desc')
   .orderBy('updated_at', 'desc')
   .limit(1)
   .onSnapshot(function(doc) {
        ...
   });

However, this yields me with the wrong result: it doesn't return the most recently updated public profile, whose level is lower or equal to 5. It returns me the the the most recently updated public profile, of the profile with the highest level.

Has any of you found a similar issue? If yes, what was your solution?

Upvotes: 7

Views: 1175

Answers (1)

Kerlos Bekhit
Kerlos Bekhit

Reputation: 128

As the documentations states in the limitation page (click) if you use a range comparison (<, <=, >, >=) the first order by that you use should be the same field filtered.

After fixing the order it will probably prompt you to create ad index of that collection in order to use those conditions.

Tip: On firestore the where condition should be at the top, and order by should be at the bottom. (it's useful to better understand the condition orders)

Upvotes: 2

Related Questions