Tutorials

Table of contents
Close

How to join multiple HubDB tables

HubDB is relational data store represented as rows, columns and cells in a table, much like a spreadsheet. HubDB tables can be joined using the Foreign ID column type. This allows developers to render the combined data of multiple tables, joining the data and information of multiple tables. This can be very helpful when some data might be shared across multiple data stores, allowing one centralized data table of this information, which can then be accessed across multiple other HubDB table data stores.

This tutorial will detail how to combine HubDB tables using the Foreign ID field, and how to write the HubL to render the combined HubDB data. You’ll need:

  • A HubSpot account with the CMS Add-On
    • Portals with Website Starter or without the CMS Add-On do not support HubDB
    • You can check if your portal has the CMS Add-On by signing in here.
  • Some prior knowledge of HubSpot's CMS, HTML and HubL

Please note that this tutorial assumes you already have two HubDB tables created that you wish to join. Please see the HubDB documentation if you are unfamiliar with HubDB or want to create your first HubDB tables.

Add a Foreign ID column to your HubDB table

Navigate to HubDB in your HubSpot portal, and edit the table you wish to add a table join to. Select the "Add Column" button, and choose the "Foreign ID" column type.

HubDB Foreign ID

In the "Select table" field, select the HubDB table you which to join to the table you are editing. In the "Select column" field, select the column from the joining table you have selected to be visible in the Foreign ID field. 

Please note that value selected in the "Select column" only dictates which column value your see in the Foreign ID field in the HubDB user interface, all table columns are available in the actual rendering of joined HUbDB tables.

Add foreign table rows to your table's rows

Now that you have a Foreign ID HubDB column, you will have a multi-select column field on every row in your HubDB table, which allows you to select a foreign table's rows. As noted above, the "Select column" field will be used in this multi-select field to help you identify which row you are selecting from the foreign table. In the below example, the multi-select values for the "Expertise table join" field are the Name column of the foreign HubDB table. 

hubdb_foreign_id_ui

Please note it is perfectly safe to edit the "Select column" field of your Foreign ID column, this will simply update which column's values will display in the HubDB user iterface.

Render your joined HubDB table data

As noted above, all of a foreign table rows information is accessible via HubL for rendering, not just the "Select column." HUbDB foreign row data is accessible by using a nested for loop, looping through all of the foreign rows associated with an individual row.

The for loop to render foreign rows is accessed via the internal "Column name" field value set on the Foreign ID column. The HubL would look something like the following:


{% for row in hubdb_table_rows(<tableId>, <filterQuery>) %}
  the name for row {{ row.hs_id }} is {{ row.name }}
  {% for foreign_row in row.foreign_table %}
  	the name for foreign row {{ foreign_row.hs_id }} is {{ foreign_row.name }}
  {% endfor %}
{% endfor %}