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 docid\ snfmlvk7kh4k91klxxubh 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 query blogswithauthors { records( per page 20, filter { table { value "module 3" } } ) { results { id properties } } } 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 docid a mffo96d wwedeagqzx 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)! query findblogandauthorproperties { admin forms(filter {name {value in \["module 3","module 6"]}}) { results { fields } } } 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 query blogswithauthors { records(per page 20, filter {table {value "module 3"}}) { results { id properties related 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 query blogswithauthors { records(per page 20, filter {table {value "module 3"}}) { results { id properties author related record() { } } } } 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 automatically this goes inside the curly brackets as an argument query blogswithauthors { records(per page 20, filter {table {value "module 3"}}) { results { id properties author related record( join on property "module field 3 4" ) { } } } } 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 query blogswithauthors { records(per page 20, filter {table {value "module 3"}}) { results { id properties author related record( join on property "module field 3 4", foreign property "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 query blogswithauthors { records(per page 20, filter {table {value "module 3"}}) { results { id properties author related record( join on property "module field 3 4", foreign property "id", table "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 query blogswithauthors { records(per page 20, filter {table {value "module 3"}}) { results { id properties author related record( join on property "module field 3 4", foreign property "id", table "module 6" ) { name property(name "name") image property(name "module field 6 4") } } } } 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 "author" null }, { "id" "8", "properties" { }, \# note this blog item with id of 8 did match with an author the author's fields we asked for are below! "author" { "name" "jese leos", "image" "https //cdn staging oregon platform os com/instances/10093/assets/jese leos png" } }, { "id" "10", "properties" { }, "author" { "name" "karen nelson", "image" "https //cdn staging oregon platform os com/instances/10093/assets/karen nelson png" } } ] } } } as always, when outputting in liquid, you can use dot notation (see liquid dot notation docid 2p4vus5nud99zvg09tmcq or tutorial 5 using liquid to run graphql queries on your site docid 2sc1gj360b5yvdj22v4pk ) 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 liquid {% graphql blogs with authors = "blogswithauthors" %} {% for blog in blogs with authors records results %} {{blog properties module field 3 1}} by {{blog author name}} {% endfor %} 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 query authorsandtheirarticles { #renamed records(per page 20, filter {table {value "module 6"}}) { #change table results { id properties author related record( join on property "module field 3 4", foreign property "id", table "module 6" ) { name property(name "name") image property(name "module field 6 4") } } } } step 2) change join on property to author's id query authorsandtheirarticles { #renamed records(per page 20, filter {table {value "module 6"}}) { #change table results { id properties author related record( join on property "id", #edit join foreign property "id", table "module 6" ) { name property(name "name") image property(name "module field 6 4") } } } } step 3) change foreign property to blog's property which contains author's id query authorsandtheirarticles { #renamed records(per page 20, filter {table {value "module 6"}}) { #change table results { id properties author related record( join on property "id",#edit join foreign property "module field 3 4", # edit foreign table "module 6" ) { name property(name "name") image property(name "module field 6 4") } } } } step 4) change related table to blog query authorsandtheirarticles { #renamed records(per page 20, filter {table {value "module 6"}}) { #change table results { id properties author related record( join on property "id",#edit join foreign property "module field 3 4", # edit foreign table "module 3" # edit table ) { name property(name "name") image property(name "module field 6 4") } } } } step 5) change related record to related records and rename query authorsandtheirarticles { #renamed records(per page 20, filter {table {value "module 6"}}) { #change table results { id properties articles related records( join on property "id",#edit join foreign property "module field 3 4", # edit foreign table "module 3" # edit table ) { name property(name "name") image property(name "module field 6 4") } } } } step 6) change results query authorsandtheirarticles { #renamed records(per page 20, filter {table {value "module 6"}}) { #change table results { id properties articles related records( join on property "id",#edit join foreign property "module field 3 4", # edit foreign table "module 3" # edit table ) { title property(name "module field 3 1") slug property(name "slug") } } } } step 7) liquid example liquid {% graphql authors and their articles = "authorsandtheirarticles" %} {% for author in authors and their articles records results %} {{author properties name}} articles published {% for article in author articles %} 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 to related 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