Firebase Realtime Database orderByChild data structure

I'm having issues querying data with orderByChild. I would like to query all tasks where provided userID matches any approver id. Task ID is autogenerated so I need to flatten this structure somehow. Each task has two approvers, and each approver has status, timestamp, id.

Any idea on how to flatten it so I can take advantage of orderByChild?

Tasks:{
  "df234dgkjsf234" : {
   "approvers" : {
      "4U1D23dfsdf23e" : {
        "id" : "4U1DWf95rJvgfAwDYs7m",
        "status" : "pending",
        "timestamp" : "pending"
      },
      "sdf32fdsf34sdg3" : {
        "id" : "FXRkK22TjyxKV6z4UkrU",
        "status" : "pending",
        "timestamp" : "pending"
      }
    },
    "reason" : "test",
    "requester" : "4U1DWf95rJvgfAwDYs7m",
    "requesterName" : "Dana Mayers",
    "status" : "pending",
    "tagetValue" : "QT1IkGHS3mmalyXqdCuD",
    "taskName" : "Title Change",
    "timestamp" : 1644773238,
    "value" : "New Title"
  },
"d4S34FSAdsf43FM" : {
   ...
  }
}

I want to make ideally one query to get this data as opposed to querying by Approver1 and then by Approver2

Tasks:{
  "df234dgkjsf234" : {
    "Approver1" : "4U1DWf95rJvgfAwDYs7m",
    "Approver1status" : "pending",
    "Approver1timestamp" : "pending"
    "Approver2" : "FXRkK22TjyxKV6z4UkrU",
    "Approver2status" : "pending",
    "Approver2timestamp" : "pending",
    "reason" : "test",
    "requester" : "4U1DWf95rJvgfAwDYs7m",
    "requesterName" : "Dana Mayers",
    "status" : "pending",
    "tagetValue" : "QT1IkGHS3mmalyXqdCuD",
    "taskName" : "Title Change",
    "timestamp" : 1644773238,
    "value" : "New Title"
  },
"d4S34FSAdsf43FM" : {
   ...
  }
}

CodePudding user response：

There is no way to do this with one query, since you're looking for two separate values. The closest you can get is by doing two separate queries on (4Approver1 and Approver2 in) the second data structure, and then merging the results in your application code.

The alternative is to add a secondary data structure, where you map from the user ID back to the tasks they're allowed to approve:

UserTasks: {
  "4U1D23dfsdf23e": {
    "df234dgkjsf234": true,
    "d4S34FSAdsf43FM": true
  },
  "sdf32fdsf34sdg3": {
    "df234dgkjsf234": true
  }
}

With such an additional structure you can easily find the tasks for a specific UID, and then load the task details for each.

For more on this, I recommend also reading: