Loading Related Models
This guide will help you load related models while avoiding n+1. n+1 queries slow down the process, because displaying each record produces a request for another model.
Requirements
This is an advanced tutorial. To follow it, you should be familiar with basic platformOS concepts, the topics in the Get Started section, Model Schemas, 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
- Models and Model Schemas
- 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 models, use the second, recommended approach.
Loading related models in this demonstrative scenario is a five-step process:
Step 1: Create models
The data schema in this scenario consists of two models: company and programmer. The programmer model has a company_id property which references a corresponding company model [foreign key].
app/model_schemas/company.yml
name: company
properties:
- name: name
type: string
- name: email
type: string
- name: url
type: string
app/model_schemas/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:
| 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 model for each record (not recommended)
In this approach, you use two GraphQL queries:
app/graphql/programmers.grapqhl
query programmers {
programmers: models(
per_page: 20,
filter: {
model_schema_name: { value: "programmer" }
}
) {
total_entries
results {
properties
}
}
}
app/graphql/company.graphql
query company($id: ID) {
companies: models(
per_page: 20,
filter: {
model_schema_name: { value: "company" },
id: { value: $id }
}
) {
results {
model_schema_name
id
deleted_at
properties
}
}
}
While displaying the programmer collection — for each programmer record — you fetch the related company model 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 model.
Step 4: Request data from related model 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_model.
related_model(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: models(
filter: {
model_schema_name: { value: "programmer" }
},
per_page: 200
) {
total_entries
results {
properties
company: related_model(model_schema_name: "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-model | 122.504 |
| 160.885 | |
| 134.064 | |
| 160.051 | |
| 131.056 |
Requesting data from the related model within the GraphQL query delivers results 10 times faster.
Live example and source code
To see how it works on a real webpage go to the live example page.
Source code can be found on GitHub.