Overview
HubDB is an important tool when it comes to building sites on HubSpot. Aside from just looping through the rows in your table -- there are reasons why you might want to loop through column data:
- building front-end filters
- custom sorting
- custom filtering
- many more kewl thangs!
Let's make sure we are pro's at getting any job done when it comes to hubDB.
Allons y! (Let's go!)
Basics
HubSpot provides us with a few different ways to filter and sort, via built in functions and filters, natively. Those are always good places to start for basic filtering needs:
ex: You have a hubDB table filled with team members for your client. One column in the table is a select dropdown with various roles. Each team member can only be assigned a single role. The client wants a module that will display all team members who have a specific role.
Personally I always use the "filter_query" in the hubdb_table_rows() function. Why? Old habits die hard? No. Mostly, I've found selectattr to be a bit buggy and not quite work how I would expect. It's a bit limited and typically doesnt work with a comperably formatted call to the hubdb_table_rows() function.
Because of this fact -- I don't recommend using it unless you want to be frustrated. That being said -- HubSpot sill recommends using it and it's worth mentioning.
{# Using the filter_query #}
{% set table_info = hubdb_table(xxxxxxxx, "role=key_pusher" ) %}The cool thing about this is that we can also use it for multi-select options (but again this only works the the filter_query method)
{# Using the filter_query #}
{# Will return all rows where team members have the role of "key_pusher" or "ceo" #}
{% set table_info = hubdb_table(xxxxxxxx, "role__icontains=key_pusher,ceo" ) %}Awesome -- this works really well for cases where we want to display ALL of the filtered results once. But what happens if our team members, maybe sales reps, are grouped by states. Where a single representative might be allocated to multiple states?
Grouping by column value(s)
New problem: Our hubDB table is full of sales reps who are can be assigned to multiple states. Multiple sales reps might also be assigned to the same state. We want all of the sales reps to be grouped by whatever state they belong.
This is a bit more complicated because we cannot just do a single filter_query like we did in the basic example because it would only return essentially our first state. We can't make a request for each possible state. That would be horribly inefficient. But, what we can do is create our own business logic that will grab all of the rows and filter them based on our own terms.
Here is the overarching workflow:
- Grab all row data
- Grab all of our potential filters (States in this case)
- Loop through each filter option to generate group
- Loop through each row and see if it belongs in that group.
- Display info
- Celebrate.
1. Grab all row data
Easy peasy.
{% set rows = hubdb_table_rows(xxxxxxxxx) %}2. Grab all of our potential filters/groups(states)
{# grabs all of the table info #}
{% set table_info = hubdb_table(xxxxxxxxx) %}
{# create a blank array for us to use later on #}
{% set states = [] %}
{# Loop through table columns #}
{% for item in table_info.columns %}
{# If we are on the state column #}
{% if item.name == 'states' %}
{# Loop through the available options #}
{% for state in item.options %}
{# append the state name in the states array #}
{%do states.append(state.name)%}
{% endfor %}
{% endif %}
{% endfor %}3. Generate and populate groups
This next snippet is the meat of the whole process. I would recommend reading this from bottom to top, as that is the flow of the macros.
{% macro build_row(row) %}
{#
this is where you add whatever data from the row
that you want to have listed in the group
in this case we are just adding the name
#}
{{row.name}}<br><br>
{% endmacro %}
{% macro build_filter_group(state)%}
{# Loop through all rows #}
{% for row in rows %}
{# set boolean varable to inform us whether
or not to include in state group
#}
{% set include_in_group = {"include": false} %}
{# check to see if the state is contianed in the row #}
{% for row_state in row.states %}
{#
if the row contains the correct state
include the row in the grouping
#}
{% do include_in_group.update({"include": true}) if row_state.name == state %}
{% endfor %}
{# If it is then include it in the group #}
{{build_row(row) if include_in_group.include}}
{% endfor %}
{% endmacro %}
{% macro build_container(states) %}
<seciton class="states-container">
{# Loops through all of the possible states #}
{% for state in states %}
<div class="state-group" data-filter="{{state}}">
{{build_filter_group(state)}}
</div>
{% endfor %}
</seciton>
{% endmacro %}
{# Kicks off the whole process #}
{{ build_container(states) }}Final File
All together we get a file that looks something like this
{% set rows = hubdb_table_rows(xxxxxxxxx) %}
{% set table_info = hubdb_table(xxxxxxxxx) %}
{# create a blank array for us to use later on #}
{% set states = [] %}
{# Loop through table columns #}
{% for item in table_info.columns %}
{# If we are on the state column #}
{% if item.name == 'states' %}
{# Loop through the available options #}
{% for state in item.options %}
{# append the state name in the states array #}
{%do states.append(state.name)%}
{% endfor %}
{% endif %}
{% endfor %}
{% macro build_row(row) %}
{#
this is where you add whatever data from the row
that you want to have listed in the group
in this case we are just adding the name
#}
{{row.name}}<br><br>
{% endmacro %}
{% macro build_filter_group(state)%}
{# Loop through all rows #}
{% for row in rows %}
{# set boolean varable to inform us whether
or not to include in state group
#}
{% set include_in_group = {"include": false} %}
{# check to see if the state is contianed in the row #}
{% for row_state in row.states %}
{#
if the row contains the correct state
include the row in the grouping
#}
{% do include_in_group.update({"include": true}) if row_state.name == state %}
{% endfor %}
{# If it is then include it in the group #}
{{build_row(row) if include_in_group.include}}
{% endfor %}
{% endmacro %}
{% macro build_container(states) %}
<seciton class="states-container">
{# Loops through all of the possible states #}
{% for state in states %}
<div class="state-group" data-filter="{{state}}">
{{build_filter_group(state)}}
</div>
{% endfor %}
</seciton>
{% endmacro %}
{# Kicks off the whole process #}
{{ build_container(states) }}The final output of this kind of flow would be html that resembles:
<section class="states-container">
<div class="state-group" data-filter="Wisconsin">
Jon Doe<br><br>
Jon Doe 2<br><br>
</div>
<div class="state-group" data-filter="Washington">
Jane Doe<br><br>
</div>
</section>Of course you can feel free, and will likely need, to modify this code in order to fit your needs. But I think the gist of all this is pretty straight forward. And if it's not feel free to reach out with any questions that you may have.
Final thoughts
There are a lot more nuggets in this post than may meet the eye. A lot of these macros or techniques can be used to do a lot more that what they are doing here in this post. For example, if you're looking to create a dropdown on the front end for a javascript filter, you can use the same method used to grab all of the states to dynamically generate a select dropdown. Cool stuff!
Happy coding!