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, Custom Model Types, and Properties. For testing, you should be familiar with measuring the execution time of Liquid code fragments using the time_diff filter.

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].

custom_model_types/company.yml
name: company
properties:
- name: name
  type: string
- name: email
  type: string
- name: url
  type: string
custom_model_types/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 model for each record (not recommended)

In this approach, you use two GraphQL queries:

graph_queries/programmers.grapqhl
query programmers {
  programmers: customizations(name: "modules/n_plus_one/programmer"){
    total_entries
    results {
      properties
    }
  }
}
graph_queries/company.graphql
query company($id: ID) {
  companies: customizations(name: "modules/n_plus_one/company", id: $id){
    results {
      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 = 'modules/n_plus_one/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 = 'modules/n_plus_one/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 model.

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 looks like this now:

query programmers {
  programmers: customizations(name: "modules/n_plus_one/programmer", per_page: 200) {
    total_entries
    results {
      properties
      company: model(join_on_property: "company_id") {
        url: property(name: "url")
        properties
      }
    }
  }
}

Update the programmers/index page. The page looks much simpler now:

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.

Questions?

We are always happy to help with any questions you may have. Check out our Help page, or contact us.