Firebase database query by substring in specific ids

Question

I have database structure look like this:

posts:
   pid1:
      timestamp: 111
      pod:
         title: "eggs"
   pid2:
      timestamp: 222
      pod:
         title: "milk"
   pid3:
      timestamp: 333
      pod:
         title: "bread"


users_posts:
   uid1:
      pid1: true
      pid2: true

I want to get all the uid1's posts where title contains a specific substring.

For example:

input: substring: "gs", userId: uid1

output: pid1

What is the best way to do that?

This is what I do so far:

func queryPosts(withSearchText searchText: String, userId: String, completion: @escaping (Post) -> Void) {
        REF_USER_POSTS.child(userId).observeSingleEvent(of: .value) { (snapshot) in
            let ids = (snapshot.value as! [String: Any]).keys
            for id in ids {
                REF_POSTS.child(id).observeSingleEvent(of: .value) { (snapshot) in
                    if let dict = snapshot.value as? [String: Any],
                        let title = ((dict["pod"] as? [String: Any])?["title"] as? String)?,
                        title.contains(searchText) {
                        print("found result", id)
                    }
                }
            }
        }
    }

Answer 1

You can easily query for child nodes using a deep query.

So for example, lets say you wanted to query for all nodes where title = "milk"

func performDeepQuery() {
    let postsRef = self.ref.child("more_posts")
    let query = postsRef.queryOrdered(byChild: "pod/title").queryEqual(toValue: "milk")
    query.observeSingleEvent(of: .value, with: { snapshot in
        let allChildren = snapshot.children.allObjects as! [DataSnapshot]
        for snap in allChildren {
            let postId = snap.key
            let timestamp = snap.childSnapshot(forPath: "timestamp").value as? String ?? "No Timestamp"
            print(postId, timestamp)
        }
    })
}

and the output is

pid2 222

That being said, this won't query for a specific users posts. This will query all posts. However, you can easily fix that by adding another node to make the nodes user specific

   pid2:
      timestamp: 222
      pod:
         title: "milk"
         uid_title: "uid1_milk"

and the query would then be

let query = postsRef.queryOrdered(byChild: "pod/title").queryEqual(toValue: "uid1_milk")

this is a composite value and is quite common in NoSQL databases.

If you want to expand of that, flip the value around so you can link multiple users to a single post

   pid2:
      timestamp: 222
      pod:
         title: "milk"
         uid1_milk: true
         uid2_milk: true

and then you can query for any user that has milk.

let query = postsRef.queryOrdered(byChild: "pod/uid1_milk").queryEqual(toValue: true)

That may not work for your use case but it's a pretty cool option and allows for future growth.

Answer 2

As discussed in comments above, you'll have to modify the database structure as follows

posts:
   pid1:
      timestamp: 111
      createdBy: "uid1"
      title: "eggs"
   pid2:
      timestamp: 222
      createdBy: "uid1"
      title: "milk"
   pid3:
      timestamp: 333
      createdBy: "uid2"
      title: "bread"


users_posts:
   uid1:
      pid1: true
      pid2: true

for fetching all posts by particular user and matching the string use following query

FirebaseFirestore.getInstance().collection("posts")
            .whereEqualTo("createdBy","uid1")
            .whereEqualTo("title","eggs")
            .get()
            .addOnCompleteListener {
                //all posts written by user and matching the title eggs
            }
            .addOnFailureListener { 
                it.printStackTrace()
            }

on running this query initially it'll give error in logcat saying generate composite index check the logcat and click on the link to generate composite index, after the index is created it'll work