@ptpaterson @aprilmintacpineda thanks for your replies. Good reading.
@ptpaterson this is a pattern I am actively working on for a document locking scheme I have put together to enable users to manage permissions on individual documents being shared between different user groups, where funnily enough those groups are themselves inside organizations.
In answer to your query, I decided to do a full write up of how I plan to implement this, but altered it to make it suitable for what @aprilmintacpineda is doing (and for easier context to anyone that comes along later and reads this thread).
I call this pattern the “Tertiary Index Pattern” - and so organizationList would be a tertiary index.
I don’t know if there already exists another name to describe this, or if the term Tertiary Index butts heads with something else, but for now it will do.
Here’s the pseudo code:
Enforce Unique Entries
export default CreateIndex({
name: 'organization_users_unique',
source: Collection('organisationUsers'),
terms: [
{
field: ['data' , 'organisation'],
field: ['data' , 'user']
}
],
values: [
{ field: ['data' , 'organisation'] },
{ field: ['data' , 'user'] }
],
// uniqueness works on the combination of terms/values
unique: true,
serialized: false
})
-
Note: that the ‘ref’ of the record is missing from terms and values above - this is because having the ref as one of the values would mean that the uniqueness check would become meaningless because every new primary key for a record is different, so you could have two records with the same user and organisation, and it would not be blocked because the ref would be different.
-
Note: Performance wise, if you are going to have thousands of users regularly added to an organisation, it is much better to have the two terms: user and organisation - this will speed up Fauna’s ability to check for duplicates when you are creating a new document in organizationUsers. You can also query this index directly to see if a user has already been added to an organisation.
Fetch all users of an organization
export default CreateIndex({
name: 'organization_users_by_organisation',
source: Collection('organisationUsers'),
terms: [
{
field: ['data' , 'organisation']
}
],
values: [
{ field: ['data' , 'user'] },
{ field: ['data' , 'organisation'] },
{ field: ['ref'] }
],
// uniqueness works on the combination of terms/values
unique: false,
serialized: false
})
-
Note: this index can be used to fetch all users of a given organisation. It orders the results by user so search results are easier to search. The ref is also included so that you can quickly access and alter the organizationUsers document if needed.
-
If you don’t want to have two indexes that cover this, you could alter this index by removing the ref value, and setting the unique flag to true. This would enable this index to both serve to fetch users by their organization, and also to enforce uniqueness - however it will not be as performant as the organization_users_unique index above.
Adding a user to an organisation (support function)
This is a support function - do not call this function externally. It should only be used by AddUserToOrganization (documented below) or RemoveUserFromOrganization (an example name, but not documented below).
// Add or remove organization entries from a user's document
export default CreateFunction({
name: 'UpdateUserOrganisations',
body: Query(
Lambda(
['user_id_or_ref', 'add_organization_id_or_ref', 'remove_organization_id_or_ref'],
Let(
{
// Validate and fetch user document
user_ref : If(
IsRef(Var('user_id_or_ref')),
Var('user_id_or_ref'),
Ref(Collection("users"), Var('user_id_or_ref'))
),
user : If (
Exists(Var('user_ref')),
Get(Var('user_ref')),
Abort('Valid User Not Passed In')
),
// Validate organization(s), but don't bother fetching them
add_organization_ref : If(
IsNull(Var('add_organization_id_or_ref')),
null,
If (
IsRef(Var('add_organization_id_or_ref')),
Var('add_organization_id_or_ref'),
Ref(Collection("organizations"), Var('add_organization_id_or_ref'))
)
),
remove_organization_ref : If(
IsNull(Var('remove_organization_id_or_ref')),
null,
If (
IsRef(Var('remove_organization_id_or_ref')),
Var('remove_organization_id_or_ref'),
Ref(Collection("organizations"), Var('remove_organization_id_or_ref'))
)
),
organization_valid : If (
Or(
And(Not(IsNull(add_organization_ref)), Exists(Var('organization_ref')))
And(Not(IsNull(remove_organization_ref)), Exists(Var('remove_organization_ref')))
),
true,
Abort('Valid Organization Not Passed In')
),
// Fetch and update the organizationList
organizationList : Select(['data','organizationList'], Var('user'), []),
// Adding items
organizationList_update1 : if (
Or(IsNull(Var('add_organization_ref')), IsEmpty(Var('add_organization_ref'))),
Var('organizationList'),
Union(Var('organizationList'), Var('add_organization_ref'))
),
// Removing items
organizationList_update2 : if (
Or(IsNull(Var('remove_organization_ref')), IsEmpty(Var('remove_organization_ref'))),
Var('organizationList_update1'),
Difference(Var('organizationList_update1'), Var('remove_organization_ref'))
),
},
Update(
Var('user_ref'),
{
data: {
organizationList : Var('permission_group_update2')
}
}
)
)
)
),
role: 'server'
})
Adding a user to an organisation (API)
Function used as API to add a user to an organization.
export default CreateFunction({
name: 'AddUserToOrganization',
body: Query(
Lambda(
['organization', 'user'],
Let(
{
isNewMapping : Not(Exists(Match(
Index('organization_users_unique'),
Var('organization'),
Var('user')
))),
result : Do (
Var('isNewMapping'),
Create(
Ref(Collection("organizationUsers"), {
data: {
user : Var('user'),
organization : Var('organization')
}
}
),
Call('UpdateUserOrganisations', ['user', 'organization', null])
)
},
Var('result')
)
)
),
role: 'server'
})
To add a user to an organisation, you would then call it like this
Call('AddUserToOrganization', [
Ref(Collection('organizations'), 123),
Ref(Collection('users'), 456),
])
The code should be mostly self explanatory. AddUserToOrganization uses an index to see if a mapping already exists, if it does it will return ‘false’, else it will add the mapping to organizationUsers, and then use the UpdateUserOrganisations function to update the ‘tertiary index’ organizationList on the user’s document. I call this a tertiary index, but I am not sure if there is a proper name for using an array like this inside a document as an index.
UpdateUserOrganisations checks that the user record exists, that the organization being added or removed also exists, and then adds or removes the organization entry. Note that technically the Union and Difference functions support arrays, but the code that validates the add_organization or remove_organization values does not support arrays (no doubt there is a way to do this, but that would just make things more complicated than they need to be - and a use case that is probably not that likely?)
Again, this code has not been tested, but the principle should work - and it is what I will be working on shortly.
Status Field
One other point I would note is that I typically never delete documents (or records from a DB). Instead I put a status field on every one, and when a document is archived or ‘deleted’ I update the status value rather than removing it from the collection/table.
This means that I also use status values as part of my indexes to quickly filter items that are valid, verses data that is old.
The reason I don’t delete data is because it is easier to undo (when a user changes their mind about something) and because deleting data is not instant - so even if you delete something it still sits there for a while (and in the case of MySQL will be there forever until you run an optimisation on the DB or table affected - and will slow things down in the mean time as it has to keep jumping over deleted items). Fauna cleans up by itself (not having to manage that is pretty cool) and I presume is not affected in the same way as SQL DBs are by deleted records/documents hanging around, though there is still a delay in actually removing the data in Fauna.
From experience, I think it is better not to delete data. Obviously, if you are creating massive documents that use lots of space, it may make sense to just delete them - though I would probably choose to compact them and then archive them instead (keep them in the same collection, with status archive).
Status field values I use in code are:
STATUS_DISABLED
STATUS_ENABLED
STATUS_ARCHIVED
STATUS_DELETED
These are constants, with numeric values.
So in the above example code a valid record has status STATUS_ENABLED, and a deleted entry would be STATUS_DELETED. Now this obviously requires altering the code above to take the status value into account and alter it from STATUS_DELETED to STATUS_ENABLED in the case of a user being re-added to an organisation. You get the idea.
Good luck!
Edited for clarity.
