Simple HubDB Pagination

HubSpot provides a really intuitive relational database tool to manage different types of structured data with ease. Once the database gets larger than a few hundred rows, you’ll need a way to paginate through the data on your website or application.

This is where a lot of people get stuck because you have to implement the logic for pagination yourself—It’s not built-in. Thankfully, HubSpot offers some really great APIs for HubDB that make implementing pagination logic fairly trivial. Here’s a simple step-by-step walk through for how we’ve done this in prior projects with a simple Model View Controller (MVC) approach.

1. The Model

First, we need to calculate how many rows we want to show per page and how many total rows there are in the table. Once we have that, we can determine how many total pages there will be by dividing the total rows by the amount we want to show per page.

Knowing these basic data points allows us to easily show the next page button if there is a next page and hide it when there is not another page. Saving these numbers as variables makes it easy to modify how many rows we want to show up per page in the future. Here’s the HubL:

{# variables #}
{% set per_page = 18 %}
{% set offset_number = 0 %}
{% set table = hubdb_table_rows(dynamic_page_hubdb_table_id) %}
{% set total_pages = table|length|divide(per_page)|round(0,"ceil") %}Code language: Twig (twig)

2. The Controller

Now that we have our data points, let’s put them to use in our Controller by querying the HubDB table so we only request the current page rows. We’ll do that by using query string parameters to the page URL and using the limit and offset parameters in the query to the table from the Get Table Rows API. Here’s what that looks like in HubL:

{# the page logic #}
{% if not request.query_dict.page %}
  {% set page_number = 1 %}
{% elif request.query_dict.page %}
  {% set page_number = request.query_dict.page %}
  {% set offset_number = (page_number - 1) * per_page %}
{% endif %}

{# the database query #}
{% set query = "limit=" ~ per_page ~ "&offset=" ~ offset_number %}
{% set table_with_query = hubdb_table_rows(dynamic_page_hubdb_table_id, query) %}Code language: Twig (twig)

3. The View

Now, let’s create the UI for the end-user to see the results. First, we’ll loop through each row in the current page. Then, we’ll add an if statement to check if there are more pages. If there are, we’ll show the next page button. Here’s the code:

{# print query and pagination button to page #}
<div id="articles">
  {% for row in table_with_query %}
    <h1>{{ row.name }}</h1>
  {% endfor %}
  {% if total_pages >= page_number %}
    <a id="load-more" href="{{ content.absolute_url }}?page={{ page_number|add(1) }}">Load More</a>														 
  {% endif %}
</div>Code language: Twig (twig)

Just for Bonus Points

Let’s add a little extra UX goodness just for fun since we’ve already learned how to make a load more posts button without a plugin in our prior blog post:

$("#articles").on("click", "#load-more", function(e) {
	e.preventDefault();
	var $button = $(this);
	var next_page = $button.attr("href");
	$button.text("... loading ...");
	$("#articles").append(
		$("<div />").load(next_page + " #articles", function() {
			$button.remove();
		})
	);
});Code language: JavaScript (javascript)

If you have a favorite pagination script or logic that you use that differs from above, share it in the comments! We love to see what the community is building.