Query subcollection document existence in firestore

Question

I currently have a firestore schema that looks like so:

Threads (threadid)
    Users (userid)
        admin
    title

What I would like to do is perform a query that returns all the thread documents where the subcollection document of a user id exists. I have seen collection groups however I am confused as to how to use that to query the existence of a document rather than a property of a document. I also have a root collection named "Users" as well as a subcollection and it is unclear to me how to differentiate between them.

score 0 · Answer 1 · answered Oct 12 '22 at 14:26

0

I also have a root collection named "Users" as well as the subcollection and it is unclear to me how to differentiate between them.

You can use the trick that Sam showed here to search only the Users collections under Threads: CollectionGroupQuery but limit search to subcollections under a particular document

Since you can't query for the existence of the a document, you'll have to read all documents in this collection group and then check if you get any results.

answered Oct 12 '22 at 14:26

Frank van Puffelen

565,676
79
828
807

Unfortunately I don't quite understand how to go from that solution to the one I require, since the solution you linked to knows the city id (root document id). In my case I don't know the thread ID, I am trying to get a list of all threads the user is a part of by querying for the existence of a subcollection document ID. Could you elaborate on how to marry these two concepts? – user293895 Oct 12 '22 at 14:36
The link does a prefix query. While the answer there indeed prefixes the root collection and document, you can do the exact same with just the collection name. Give it a try. – Frank van Puffelen Oct 12 '22 at 15:57
I think what I'm not understanding here is the prefix is the collection and the document ID, and firestore does not allow wildcards in its path queries, so I'm confused at how I can essentially query: "/threads/*/users/{userid}". After giving this a try it is unclear at how to achieve this. – user293895 Oct 13 '22 at 15:14
You'd query the collection group `users` and then prefix filter the `threads` path, so `startAt('threads').endAt('threads/\uf8ff')` – Frank van Puffelen Oct 14 '22 at 14:06
Where would I use the userid as a variable to query for? – user293895 Oct 15 '22 at 12:16
I might be confused and not understand what you're trying to actually get back. The logic above searches across all `Users` collections under `Threads`. If you want to search in the `Users` collection for a specific thread, you can just pass build a reference to that specific subcollection. --- If this still doesn't allow you to solve the problem, I recommend editing your question to show screenshots of the document(s) you're tring to retrieve, and the code of the query you already tried. – Frank van Puffelen Oct 16 '22 at 01:26
What I want is to find all the threads that contain a document ID in the Users subcollection that equals the user id. To do this I'd have to query against the user ID. – user293895 Oct 19 '22 at 15:04

score 0 · Answer 2 · answered Oct 19 '22 at 15:12

I don't believe there is a satisfactory answer to this problem, and am convinced that at the time given the constraints it is intractable. However there is a workaround, I added a reference to the Thread document in the Users subcollection, so now the schema looks something like this:

Threads (threadid)
    ThreadUsers (userid)
        admin
        threadref
        userref
    title
Users (userid)

Now I can perform a collection group query to find all the ThreadUsers documents that contain a specific userref, and that will give me a list of thread references associated with the user.

Query subcollection document existence in firestore

2 Answers2