Using Indexes
The firstWhere method is going to require, in the worst case, reading the entire collection looking for a match. This can be fixed by using an index.
You have some options depending on your data model and other query patterns, but it looks like a plusses document should have a unique combination of user and company, so let’s build an index with those as terms.
- enable efficient search by indexing on both key fields
- include “plusses” field in values to avoid extra read cost for fetching document to get it
- the reference is always covered in v10, so you can also efficiently get the document ID
// FSL
collection plusses {
history_days 0
index by_user_company {
terms [.user_id, .company]
values [asc(.plusses)]
}
}
collection companies {
history_days 0
index by_company_id {
terms [.data.id]
}
}
And using the indexes
let company_match = companies.by_company_id("T045JP1N51T")!
let plusses_doc = plusses.by_user_company("U045MUHJD34", company_match)
if (plusses_doc == null) {
plusses.create({
username: "plusbot3",
user_id: "U045MUHJD34",
plusses: 1,
company: company_match
})
} else {
plusses_doc!.update({ plusses: user.plusses + 1 })
}
Optimizing relationships
Whether you are using v4 or v10, you should try to store relationships using native references rather than foreign keys. Using direct references mean you can resolve the relationship without an index lookup (or in the case of using where or firstWhere, a full table scan).
v10 automatically handles referencing and dereferencing Documents, similar to how many languages automatically handle pointers for reference values. This means you can supply a document as a value when you create or update another document, and it will store a reference.
You are already doing this when you store the company match in the plusses document, but you didn’t for the user. I recommend also storing the user document directly.
// FSL
collection plusses {
history_days 0
index by_user_company {
terms [.user_id, .company]
values [asc(.plusses)]
}
index by_user {
terms [.user]
values [asc(.plusses), .company]
}
index by_company {
terms [.company]
values [asc(.plusses), .user]
}
}
collection users {
history_days 0
index by_user_id {
terms [.user_id]
}
}
collection companies {
history_days 0
index by_company_id {
terms [.data.id]
}
}
Updating the original query
let user_match = users.by_user_id("U045MUHJD34")!
let company_match = companies.by_company_id("T045JP1N51T")!
let plusses_doc = plusses.by_user_company(user_match, company_match)
if (plusses_doc == null) {
plusses.create({
username: "plusbot3",
user_id: "U045MUHJD34",
user: user_match,
company_id: "T045JP1N51T",
company: company_match,
plusses: 1,
})
} else {
plusses_doc!.update({ plusses: user.plusses + 1 })
}
And with the indexes and relationships you can run other queries.
Listing a user’s top plusses
// most efficient if you have the document id in your client
// otherwise you can do an efficient index lookup by the business id you provide
let user_match = users.byId("3691238741239045822") // do not use null assertion
// The user document is not read, because we never needed any fields from it.
// The reference is used to make the index lookup.
let top_plusses = plusses.by_user(user_match).reverse.take(5) {
// rendering the company does not require another index lookup, because it's stored as a reference
company {
id,
name,
data: { id } // wrap in data because the field name conflicts with native top-level `id` field.
}
}
List the top users that like a company
let company_match = company.byId("3691233879234562346") // do not use null assertion
let top_plusses = plusses.by_company(company_match).reverse.take(5) {
// rendering the user does not require another index lookup, because it's stored as a reference
user {
id,
name,
user_id
}
}