Ordering and filtering denormalized data with Firebase

Question

I'm using Firebase to build an application that has two models, A and B:

• There is a one to many relationship between A and B (A has many B).
• A and B need to be somewhere easy to query, so I need a way to fetch all A models that have a property that fulfills a condition. Same with B.
• B has a property createdAt (that never changes) and a property status (that changes every now and then) and I want to list all Bs that are owned by a particular A and which status and/or createdAt properties fulfill some condition.
• Bs are private and can only be read by a user that is authenticated and can read the A to which they belong (User can read b1 if can read a1 and a1 belongs to b1).

Following Firebase guidelines, my first try was to do something like this:

"As": {
  "a1": {
    ...
    Bs: {
      "b1": true,
      ...
    },
  },
  ...
},
"Bs": {
  "b1": {
    ...,
    "status": "OPEN",
    "createdAt": 1464249410579,
  },
  ...
},

The problem with this solution is that to access Bs that belong to a1 which status is OPEN and are not older than one month, I have to access a1.Bs, get all the id's and then access Bs one by one (which is fine, according to Firebase guidelines) and then filter them to find out if they fulfill my condition, which I find extremely inefficient.

Any suggestion on how to do this?

Answer 1

After thinking a bit, we came up with this solution:

"As": {
  "a1": {
    ...
    Bs: {
      "b1": { "status": "OPEN", "createdAt": 1464249410579 },
      ...
    },
  },
  ...
},
"Bs": {
  "b1": {
    ...,
    "status": "OPEN",
    "createdAt": 1464249410579,
  },
  ...
},

This solution allows us to keep the structure of the database flat, so that data As and Bs are searchable and listable by, for example, an admin user. It also tries to minimize data duplication, and it would work for as many properties as we would like to include in the query.

To fetch Bs that belong to A:

ref.child('as/:aId/bs').once('value')
  .then(snapshot => snapshot.forEach(...) // Access /bs/:bId where :bId = snapshotItem.key()

To fetch Bs that belong to A and status is OPEN:

ref.child('as/:aId/bs')
  .orderByChild('status')
  .equalTo('OPEN')  
  .once('value')
  .then(snapshot => snapshot.forEach(...) // Access /bs/:bId where :bId = snapshotItem.key()

To fetch Bs that belong to A and status is OPEN and createdAt fulfills condition:

ref.child('as/:aId/bs')
  .orderByChild('status')
  .equalTo('OPEN')  
  .once('value')
  .then(filterBsThatDontFulFillCreatedAtCondition)
  .then(snapshot => snapshot.forEach(...) // Access /bs/:bId where :bId = snapshotItem.key() 

In this last case we have to do some filtering in the client, but this is due to Firebase limitations, and I don't think we could avoid it. In any case, this is done on an array of id's, and not on the model itself, which might have 20 or 30 properties, so we are avoiding to bring the full models that we won't need.

Answer 2

I'll take a crack at this.

As
  a1
    Bs
      b1: true
      b2: true
  a2
    Bs
      b2: true
Bs
  b1:
    "open_a1": 1464249410579
  b2:
    "open_a1": 1464249410590
    "open_a2": 1464249410595

This structure allows your code to know, from the As node all of the Bs linked to a1

You can query Bs for all open_a1 < one month

ref.queryByChild("open_a1").queryStaringAtValue(today)
                           .queryEndingAtValue(one month from today)

This is totally untested so I may need to modify the solution a tad.

Answer 3

I would do something like that :

 "As": {
  "a1": {
    ...
  },
  ...
},
"Bs": {
  "a1" : {
      "b1": {
        "status": "OPEN",
        "createdAt": 1464249410579,
      },
  },
},

Where a1 is the key of you A

This way you can query easily all the bs that belongs to a a

This is what I advised here