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)
                    }
                }
            }
        }
    }

you can't check substring in firestore, as this is the limitation of firestore, but you can integrate algolia to check substring https://firebase.google.com/docs/firestore/solutions/search — ked, Dec 18 '19 at 14:58
Thanks for your answer, so if I use full string, not substring. what is the best way to query only on the specific user's posts? for loop? or firebase provides other ways more effective than what I did above? @ked — Gal, Dec 18 '19 at 15:07
in that case you'll have to modify your database structure a little, i'll write the solution below in some time, and firestore query i'll be writing in kotlin, i hope that would be enough for understanding the logic — ked, Dec 18 '19 at 15:13
check the solution below, i guessed you are using firestore, but if you are using realtime database you can't do query on realtime database — ked, Dec 18 '19 at 15:27
@ked I may misunderstand your comment but you can certainly query the realtime database. See [Sorting and filtering data](https://firebase.google.com/docs/database/ios/lists-of-data#sorting_and_filtering_data). If you need to query a level deeper, that's done with a deep query. See [Ordering by deep paths](https://firebase.googleblog.com/2015/09/introducing-multi-location-updates-and_86.html) — Jay, Dec 18 '19 at 21:15
While you cannot do a substring search, you can definitely do a partial match starting string search e.g. query for all matches starting with 'mi'. You can also build your own sub string search. It's impractical for long string but if it's single words, see my answer to this question [string search](https://stackoverflow.com/questions/36870425/swift-how-can-i-use-shouldchangetextinrange-with-firebase-for-real-time-sear/36872475#36872475). See my answer [here](https://stackoverflow.com/questions/45132981/querying-firebase-efficiently/45174762#45174762) for usage of \uf8ff — Jay, Dec 18 '19 at 21:42

Jay · Answer 1 · 2019-12-21T15:45:59.340

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.

score 0 · Answer 2 · answered Dec 18 '19 at 15:24

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

Firebase database query by substring in specific ids

2 Answers2