Tutorial 12 - Related Records and Datasources
Introduction
In this tutorial we're going to show you how you can use a single query to get not only results in a single table, but at the same time return related results from another table.
For example:
Products may be related to categories- and each category may be related to some help articles.
Blog Articles may each be related to an Author, and that Author may have a relationship with some Secure Zones.
Using related_records is powerful because:
It allows you to build more complex applications - giving you complete control over getting the data you need.
It's flexible - if you've stored enough data, you can define relationships in your query without changing any settings.
Combining multiple queries into one will give you much better performance than any other way of getting this data, e.g. nesting Siteglide include tags inside each other.
Glossary of Terms
Here are a few of the terms you may come across in this topic:
Term | Definition |
---|---|
relational database | A relational database describes a database comprised of tables, where there may be predefined relationships between records in different tables. platformOS's database is a kind of relational database. |
join | In many query languages e.g. SQL, a join defines how two tables are linked together. In platformOS, a join on property is used to define which field should be used to find other related_records. |
tables | In platformOS, as in many other databases, a table is a set of data which contains records of a certain type, with a certain set of properties. |
records | In platformOS, as in many other databases, a record is a single entry of data in a database. |
related_records | In platformOS, records which share a relationship. It can be single:single, many:many or single:many. This is effectively the same concept as a datasource. |
datasource | In Siteglide, a datasource is where there is a relationship between records in the same table or in a different table, and data can be "sourced" from that other table. It can be single:single, many:many or single:many. This is effectively the same concept as platformOS's related_records. |
datasource and datasource_multi field types | In Siteglide, some fields can be given the datasource and datasource_multi field types: Field Types These are designed to store IDs of other records to make joining easy. However, you can also in GraphQL join other types of fields. |
foreign | In platformOS, a foreign property refers to a property outside of the current record. The foreign property is matched with the join on property in order to fetch related records which share a relationship. |
data graph | In computing a data graph describes the relationships between different nodes of data. Think of a node like a city and the relationships like roads. One of the reasons GraphQL is called GraphQL is that it is trying to give queries an intuitive graph-like structure- we define the relationships in the query itself, and the results return with the same relationship structure. |
Building your first query using Related Records
The great thing about related records being as flexible as they are is that there are a lot of examples we could choose from. Our first example though will try to keep things simple by fetching Blog posts with their associated authors.
Step 1) Write your query to fetch Blog Posts
We've already covered how to write a records query and filter it so it contains only blog posts. Here's an example:
Step 2) Find out which fields match across the two tables
Since these two modules - the Blog and Authors Module - are both created by Siteglide, they already store the information needed to join them inside their properties. We only need to look at the existing properties in detail to get the information we need to build our query.
There are several ways to do this. One of the easiest to do on an everyday basis is to use Introducing Siteglide CLI to pull
your site, then to look in marketplace_builder/form_configurations:
ââââforms â form_1.liquid â form_2.liquid â form_3.liquid â ââââmodules â â module_17.liquid â â module_3.liquid â â module_6.liquid â â â ââââmodule_14
For this exercise, we'd be looking at module_3
for blog and module_6
for authors.
In Blog, scroll down or use ctrl-f to search for author
The form configuration will contain both the human-friendly name and the Siteglide ID for each field:
module_field_3_4: name: Author type: datasource live: true hidden: false order: 0 editable: true datasource_id: module_6 required: false validation: {}
Great! It's a datasource field, which is pefect, because it will already be set up to contain IDs of Author records related to each Blog record. We don't need to look at the Author record now to know that the module_field_3_4
property of the Blog and the id
of the Author should match. Sometimes, you'll need to look at both.
Since this is a GraphQL tutorial- here's an alternative way to check the form-configurations- using a query (see how we use filter to look for only the Blog and Author configurations)!
In the Siteglide Admin, we can check that some of the Blogs already have authors set in the datasource
field, in this case this Blog item has an Author ID of 100
in the field:
Step 3) Add related_records inside results
To start with, look inside results in explorer: related_record
and related_records
appear as possible results. By including these special objects inside the results object, we can define a branching tree of related results.
The two options are similar but have one key difference:
related_record - Use this to define a 1:1 or a many:1 relationship. It will return a single record as an object. This may give you better performance, if you know for example there's only one author.
related_records - Use this to define a 1:many or a many:many relationship. It will return multiple records as an array.
The main reason to pay attention to which you choose is that it will change the way you structure your Liquid to access the data- remember if there is an array in the data you may need to loop over it in order to access it.
For now, let's use related_record, as we have a datasource
field to join with, and only one Author per Blog record
:
Step 4) Rename the related_record
Remember, we use a string with no spaces and a colon to "rename" things in GraphQL. It's a good idea to rename this related_record to describe what it will return. In this case, it describes a single author.
This will make it easier to understand the results and allow you to add other related_records
in future (it won't let you if names clash).
Step 5) Set the join_on_property
As mentioned in the glossary, the join_on_property
is used to define the property in the current result record which will be used to match with a related record. In step 3 we worked out it was module_field_3_4
.
We don't need to write the value of the module_field_3_4 field, just the Siteglide field ID.
you also don't need to add
properties
before the field ID, as you may when filtering a query; here, platformOS works this out automaticallythis goes inside the curly brackets as an argument
Step 6 - Set the Foreign Property
The foreign property is the counterpart to the join_on_property, but it refers to the property in the record we are looking for, in this case the Author record is the record, and the property is id
.
Note the syntax is the same, even though module_field_3_4
is a custom property and id
is a core property in platformOS.
Step 7 - Set the Table
table
here is a filter, despite not being inside a filter object. It must be set to describe the table we should look in for these related records. In this case, the ID of the Authors module: module_6
Step 8 - Results
We've already defined which results we want for Blog items, and that we want to return a related author per blog item, but we also need to define which authors properties should be returned.
We could return the whole properties object, but where possible it's worth being extra efficient when working with relationships. There is more work for the query to do, and it may run more slowly. Maybe we just need the author's name and an image (I use the information we looked at in step 2 to find the module_6 image field ID)?
The results go in the new object under related_record
after the arguments; no results
key is needed:
That's the query itself done!
Step 9 - Working with the Results
The results in JSON may look like the below (we've minimised Blog properties which aren't useful to the example):
{ "data": { "records": { "results": [ { "id": "97", "properties": {...}, # note - this record 97 did NOT have a match with an author. Maybe the Author has been deleted or maybe the ID has not been stored in the field we're joining on.
} }
As always, when outputting in Liquid, you can use dot notation (see Liquid Dot Notation or Tutorial 5 - Using Liquid to run GraphQL queries on your Site) to access the results, until you get to an array. Since we only asked for a single author, we can use dot notation inside the blog record to access the author. We still need to loop over the blog results as always:
A many:1 alternative
What if you need this to fetch information the other way around, e.g. you are on a page which displays information about an author and you wish to display a list of the Author's Blog Posts? An additional aspect to this is that this is a 1:many relationship- it's no problem- we'll use related_records
plural instead of related_record
to return an array instead of an object.
Starting with the query above, lets make some changes:
Step 1) Change the initial query to fetch Authors first and rename:
Step 2) Change join on property to Author's ID
Step 3) Change foreign property to Blog's property which contains Author's ID
Step 4) Change related table to Blog
Step 5) Change related_record to related_records and rename
Step 6) Change Results
Step 7) Liquid example
Further Learning
Next, you could experiment with some of these options:
using filters to further filter related records e.g. Authors with Blog posts which are enabled
using
related_users
an alternative torelated_records
which fetches CRM users with a relationship to your record. You don't need to specify a table, but join and foreign properties work the same.Additional nesting. Inside your related_records results, you can define related_records again. This allows you to build a complex results tree containing as many layers of related results as you like, for example adding to our example above- you could return the categories of each Blog the Author had published- and display a list of category names.
Last updated