Loading Related Records

This guide will help you load related records while avoiding n+1. n+1 queries slow down the process, because displaying each record produces a request for another record.

Requirements

This is an advanced tutorial. To follow it, you should be familiar with basic platformOS concepts, the topics in the Get Started section, Tables, and Properties. For testing, you should be familiar with measuring the execution time of Liquid code fragments using the time_diff filter.

• platformOS Workflow
• Get Started
• Records and Tables
• Properties
• Measuring Execution Time of Liquid Code Fragments (time_diff)

Steps

The guide's sample scenario walks you through two different approaches to demonstrate the difference between the naive and the recommended approach. When implementing related records, use the second, recommended approach.

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

Step 1: Create records

The data schema in this scenario consists of two records: company and programmer. The programmer record has a company_id property which 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 programmers' data along with related company by joining programmers and companies:

Programmers:

email	title	company_id
programmer-1@example.com	junior	238415
programmer-2@example.com	middle	238416
programmer-3@example.com	senior	238417
programmer-7@example.com	senior	238421

Companies:

id	name	url
238415	Company #2	company-2.com
238416	Company #3	company-3.com
238417	Company #4	company-4.com
238421	Company #8	company-8.com

Result collection should be as follows:

programmer.email	programmer.title	company.name	company.url
programmer-1@example.com	junior	Company #2	company-2.com
programmer-2@example.com	middle	Company #3	company-3.com
programmer-3@example.com	senior	Company #4	company-4.com
programmer-7@example.com	senior	Company #8	company-8.com

Step 2: Load related record for each record (not recommended)

In this approach, you use two GraphQL queries:

app/graphql/programmers.grapqhl

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 — you fetch the related company record using the company 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: Test approach

Test the approach described in step 2. The results of Measuring Execution Time of Liquid Code Fragments (time_diff) in this case will be as follows:

	overall
benchmark-n+1	1236.169
	1223.485
	1418.733
	1216.547
	1314.053

The results show that this approach produces slow output. The n+1 queries slow down the process, because displaying each record produces a request for another record.

Step 4: Request data from related record within GraphQL query (recommended)

To avoid n+1 queries, request company data within the programmers GraphQL query. Load related data at once along with the programmer collection using new GraphQL field called related_record.

related_record(join_on_property: "company_id") { properties }

join_on_property argument is required and is used as a foreign key of the company collection. In SQL language this could look similarly:

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

and after updating your GraphQL query would look like this:

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 programmers/index 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: Test and compare

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

	overall
benchmark-record	122.504
	160.885
	134.064
	160.051
	131.056

Requesting data from the related record within the GraphQL query delivers results 10 times faster.

Live example and source code

Live example

Source code can be found on GitHub.