Firebase, IN clause query and lists of datas

Question

In my opinion this is pretty obvious question when someone has to deal with a DB, so I don't know why I can't find anything about it.
I'm trying to design the non-relational database for my project. I have a list of products described by some info (e.g. the code, an image and a multi language description) and they are categorized in different ways. Here is a snap of the structure:

"products" : {
    "-JiGh_31GA20JabpZBfa" : {
      "code" : "IR3300000000",
      "schema" : "http://lorempixel.com/400/200/",
    },
    "-JiGh_31GA20JabpZBfb" : {
      "code" : "PJ0000000000",
      "schema" : "http://lorempixel.com/400/200/",
    }
  }

"it" : {
    "products" : {
      "-JiGh_31GA20JabpZBfa" : {
        "description" : "desc product IR330000000"
      },
      "-JiGh_31GA20JabpZBfb" : {
        "description" : "description product PJ00000 lorem ipsum"
      }
    }
  }

And below there are a couple of usages of the products, the real structure is much more complex, but probably it's enough to explain my problem:

"families" : {
    "family1" : {
        "products" : {
            "-JiGh_31GA20JabpZBfa" : true,
            "-JiGh_31GA20JabpZBfb" : true
        }
    },
    "family2" : {
        "products" : {
            "-JiGh_31GA20JabpZBfa" : true
        }
    }
}

"applications" : {
    "application_1" : {
        "products" : {
            "-JiGh_31GA20JabpZBfa" : true,
            "-JiGh_31GA20JabpZBfb" : true
        }
    },
    "application_1" : {
        "products" : {
            "-JiGh_31GA20JabpZBfa" : true,
        }
    }
}

Ok, now let's move to the client side!

I have a couple of tables, one that shows the families and one for the applications of the products. Selecting one of them I want to show all products that belong to the family or that are used in an application.

And here is what I'm struggling with: if I want to show the description and the code of each product in a cell, do I have to perform a query for every product to retrieve their fields or can I use some sort of "IN" query? Something like select * from products where key IN {"-JiGh_31GA20JabpZBfa", "-JiGh_31GA20JabpZBfa"}. I know that it's an SQL like query, but this was just to better explain myself.

Btw, I don't exclude that my way of designing the DB is wrong.

You will have to load each item separately. Firebase doesn't have an equivalent to a SQL `WHERE id IN (1,2,3)`, not would there be a benefit to having it. For a full explanation, see http://stackoverflow.com/questions/35931526/speed-up-fetching-posts-for-my-social-network-app-by-using-query-instead-of-obse/35932786#35932786 — Frank van Puffelen, Nov 24 '16 at 16:47
Hi @FrankvanPuffelen, I have taken a look at your answer. Ok, I'll try to go on with this structure and I'll update my question with my code. Btw, I was at the Dev Summit in Berlin, it was very interesting! ;-) — Zeb, Nov 25 '16 at 09:21
Hi again @FrankvanPuffelen, I answered to my own question with your suggestion. Furthermore, I tried to implement a simple project based on that structure. Can you please take a look at that and, if necessary, give me some advice? I would like to start to integrate Firebase in my projects in the best way. — Zeb, Nov 25 '16 at 15:53

score 1 · Answer 1 · answered Nov 25 '16 at 15:49

As indicated in the first comment by @Frank van Puffelen, there is no way to perform a IN query or, I suppose, something similar to a JOIN.
So here are a few pieces of my code and a brief of the pipeline that I have implemented!

I created 2 UITableViewController, the first one to show the families and the second one to show the products of the selected one.

So, in the first controller I wrote this code to retrieve the list of families:

let ref = FIRDatabase.database().reference()
ref.child("families").observeSingleEvent(of: .value, with: { snap in
    if snap.value is NSNull { return }        
    let families = snap.value as! [String:AnyObject]
    for familyKey in families.keys {
        let familyDetails = familyes[familyKey] as! [String:AnyObject]
        self.families.append(FamilyL1.init(familyName: familyKey, familyDetails: familyDetails))
    }
    self.tableView.reloadData()
})

After the selection of a family, in the second controller I'll perform two queries: one to retrieve the details of the product and the other one for the multi-language description.

for (index, product) in (family?.products ?? []).enumerated() {
    self.ref.child("products").child(product.id).observeSingleEvent(of: .value, with: { snap in
        if snap.value is NSNull { return }
        product.populateProduct(product: snap.value as!  [String:AnyObject])
        self.tableView.reloadRows(at: [IndexPath.init(row: index, section: 0)], with: .none)
    })   
    self.ref.child("it").child("products").child(product.id).observeSingleEvent(of: .value, with: { snap in
        if snap.value is NSNull { return }
        product.populateProductDescription(product: snap.value as! [String:AnyObject])
        self.tableView.reloadRows(at: [IndexPath.init(row: index, section: 0)], with: .none)
    })
}

As suggested, I've taken advantage of the parallelism, so the queries aren't performed sequentially and the interested cells will be refreshed at each update. I've taken care of the optional values to achieve that:

override func tableView(_ tableView: UITableView, cellForRowAt indexPath: IndexPath) -> UITableViewCell {
    let cell = tableView.dequeueReusableCell(withIdentifier: "cell", for: indexPath)
    let product = self.family?.products[indexPath.row]
    cell.textLabel?.text = product?.code ?? "..."
    cell.detailTextLabel?.text = product?.pDescription ?? "..."
    return cell
}

I hope this answer will help those who are trying to use this great platform!

Firebase, IN clause query and lists of datas

1 Answers1

Linked