In Firebase how does one do a WHERE query with separate ORDER BY

Question

Using firebase and trying to do a query which in SQL would be:

`SELECT * FROM definitions WHERE author='joe' ORDER BY votes DESC`

However in order to do equalTo in firebase, you must specific field that you are ordering as well. Both must be the same. Is there truly no way to accomplish this?

That's currently not possible. See http://stackoverflow.com/questions/27432030/how-to-do-the-following-query-in-firebase-more-than-one-where-condition or http://stackoverflow.com/questions/26700924/query-based-on-multiple-where-clauses-in-firebase/26701282#26701282 or http://stackoverflow.com/questions/30151012/is-there-a-way-in-angularfire-to-query-for-matching-and-condition/30153736#30153736 — Frank van Puffelen, Nov 07 '15 at 01:46

score 1 · Accepted Answer · answered Nov 07 '15 at 15:47

1

This can be done - kinda.

Here's a sample set of data

definitions
  -K2XaThTbq3iRnevjM4V
    author: "joe"
    votes: "5"
    sort: "joe_005"
  -K2XaThTbq3iRnevjM4W
    author:  "jay"
    votes: "100"
    sort: "jay_100"
  -K2XaThTbq3iRnevjM4X
    author: "joe"
    votes: "22"
    sort: "joe_022"

ObjC

Firebase *ref = [self.myRootRef childByAppendingPath:@"definitions"];

FQuery *q1 = [ref queryOrderedByChild:@"sort"];
FQuery *q2 = [q1 queryStartingAtValue:@"joe_"];

[q2 observeEventType:FEventTypeChildAdded withBlock:^(FDataSnapshot *snapshot) {

   NSLog(@"%@", snapshot.value);

}];

This will return all the children with joe as the author, ordered by number of votes.

Keep in mind that the sorting is sorting a string value, so joe_5 would come after joe_22 (2 is less than 5). So to compensate, pad the votes with 0's so the numeric part of the string is the same length.

This fails if the are 1000 votes, so just pad it with enough 0's to compensate.

answered Nov 07 '15 at 15:47

Jay

34,438
18
52
81

The problem here is that you can't easily increment the sort field. You'd have to read it, parse it, and then update it. – Nick ONeill Nov 08 '15 at 22:17
The original question didn't mention incrementing your data, but it doesn't matter since you have to query for the data either way. For both my suggestion as well as the SQL Query, the returned data would include author and votes. No parsing is needed: take votes and increment it and then concatenate that with the name. name + newVoteCount and write it to Firebase; it's probably 8-10 lines of code. – Jay Nov 09 '15 at 15:19
Incrementing a number outside of the database is inherently a violation of DB best practices. If two people vote at the same time the system fails – Nick ONeill Nov 10 '15 at 16:09
Adding a simple locked = true child within the node before updating the value will take care of that. And the cool thing there is that other clients will be automatically notified if the node in question is 'locked' and won't try to update until it's not locked. You can also leverage Firebase transactions. Rules can also be used to avoid that issue. However, record locking and incrementing goes beyond the scope of the original question. Please start a new question on those topics as there are a number of good solutions. – Jay Nov 10 '15 at 18:32

In Firebase how does one do a WHERE query with separate ORDER BY

1 Answers1