Loading Related Database Items

Prerequisites

This is an advanced tutorial. To follow it, you must understand basic Insites concepts, the topics in the Tables and Database Fields. For testing, you must be acquainted with measuring the execution time of Liquid code fragments using the filter.

• Development Workflow
• Get Started
• Database Fields and Tables
• Measuring Execution Time of Liquid Code

Steps

The sample scenario in this guide walks you through two approaches to demonstrate the difference between the naive and the recommended approach to loading related records.

Loading related records in this demonstrative scenario is a five-step process:

• Step 1: Creating the database items
• Step 2: Loading related record items for each record (not recommended)
• Step 3: Testing the naive approach
• Step 4: Requesting data from a related record within the GraphQL query (recommended)
• Step 5: Comparing the results of the naive approach vs. the recommended approach

Step 1: Creating the database items

The data schema in this scenario consists of two records: and . The programmer record has a property that references a corresponding company record (foreign key).

app/schema/company.yml

name: company
properties:
- name: name
  type: string
- name: email
  type: string
- name: url
  type: string

app/schema/programmer.yml

name: programmer
properties:
- name: title
  type: string
- name: email
  type: string
- name: company_id
  type: string

The goal is to load data from the table, along with related data in the table, by joining the two tables:

Programmers:

Companies:

Result collection:

Step 2: Loading related record items for each record (not recommended)

Use two GraphQL queries for this approach:

app/graphql/programmers.graphql

query programmers {
  programmers: records(
    per_page: 20,
    filter: {
      table: { value: "programmer" }
    }
  ) {
    total_entries
    results {
      properties
    }
  }
}

app/graphql/company.graphql

query company($id: ID) {
  companies: records(
    per_page: 20,
    filter: {
      table: { value: "company" },
      id: { value: $id }
    }
  ) {
    results {
      table
      id
      deleted_at
      properties
    }
  }
}

While displaying the programmer collection — for each programmer record — fetch the related company record using the GraphQL query:

{% graphql g = "programmers" %}

{% for programmer in g.programmers.results %}
  <tr>
    <td>{{ programmer.properties.email }}</td>
    <td>{{ programmer.properties.title }}</td>
    {% if programmer.properties.company_id %}
      {% graphql company = "company", id: programmer.properties.company_id %}
      <td>{{ company.companies.results.first.properties.name }}</td>
      <td>{{ company.companies.results.first.properties.url }}</td>
    {% endif %}
  </tr>
{% endfor %}

Step 3: Testing the naive approach

Use the time_diff Liquid filter to test the naive approach. The quantitative results are displayed in the table below:

Step 4: Request related data in the GraphQL query (recommended)

To avoid n+1 queries, request company data within the GraphQL query. Load related data simultaneously with the programmer collection using the GraphQL field.

related_record(table: "company", join_on_property: "company_id") { properties }

The and arguments are required. is used as the foreign key of the company collection. In SQL language, this could look similar to:

SELECT * FROM programmers JOINS companies ON (programmers.company_id = companies.id)

Your GraphQL query would look like this after updating:

query programmers {
  programmers: records(
    filter: {
      table: { value: "programmer" }
    },
    per_page: 200
  ) {
    total_entries
    results {
      properties
      company: related_record(table: "company", join_on_property: "company_id") {
        url: property(name: "url")
        properties
      }
    }
  }
}

Update the page. The page looks much simpler now:

app/views/pages/programmers/index.liquid

{% for programmer in g.programmers.results %}
   <tr>
     <td> {{ programmer.properties.email }} </td>
     <td> {{ programmer.properties.title }} </td>
     <td>{{ programmer.company.properties.name }} </td>
     <td>{{ programmer.company.properties.url }} </td>
   </tr>
{% endfor %}

Step 5: Comparing the results of the naive approach vs. the recommended approach

Test the second approach using the same measurement method. The results are as follows:

Observe that requesting data from the related record within the GraphQL query delivers results around ten times faster than the previous method outlined in Step 3. The n+1 queries in the naive approach slow down the process because displaying each database item produces a request for another database item.