Blog | How To Paginate Your HubDB Table the Right Way

HubDB is most likely one of the greatest tools in the HubSpot developer's tool belt. It allows you to create a vast number of different database-driven items like location maps, real estate listings, catalogs, team pages, etc.

Why Should You Paginate?

When the amount of data is small, it generally isn't an issue to display it all on one page, but what happens if your data sets are large, say hundreds or even thousands of rows of data in your HubDB table? This can create a problem displaying the information as well as potentially increase page load time. So, how do you get around this?

One method is to reduce the overall output of the list of data by filtering the table using URL queries.

{% set rows = hubdb_table_rows('TABLE ID NUMBER', 'category=E-Book') %}

This allows you to reduce the number of rows that get output into your website page; the downfall of this method, however, is that you end up with a filtered list of data. This isn't the end of the world, but what happens when even that isn't enough? You've filtered it, but you are still getting 1100 results.

This is where pagination comes into play.

Pagination involves taking a large data set and displaying small consumable chunks at a time so that your visitors don't get overwhelmed with the amount of data they are consuming at one time. It also allows you to deep link directly to a page that contains the data you are interested in instead of linking to the entire list all at once.

Here is How We Paginate a HubDB Table

1. Set Up Your HubDB Table the Way You Normally Would.

{% set rows = hubdb_table_rows('HUBDBTABLEID', query) %}

2. Set the Number of Items Per Page You Want to Display at a Time.

{% set batch_num = 50 %}

3. Check for the Current Page Number and Set How Many Items to Offset for the Current Page.

{% if not request.query_dict.page_num %}
   {% set page_num = 1 %}
   {% set offset_num = 0 %}
{% elif request.query_dict.page_num %}
   {% set page_num = request.query_dict.page_num %}
   {% set offset_num = page_num|add(-1) * batch_num %}
{% endif %}

4. Create a HubDB Variable That Contains the Pagination Query Parameters.

{% set query = "limit=" ~ batch_num ~ "&offset=" ~ offset_num %}

5. Query Your HubDB for loop by the Query Parameters.

<ul class="item-list">
   {% for row in rows %}
     <li class="list-item">{{ row.name }}</li>
   {% endfor %}
</ul>

6. Set Up Another Table Query to Use for the Actual Pagination.

{% set nav = hubdb_table_rows('HUBDB TABLE ID') %}

7. Get the Total Number of Pages and Pages Left.

{% set total_pages = nav|length|divide(batch_num) %}
{% set more_pages = total_pages - page_num %}

8. Set Up Pagination Rules.

{% if page_num > 1 %}
   <a class="previous-posts-link" href="{{ content.absolute_url }}?page_num={{ page_num|add(-1) }}" title="Next">Next</a>
{% endif %}

{% if more_pages == 0 %}
   {% if page_num|add(-4) >= 1 -%} <a href="{{ content.absolute_url }}?page_num={{ page_num|add(-4) }}">{{ page_num|add(-4) }}</a>{%- endif %}
{% endif %}

{% if more_pages <= 1 %}
   {% if page_num|add(-3) >= 1 %}<a href="{{ content.absolute_url }}?page_num={{ page_num|add(-3) }}">{{ page_num|add(-3) }}</a>{% endif %}
{% endif %}

{% if page_num|add(-2) >= 1 %} <a href="{{ content.absolute_url }}?page_num={{ page_num|add(-2) }}">{{ page_num|add(-2) }}</a>{% endif %}

{% if page_num|add(-1) >= 1 %}<a href="{{ content.absolute_url }}?page_num={{ page_num|add(-1) }}">{{ page_num|add(-1) }}</a>{% endif %}

<a class="active" href="{{ content.absolute_url }}?page_num={{ page_num }}">{{ page_num }}</a>

{% if page_num|add(1) <= total_pages %}<a href="{{ content.absolute_url }}?page_num={{ page_num|add(1) }}">{{ page_num|add(1) }}</a>{% endif %}

{% if page_num|add(2) <= total_pages %}<a href="{{ content.absolute_url }}?page_num={{ page_num|add(2) }}">{{ page_num|add(2) }}</a>{% endif %}

{% if page_num <= 2 %}]
   {% if page_num|add(3) <= total_pages %}<a href="{{ content.absolute_url }}?page_num={{ page_num|add(3) }}">{{ page_num|add(3) }}</a>{% endif %}
{% endif %}

{% if page_num == 1 %}
   {% if page_num|add(4) <= total_pages %}<a href="{{ content.absolute_url }}?page_num={{ page_num|add(4) }}">{{ page_num|add(4) }}</a>{% endif %}
{% endif %}

{% if total_pages > page_num %}
   <a class="next-posts-link" href="{{ content.absolute_url }}?page_num={{ page_num|add(1) }}" title="Older Posts">Previous</a>
{% endif %}

9. Test the Pagination.

It Works!

10. This is How the Final Product Will Look:

{% set batch_num = 50 %}

{% if not request.query_dict.page_num %}
   {% set page_num = 1 %}
   {% set offset_num = 0 %}
{% elif request.query_dict.page_num %}
   {% set page_num = request.query_dict.page_num %}
   {% set offset_num = page_num|add(-1) * batch_num %}
{% endif %}

{% set query = "limit=" ~ batch_num ~ "&offset=" ~ offset_num %}

{% set rows = hubdb_table_rows('HUBDBTABLEID', query) %}
<ul class="item-list">
   {% for row in rows %}
     <li class="list-item">{{ row.name }}</li>
   {% endfor %}
</ul>

{% set nav = hubdb_table_rows('HUBDB TABLE ID') %}

{% set total_pages = nav|length|divide(batch_num) %}

{% set more_pages = total_pages - page_num %}

<nav>
  {% if page_num > 1 %}
     <a class="previous-posts-link" href="{{ content.absolute_url }}?page_num={{ page_num|add(-1) }}" title="Next">Next</a>
  {% endif %}

  {% if more_pages == 0 %}
     {% if page_num|add(-4) >= 1 -%} <a href="{{ content.absolute_url }}?page_num={{ page_num|add(-4) }}">{{ page_num|add(-4) }}</a>{%- endif %}
  {% endif %}

  {% if more_pages <= 1 %}
     {% if page_num|add(-3) >= 1 %}<a href="{{ content.absolute_url }}?page_num={{ page_num|add(-3) }}">{{ page_num|add(-3) }}</a>{% endif %}
  {% endif %}

  {% if page_num|add(-2) >= 1 %} <a href="{{ content.absolute_url }}?page_num={{ page_num|add(-2) }}">{{ page_num|add(-2) }}</a>{% endif %}

  {% if page_num|add(-1) >= 1 %}<a href="{{ content.absolute_url }}?page_num={{ page_num|add(-1) }}">{{ page_num|add(-1) }}</a>{% endif %}

  <a class="active" href="{{ content.absolute_url }}?page_num={{ page_num }}">{{ page_num }}</a>

  {% if page_num|add(1) <= total_pages %}<a href="{{ content.absolute_url }}?page_num={{ page_num|add(1) }}">{{ page_num|add(1) }}</a>{% endif %}

  {% if page_num|add(2) <= total_pages %}<a href="{{ content.absolute_url }}?page_num={{ page_num|add(2) }}">{{ page_num|add(2) }}</a>{% endif %}

  {% if page_num <= 2 %}
     {% if page_num|add(3) <= total_pages %}<a href="{{ content.absolute_url }}?page_num={{ page_num|add(3) }}">{{ page_num|add(3) }}</a>{% endif %}=
  {% endif %}

  {% if page_num == 1 %}=
     {% if page_num|add(4) <= total_pages %}<a href="{{ content.absolute_url }}?page_num={{ page_num|add(4) }}">{{ page_num|add(4) }}</a>{% endif %}
  {% endif %}

  {% if total_pages > page_num %}
     <a class="next-posts-link" href="{{ content.absolute_url }}?page_num={{ page_num|add(1) }}" title="Older Posts">Previous</a>
  {% endif %}
</nav>

You can also combine this method with other queries/filters of your HubDB table like a category and/or location column.

Conclusion

HubSpot has some powerful tools, but sometimes you have to know how to use those tools for the benefit of your clients and visitors. Adding pagination to your HubDB table is a great way to improve the UX of your website and improve overall website performance as well.