How to paginate multiple sorted indexes?

[ryansumma]

I am creating a CRM that has 3 record types that a user can create - Accounts, Jobs, and Contacts. (Collections). In this example, let’s assume the account document contains references to 2 job documents and one contact document reference. Both job documents contain references to the account document, while one of the job documents contains a reference to a contact document. Lastly, the contact document contains a reference to a job document and a reference to the account document.

accountDoc = {
    ...data,
    jobs: [ Ref(Collection("jobs"), "job-1"), Ref(Collection("jobs"), "job-2") ],
    contacts: [ Ref(Collection("contacts"), "contact") ]
};

// job 1
jobsDoc = {
    ...data,
    account: Ref(Collection("accounts"), "account"),
    contacts: [ Ref(Collection("contacts"), "contact") ]
};

// job 2
jobsDoc = {
    ...data,
    account: Ref(Collection("accounts"), "account"),
    contacts: []
};

contactDoc = {
    ...data,
    account: Ref(Collection("accounts"), "account"),
    jobs: [ Ref(Collection("jobs"), "job-1") ]
};

When a user navigates to any of those 3 records (Account, Job, Contact), they can create a note associated with that record. Let’s assume the user creates a note on the job-1 record, account record, and contact record.

noteDoc = {
    ...data,
    related: Ref(Collection("accounts"), "account")
};

noteDoc = {
    ...data,
    related: Ref(Collection("jobs"), "job-1")
};

noteDoc = {
    ...data,
    related: Ref(Collection("contacts"), "contact")
};

Since the account document, both job documents, and the contact document are all related to each other, I would like to query all note documents that are related to those records and sorted by most recent. To take it a step further, I would like to be able to paginate the note documents sorted by the most recent note documents. For example, I’d like to query 2 of the most recent note documents that are related to those records. Is this possible? Is there a better way to go about this? Please and thank you!

[ptpaterson]

If I understand correctly, here are the relationships you have. Do these look right?

• jobs >— accounts
  • jobs have one account
• contacts >— contacts
  • cantacts have one account
• contacts >–< Job
  • many-to-many between contacts and jobs
• notes >— [accounts or jobs or contacts]
  • notes have a single relationship to one of the other 3 collections

So basically any Job or Contact can be traced back to a single account. Are you trying to query for all the notes that tie back to that account? Or just those notes directly related to the selected/navigated-to record?

[ptpaterson]

example query structure

This is assuming you want all notes for all the relationships.

We can traverse the relationships, Union all of the accounts, jobs, and contacts, then Join with the related notes.

Let(
  {
    selected_account: Ref(Collection("accounts"), "101"),
    related_jobs: SET_OF_RELATED_JOBS,
    related_contacts: SET_OF_RELATED_CONTACTS,
    all_records: Union(
      Singleton(Var("selected_account")),
      Var("related_jobs"),
      Var("related_contacts")
    )
  },
  Paginate(
    Join(
      Var("all_records"),
      Index("notes_by_related")
    )
  )
)

I’ve left the expressions SET_OF_RELATED_JOBS and SET_OF_RELATED_CONTACTS undefined. I am not sure what is the best way to do that for your application. It could be turning the array of relationships you have into a Set (using Map, Union and Singleton). It appears you are storing the relationship in both records. If you instead only stored it in the many-side (e.g. account–>job only in the job), then you can use an Index to traverse from the account to related jobs – something like Match(Index("jobs_by_account"), Var("selected_account"))