Advanced multiple search query in Firebase

Question

This is my Firebase database inside "/articles", which has loads of articles inside. A user can (using his/her own article), list other articles that correspond to certain conditions. In order for a article to pass the query test, it has to be of category that the user's article has listed inside "tradableCategories", while also THAT article needs to have the user's article's category within its "tradableCategories".

Here’s the database structure:

"articles": {
  "article1": {
    "title": "Car",
    "category": "vehicles",
    "owner": "user1",
    "tradableCategories": {
      "furnishings": true,
      "other": true,
      "vehicles": true
    },
    "category_tradableCategories": {
      "vehicles_furnishings": true,
      "vehicles_other": true,
      "vehicles_vehicles": true
    }
  },
  "article2": {
    "title": "Bike",
    "category": "vehicles",
    "owner": "user2",
    "tradableCategories": {
      "furnishings": true,
      "other": true
      "vehicles": true,
    },
    "category_tradableCategories": {
      "vehicles_furnishings": true,
      "vehicles_other": true,
      "vehicles_vehicles": true
    }
  },
  "article2": {
    "title": "Couch",
    "category": "furnishings",
    "owner": "user2",
    "tradableCategories": {
      "furnishings": true,
      "other": true,
      "vehicles": true
    },
    "category_tradableCategories": {
      "furnishings_furnishings": true,
      "furnishings_other": true,
      "furnishings_vehicles": true
    }
  },
  ...
}

user1 owns article1, which wants to find articles that are within furnishings, other and vehicles. Those articles that match the conditions also have to look for article1’s set category. The query can be done easily using SQL:

SELECT *
FROM articles
WHERE category = ’vehicles’ /* This is article1’s category */
  AND find_in_set(category, :tradableCategories) /* :tradableCategories is a stringified, comma-separated set of article1’s tradableCategories: “furnishings,other,vehicles” */
  AND NOT owner = ‘user1’

As you’ve seen in the database structure. I have included another object called “category_tradableCategories”. I’ve seen various answers here on Stack Overflow that explain how to search for items using two conditions combined into one. This could’ve worked but means that I have to initiate 3 Firebase queries since I cannot combine three (or more) different categories within tradableCategories.

I am afraid this is too complicated for Firebase, but if there is any efficient solution to this I’d like some help. Thank you!

score 2 · Accepted Answer · edited May 23 '17 at 10:30

In relational databases you often first define your data model to match with the data you want to store and then write queries for the use-cases of your app. In NoSQL databases you typically use the inverse logic: you make a list of your app's use-cases and then define your data model to match those.

If Firebase's API doesn't directly support the query you want to build, you'll typically have to change/augment your data model to allow that query. This will lead to storing more data and more complex updates, but the advantage is that you have faster and simpler read operations.

So in your scenario: you want a list of articles in one of three categories that is not owned by the current user. The most direct mapping of that requirement would be to literally store that list:

user_articles
  $uid
    categories_1_2_3
      articlekey1: true
      articlekey2: true

This would make the query trivial: ref.child("user_articles").child(currentUser.uid).child(categories).on("child_added"....

Now this may be taking the denormalization and duplication a bit too far. We'd need a separate list for each user/category combination. So an article in 3 categories with 10 users would end up in 60 lists.

More likely you'll want to keep these articles-per-categories in a single list across all users. For example:

articles_by_category_with_owner
  category_1
    articlekey1: uid1
    articlekey2: uid2
    articlekey3: uid1
  category_2
    articlekey1: uid1
    articlekey2: uid2
  category_3
    articlekey1: uid1
    articlekey3: uid1

Now you can get all article keys with category_1 with ref.child("articles_by_category_with_owner").child(category).on("child_added"... and then do the "not owned by the current user" filtering client-side.

In the above list I've also removed the multiple-categories. That does mean that you'll need to read a node for each category. But this is actually not as slow as you may expect, since Firebase pipelines these requests (see link below).

Further recommended reading/viewing:

Thank you very much for the detailed answer! I have a question regarding your second example. This only lists available articles inside each category. First I would have to query this, and then I would have to query EACH article for its actual object (title, description etc). This might end up with 100 queries per feed request inside the app. Is that good? I want as little queries as possible to minimize costs. But this example made it even more complex (backend-wise) than I currently have. — Nordling Art, Dec 19 '16 at 09:54
Please read the documentation I linked, as it explains that this is a common approach in NoSQL databases (and Firebase in particular). For additional reading after all that, see my answer on why these client-side joins are not as slow as you may think: 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, Dec 19 '16 at 15:22
Just read your answer there. Will read the others as well. Thank you for the help! I'll mark this as the answer to this question. — Nordling Art, Dec 19 '16 at 21:59

Advanced multiple search query in Firebase

1 Answers1