Firebase query: exclude data where a specific field is null

Question

I have a query that fetches data between 2 dates, using startAt (1 week ago) and endAt (now) on the last_visit fields.

Then I loop through the results to discard users who don’t have a profile picture.

Problem is around 20% of the users have a profile picture, so just to get 100 users with profile pictures, I have to query at least 500 people (I use limitToLast(500)).

Could I make this query more efficient, by somehow specifying something like in SQL: WHERE profile_picture IS NOT NULL?

If possible, I could also use only limitToLast(100) if it was possible to only take the users that do have a profile picture set.

Database looks like:

users: {
  {user_uid}: {
     profile_picture: null,
     last_visit: 123456789
  }
  {user_uid}: {
     profile_picture: 'example.com/pic.png',
     last_visit: 123456789
  }
}

`ref.orderByChild('profile_picture').startAt('a').endAt('z~')`? — Frank van Puffelen, Aug 22 '16 at 18:37

score 9 · Accepted Answer · answered Aug 25 '16 at 14:57

9

If you're trying to exclude items that don't have a property, you need to query for the broadest range of values possible.

A simple example would be:

ref.orderByChild('profile_picture').startAt('!').endAt('~')‌

This will capture most keys that consist of ASCII characters, since ! is the first printable character (#33) and ~ is the last printable character (#126). Be careful with these, because they won't work when your keys consist of unicode characters.

answered Aug 25 '16 at 14:57

Frank van Puffelen

565,676
79
828
807

I'm getting the following error: `firebase.ref('users').orderByChild('instagram').startAt('!')‌.endAt('~').once('value') SyntaxError: Invalid or unexpected token at createScript (vm.js:80:10) at Object.runInThisContext (vm.js:152:10) at Module._compile (module.js:624:28) at Object.Module._extensions..js (module.js:671:10) at Module.load (module.js:573:32) at tryModuleLoad (module.js:513:12) at Function.Module._load (module.js:505:3) at Function.Module.runMain (module.js:701:10) at startup (bootstrap_node.js:194:16) at bootstrap_node.js:618:3` – Deepak Goyal Jan 09 '19 at 22:32
It's impossible to say why you're getting that without seeing a [minimal complete verifiable example](http://stackoverflow.com/help/mcve). I highly suggest opening a new question for this. – Frank van Puffelen Jan 09 '19 at 23:51
Is database.push function always create ASCII key ? @Frank van Puffelen – tabebqena Sep 21 '19 at 22:55
The keys `push()` generates will indeed always be ASCII. But if you want to also search non-ASCII characters, be sure to something more suited than `~`. The most common value is `\uf8ff`, which is the highest known unicode codepoint. See for example https://stackoverflow.com/a/38562161 – Frank van Puffelen Sep 22 '19 at 06:23

Firebase query: exclude data where a specific field is null

1 Answers1