firestore Query issue order by if array contains

Question

I have a group chat app. Each chat document has a node called

chatUsers:[1,2,3,4,5]

where 1-5 is a user Id on that node.

I need to pull all chats where I am a user, so I use the array-contains operator. My issue is there is also another node called archivedChat. That node tells if I archived the chat.

ie:

archivedChat:[1,2] meaning users 1 and 2 have archived this chat. I want to get all chats where I am a user and I have not archived, and then all chats I am a user and have archived.

firebase prevents using these two operators together, and I understand I can filter on the front end, but I'd need all records retrieved that. I could have 1000 chat rooms/documents, so I do not want to query the entire collection, I'd much much prefer doing 2 separate queries. Here is where I am at:

 query(
        roomsRef,
        where(USERS_PATH, 'array-contains', currentUserId),
        where(ARCHIVE_USERS_FIELD, 'not-in', [[currentUserId]]),
        orderBy(LAST_UPDATED_FIELD, 'desc'),
        limit(roomsPerPage),
        startAfter(lastRoom)

I can think of no way to do this. Since the chat is the same whether it is archived or not, and my archived flag just shows all archived chats in a different area and effects how I display it, I really do not want to move it into another collection....

Any help?

score 0 · Accepted Answer · answered Jan 24 '23 at 04:36

0

I'd recommend adding a third field that essentially combines the information from the other user lists. If you only want to show the document for users that are in the USERS_PATH and are not in ARCHIVE_USERS_FIELD, add a field (say SHOW_USERS) that contains just the UIDs of those users.

This type of data duplication is quite common when using NoSQL databases, where you often have to model/augment your data to fit with the specific use-cases you have.

answered Jan 24 '23 at 04:36

Frank van Puffelen

565,676
79
828
807

For clarity, I'd probably go with `MEMBERS` (array of user IDs in chatroom), `MEMBERS_ACTIVE` (array of user IDs still participating in the chatroom) and `MEMBERS_ARCHIVED` (array of user IDs who have archived the chatroom). Plus potentially adding `MEMBERS_CSV` depending on your needs, as a list of sorted, comma-separated users in the chatroom so you can do things like "find the chatrooms with only user A, user B and user C" ([dated example here](https://stackoverflow.com/a/66838632/3068190)). – samthecodingman Jan 24 '23 at 05:41
Yeah, that `MEMBERS_ACTIVE` field name makes sense too, Sam. One you have the fields that you need laid out, it's usually not too hard to add the array manipulations to your application code. – Frank van Puffelen Jan 24 '23 at 06:23

score 0 · Answer 2 · answered Jan 25 '23 at 03:01

After learning the limitations, i think i am going to go to mongodb cloud and ditch firestore.

Firestore is very good, but if you want complex queries ie(users are part of teams, and we want both team chats to be pulled along with user chats, team chats a user is not on, user chats archived, and team chats archived from a users view, there is no great way to query.

Lastly if i could have 1000 team Chats all with listeners on the room user online status’s, etc i can easily exceed quota limits.

Mongodb requires a server layer but there are no server limits, and better query capabilities.

Much more complex to build as subscribing to documents has to be pushed via sockets instead of a very clean front-end sdk that firestore has.

Each has their perks, but a large scale chat app with 1000+ rooms and complex querying feels like forcing a square peg through a round hole here:(

firestore Query issue order by if array contains

2 Answers2