Join queries

warning

This feature is experimental

In certain scenarios, data that the collaboration system requires may be stored in a separate table from the rest of the data.

Take this database schema for example:

The collaboration system expects every annotation to have a documentId, but in the structure above, that information is contained in a separate table (DocumentAnnotations).

Since documentId cannot be queried directly from Annotations, we must do a join query on DocumentAnnotations to capture that data.

To make a join query, you can provide a join property to the column you need to make a join query on.

The `join` property#

The join property accepts an array with four strings:

import SQLResolverGenerator, { MutationOperationType } from '@pdftron/collab-sql-resolver-generator';

const resolvers = SQLResolverGenerator({
  info: {
    Annotations: {
      table: 'Annotations',
      columns: {
        documentId: {
          join: ['TableName.ColumnName', 'JoinLeft', 'JoinOperator', 'JoinRight'],
        },
        ...other
      },
    },
    ...etc
  }
})

The first item in the array should be the name of the Table and Column that you want to Select. The next three items build up the WHERE clause for the join query.

In our example database schema above, we would set the following:

import SQLResolverGenerator, { MutationOperationType } from '@pdftron/collab-sql-resolver-generator';

const resolvers = SQLResolverGenerator({
  info: {
    Annotations: {
      table: 'Annotations',
      columns: {
        documentId: {
          join: [
            // INNER JOIN DocumentAnnotations.documentId 
            // ON Annotations.id = DocumentAnnotations.annotationId
            'DocumentAnnotations.documentId', 
            'Annotations.id', 
            '=', 
            'DocumentAnnotations.annotationId'
          ],
        },
        ...other
      },
    },
    ...etc
  }
})

Complex join queries#

For more advanced join queries, you can pass a function as the second item of the join array. This function accepts an instance of Knex.JoinQuery and can be used to build up your join clause.

For example, lets pretend you have this database structure:

The collaboration system requires that we return the ID of the user who created the document. In this case, that information is part of the "UserDocuments" table, which requires us to do a nested join query. This is what we would do:

import SQLResolverGenerator, { MutationOperationType } from '@pdftron/collab-sql-resolver-generator';

const resolvers = SQLResolverGenerator({
  info: {
    Documents: {
      table: "Documents",
      columns: {
        authorId: {
          join: ['UserDocuments.userId', (builder) => {
            builder
              .on('UserDocuments.documentId', '=', 'Documents.id')
              .andOnVal('UserDocuments.isOwner', '=', true)
          }],
        },
        ...other
      }
    },
    ...etc
  }
})

warning

The join property#

Complex join queries#

The `join` property#