Support for multiple `OrderBy` queries.

[BirdInTheCity]

I would like to see the ability to chain multiple OrderBy queries to filter $firebaseArray.
An example is fleshed out here: http://stackoverflow.com/questions/26700924/query-based-on-multiple-where-clauses-in-firebase

Is this anywhere on the horizon for Firebase? My application sorely needs it.

[jamestalmage]

As discussed in the link you referenced:

multiple orderBy() calls will throw an error, because the clients currently give unexpected results

This is a Firebase core issue, and has little to do with AngularFire.

Depending on your dataset, and what you are trying to do, this can be accomplished.

1. If there is NOT a lot of data - pull everything and just sort on the client

   See angular's orderBy filter.

2. If there IS a lot of data - create synthetic indexes yourself

   On the client side, when you would normally do:

   ref.set({
     firstName: 'John',
     lastName: 'Doe',
     age: 32
   });

   ref.set({
     firstName: 'John',
     lastName: 'Doe',
     age: 32,
     firstLastAge: 'john~doe~032',
     lastFirstAge: 'doe~john~032',
     lastAgeFirst: 'doe~032~john',
     // and whatever other ordering you want to index on
   });

   Note that I have done a couple things with the generated index values:

   A. I've used ~ character as a separator, it is basically the last ASCII character. The goal here is that john~doe will still come after johny~appleseed. If you are allowing users to store characters that are a superset of ASCII you will need to pick a different separator that is lexographically after whatever the highest allowed character is.

   B. I've lowercased everything in the indexes. Firebase process orderBy queries lexicographically, this leads to some unexpected results for the uninitiated:

   Original Dataset: [ C, d, A, b ]
   Alphabetically Ordered: [ A, b, C, d ]
   Lexicographically Ordered: [A C b d]
   

   This has everything to do with the byte value of characters (an alphabetical sorting algorithm would be significantly more expensive).

   C. I've padded leading 0's on the age. This allows users to be up to 999 years old. For bigger numbers you need more padding digits.

   As you can already tell, this quickly gets unmanageable if you need to be able to sort LOTS of different ways. You are also creating a bandwidth and storage penalty by creating those, so that needs to be weighed against the bandwidth penalty of option 1 above. This is similar to the exploding index problems you (used to?) run into on an AppEngine datastore. There are good talks on that topic that you should google if you are seriously considering this.

Other Thoughts

1. You will want to write security rules to enforce correct indexes.
2. Only store the indexes you know you need, you can always add future indexes using the REST api.
3. As discussed, this quickly becomes unrealistic. If you want to give users a table with many thousands of entries, and a way to sort on any one of a dozen columns, Firebase may not be the tool you seek.

[jwngr]

Great answer @jamestalmage! I think he hit most of the key points. Also see here and here for some Google Groups posts on this topic.

We'll note this feature request as we plan to bring richer querying to the base client SDKs.

[jamestalmage]

@jwngr

I was thinking, it would be nice if there was a way to label properties as "synthetic indexes", perhaps with a special prefix character? I'll use #:

ref.set({
  firstName: 'John',
  lastName: 'Doe',
  age: 32,
  '#firstLastAge': 'john~doe~032',
  '#lastFirstAge': 'doe~john~032',
  '#lastAgeFirst': 'doe~032~john',
  // and whatever other ordering you want to index on
});

The idea would be that, while they are saved as searchable indexes, they are not serialized and sent to the client.

Combine that with something similar to objectify LifecycleCallbacks, allowing synthetic indexes to be recreated/updated easily on the client side. For Firebase, instead of adding annotations to a specific class, you would use parameterized URL's

Firebase.addOnSaveProcessor('http://my-firebase-url/users/$username/', function(extendableSnap, done) {
  extendableSnap.child('#firstLast').set(extendableSnap.get('first').val() + '~' + extendableSnap.get('last'));
});

extenableSnap is a snap that can be extended with (only?) synthetic indexes.

You could have a optional onLoadProcessor that would reinflate the synthetic indexes if they needed to be used client side (in an angular orderBy filter for example).

[jwngr]

@mikelehen - Any thoughts on James' comments above?

[jamestalmage]

Even if you implement support for indexing on multiple properties, this idea still has value for other situations where the raw values do not naturally produce the ordering desired. (Zigzag indexes and geocoding for instance).

[mikelehen]

@jamestalmage Thanks for the suggestion! Honestly, I'm more inclined to work on first-class support for composite indexes though (so you don't have to do it manually). :-)

Another related feature people have asked for is "projection queries" where you can sync a "projection" of the data (concretely, you could query your data but say "only give me firstName, lastName, and age.") which I think could be used to accomplish a similar effect to what you want.

[jamestalmage]

@mikelehen @jwngr
Even with first-class support for composite indexes, there is still value to the idea. Composite indexes likely would not support any of the following:

1. Lat/Lng "nearby" sorting (synthetic index using geocoding hash).
2. Alphabetical sorting (synthetic index using toLowerCase).
3. query E-mail domain (synthetic index using RegExp to extract domain).

I should not have to think about denormalizing my data at the point where I am calling ref.set. The hooks I have described assist in separation of concerns. There are, of course, other solutions (a DAO or some other facade).

Far more importantly, I would prefer not to pay a performance penalty as users download synthetic indexes I could just as easily reconstitute client side.

[mikelehen]

Thanks @jamestalmage.

Just to clarify, would there be anything special about these "synthetic indexes" other than that the Firebase Server won't include them when clients download data? So one implementation would be to just not sync any data prefixed with '#' to the client or similar?

[jamestalmage]

one implementation would be to just not sync any data prefixed with '#' to the client

That would deliver on the most important part of my proposal. Put that way, it seems like an easy win, and I would be happy if that was the only part that got implemented.

The rest of my proposal is just convenience methods around that. Specifically a way to "hook" into reads/writes globally, so you can set/reconstitute those synthetic indexes in one place. I was thinking something along the lines of parametrized urls for specifying hook "locations". I am sure there are corner cases I have not completely thought through here.

[mikelehen]

Cool, thanks for the added detail.

[jamestalmage]

@mikelehen
A few more thoughts.
It probably would not make sense for synthetic indexes to trigger child_changed events on the parent node, nor should they trigger value events (though if people are using synthetic indexes correctly, they should only change when non synthetic siblings are changing anyways). Synthetic indexes should, however, trigger child_moved/added/removed as they move nodes around inside ordered queries.

[ZaidPathan]

@BirdInTheCity Have you found any good working solution for this?

[BirdInTheCity]

@ZaidPathan

Short answer: no.

Longer answer: I've since learned that Firebase uses a different paradigm than I'm used to, and thus I needed to rethink my data structure to allow for the sorting my app required. The info that @jamestalmage posted above is good advice. I'll add that it will be helpful to read the various blog posts about efficient Firebase data structuring. That, and keeping in mind that Firebase isn't ideally suited for all database needs, as the available filters are limited.