Firebase alternative database design for this model

Question

I am really stuck using Firestore for my backend. Since every single DB design, I try ends with Firebase not allowing almost any combination of subqueries or nested filtering I need.

The domain is simple I have courses which has categories and languages, and I want to filter those courses by category and language.

What I have tried so far:

collections:

[
    courses,
    categories,
    languages,
]

document samples:

course

{
    id: 'pQYYsU80iLgZqTiYLkJXwHL25873',
    title: 'Javascript II',
    teacherName: 'Bob',
    categories: ['javascript','nodejs'],
    languages: ['english','spanish']
}

category

{
    id: 'QJMzrwEJ6vRtPBsfSOVxojTMuHk1',
    name: 'programming',
    subcategories: ['programming','javascript','php','nodejs'],
    keywords: ['computer', 'developing', 'web', 'developer', 'programming','javascript','php','nodejs']
}

language

{
    id: 'AbMzrwC6vRRPBsfSOVx2jTMuHk1',
    name: 'english',
    code: 'en-US',
}

I want to query, i.e.: all the courses about 'javascript' and available in 'english'. But it is impossible to fetch them in a single query as array-contains cannot be nested multiple times.

I cannot do it by fetching multiple queries as the device runs out of memory very quickly.

Is there any domain design alternative to achieve this in a single query?

user_5246885 · Answer

But it is impossible to fetch them in a single query as array-contains cannot be nested multiple times.

Yes, you can use only once array-contains. If you are allowed to change the document structure, then you can solve this by changing the array data type to a Map like this:

{
    id: 'pQYYsU80iLgZqTiYLkJXwHL25873',
    title: 'Javascript II',
    teacherName: 'Bob',
    categories: {
        javascript = true,
        nodejs = true
    },
    languages: {
        english = true,
        spanish = true
    }
}

I want to query, i.e.: all the courses about 'javascript' and available in 'english'.

Using the above schema, the following query:

coursesRef
    .where("categories.javascript", "==", true)
    .where("languages.english", "==", true);

It will work perfectly fine.

user_209103 · Answer

An alternative to Alex's excellent answer would be to add a new field where the values are available category/language combinations:

category_language_pairs: ['javascript_english', 'javascript_spanish', 'nodejs_english', 'nodejs_spanish'],

With this field in place, you only need a single condition on your query:

coursesRef
    .where("category_language_pairs", "array-contains", "javascript_english")

This of course inflates the document size, so is probably best reserved for cases where the number of category/language combinations is not too high.

Firebase alternative database design for this model

2 Answers