If you've done any server-side development, or used one of the multiple CMS platforms available, you know that a custom database can be a powerful tool to use. And HubSpot's HubDB is that flexible database element you have in your HubSpot CMS site. While not without its limitations, it can be a flexible, powerful, time saving tool to use (even with the starter CMS that doesn't support the dynamic CMS page option.)
Why Use a Custom Module
If you’re using either the HubSpot Drag and Drop template designer or developing using the new Theme feature, you are able to use Custom Modules to retrieve and display information from a HubDB. And, even if you are using CMS Hub Professional and Enterprise, there are many instances when it is better to use a module to display the HubDB elements rather than use the dynamic CMS page feature in conjunction with HubDB. You may want the particular HubDB data to appear on a content page with other page content, or may want the HubDB content to appear on various pages or have different data displayed based on the page it’s on. Unless your page is intended to only have content data displayed by the HubDB table (with some exceptions like global content) a custom module is your obvious solution.
Setup and Populate Your HubDB
The first step is to create and populate your HubDB table. Even if you don’t have all of your data entered, it’s ideal to have already planned the table out and are ready to create these columns (fields) in your table. Include as much, or a realistic sampling, of your data before creating the module. Be sure to publish the HubDB table so that the latest columns and rows are available to your code.
Custom Module Fields
The latest HubSpot modules have a great deal of support for custom modules, including fields that will allow you to select a table from your HubSpot site, and then select a specific row from a table.
HubDB Row Retrieval Implementation
Output a Single HubDB Row
As with other fields, the HubL Variable Name field will be the variable name you use to reference the data retrieved from the HubDB row. It will store a structure with multiple fields associated with the selected HubDB table and row, including the selected row’s columns.
To output the data retrieved from your single HubDB row, the syntax is very similar to displaying fields in the HubSpot Custom Module. The data stored in the HubDB row is referenced using the last columns field.
<div>{{ module.hubdbrow_field.columns.name }}</div>{% if module.hubdbrow_field.columns.name %}
{{ module.hubdbrow_field.columns.name }}
{% endif %}Just remember that if you populated the Columns to Fetch field, only that data will be returned. If you reference another field, HubSpot won't necessarily give you an error; it will just treat it as a NULL value.
HubDB Field Retrieval Implementation
This function takes two parameters{% set HubDB_Table_Data = hubdb_table_rows(tableId, queryString) %}
name__eq=Jim (or the shorthand name=Jim)name__contains=John
will look for John anywhere in the column name.order_gte=10
will look for any rows with the column order having a value greater than or equal to 10.name__isnull= will return all rows with no value in the column name.You will use the & to concatenate multiple items in your query string.name__contains=Jim&limit=10&orderBy=titlewill select the first 10 rows with a name that contains Jim and order the data alphabetically by the title field.
Output Multiple HubDB Rows
The hubdb_table_rows function works like any other function in HubL; a variable to store the data retrieved belongs on the left side of the equal sign. This variable will store an array of the rows returned by the hubdb_table_rows function, even if you only return a single row. You are able to use a HubL for loop to iterate through the rows and display the retrieved data.{% set HubDB_Table_Data = hubdb_table_rows(module.hubdbtable_field) %}
{% for HubDB_Row_Data in HubDB_Table_Data %}
<div>{{ HubDB_Row_Data.name }} {{ HubDB_Row_Data.title }}</div>
{% endfor %}
General Output Thoughts
The output inside the loop can be as simple or as complex as you want to make it. Most of the fields (like Text and Rich text) are simple outputs using just the variable. However, different column types (images, selects/multi-selects, etc.) may have multiple fields/data in the single column and have a structure of their own in the field. If you want to see a raw dump of the data in the row, remember that HubSpot will output a struct using the expression to output not only a single value, but the structure of a variable with all of its fields. For example:
Simple Example{% set arQuery = hubdb_table_rows(module.hubdbtable_field, 'title__like=Digital&orderBy=random()') %}<div class="customer_quotes">{% for stData in arQuery %}
<div class="quote_item">
{% if stData.image.url %}<div>
<img src="{{ stData.image.url }}" width="{{ stData.image.width }}" height="{{ stData.image.height }}" alt="{{ stData.image.altText }}"/>
</div>{% endif %}
<div>
{{ stData.name }} {{ stData.title }}
</div>
<div>
{{ stData.quote_text }}
</div>
</div>
{% endfor %}
</div>
This will search in the HubDB table designated by the module.hubdbtable_field. It will retrieve all of the rows with Digital in the title field, and return the rows in a random order. The output will include one grouping div for each record. Since the image is considered optional, it's div and output is surrounded by an "if" statement. Another child div surrounds the name and title fields. Finally, another div displays the quote_text field. Obviously, you will want to add css styling to the css of the module to properly style the display of the items on the page for the site visitors.