Hola! ¡Tenemos nuestra Comunidad en Español!

Pagination for table rows of hubDB

SOLVE
Highlighted
Regular Contributor

Hi all,

is there a way to make a pagination with a table of a hubDB?

I'm developing an events calendar and I've many rows in my table.

I wish to watch only 5 rows per page and I know that there is the "offset" attribute to append to a query params but how can I make the pagination at the bottom of my page? 

I'ld like the numbers page solution but a next and previous page link could be enought 

Can anyone help me? Any ideas?

Thanks

 

Giovanni

2 Accepted solutions

Accepted Solutions
HubSpot Moderator

Hi,

 

I worked with the team on putting together a couple examples of this. Currently there isn’t anything documented on creating pagination for HubDB but hopefully these solutions will better help you create these for your events calendar.


I can think of two different ways to do this. Both of these are going to use batch filtering to create different sets of rows and this is how we can create groupings for the rows and then we can print these to the page. We can get all the rows in the table by creating a for loop like

 {% for row in rows %}

but instead with batch filtering we can create something like 

 {% for row in row|batch(3, ‘ ’) %}

The first parameter is setting the number of rows you want to set in each “batch” which will create page 1. You can read more about the batch filter here.

 

The first solution would be to set a query string for the table and then use the query string in the URL to select which batch is being printed to the page and we can also set the number of items in each batch or how many events will print to the page by using the following 

 {% set batch_num = 2 %}.
{# create our table #}
{% set table = hubdb_table_rows(673679) %}

{# set the number of items per batch #}
{% set batch_num = 2 %}

{# set the active batch #}
{% if not Query.page_num %}
    {% set page_num = 1 %}
{% elif Query.page_num %}
    {% set page_num = Query.page_num %}
{% endif %}

After we have this setup we can then create the table that will start to print out our rows to the page. This will work but what will happen is when we call loop.last we have to use ‘export_to_template_context=true’ and export to the template due to how these modules are scoped in HubL and this wouldn’t live outside due to how the scope of the module. If that is too weird what we can do is use the |length filter and then use some simple math to calculate each batch number.

<table>

    {% for row in table|batch(batch_num) %}
        {% if loop.index == page_num %}
            <tr class="batch-{{ loop.index }}">
                {% for innerRow in row %}
                    <td>{{ innerRow.name }}</td>
                {% endfor %}
            </tr>
        {% endif %}
        {% if loop.last %}
            {# if this is too weird, you could use the |length filter and then divide by the batch_num and round up #}
            {% text "number_of_rows", value="{{ loop.index }}", export_to_template_context=true %}
        {% endif %}
    {% endfor %}

    <div class="hubdb-pagination">
        {% if page_num > 1 %}<div class="previous"><a href="{{ content.absolute_url }}?page_num={{ page_num|add(-1) }}">Previous</a></div>{% endif %}
        {% if page_num < widget_data.number_of_rows.value %}<div class="next"><a href="{{ content.absolute_url }}?page_num={{ page_num|add(1) }}">Next</a></div>{% endif %}
    </div>

</table>

 

The second way to do this still uses batches but this time we will print all the rows to the document and then we will set a display: none; on each of the rows and for the row that we want to show on the page we can remove that active class we added to hide the elements and this makes them appear on the page. First we can setup the table with

 {# set the number of items per batch #}
{% set batch_num = 2 %}

{# set the active batch #}
{% set batch_num_active = 1 %}

 Once we have this setup we can then create our table with something like the below. We would be adding in the additional class and then we could set a style in CSS to hide the table rows such as tr { display: none; } and then for the items that we want to display we can add a class of active which would change the display to block and this would display the “ next page” and then hide the “previous page”. I won’t go too much into the JS Solution as this would be straightforward JS and adding the active class and running that function on a click event to change the CSS attribute.

{% for row in table|batch(batch_num) %}
        <tr class="batch-{{ loop.index }} {% if loop.index == batch_num_active %}active{%endif%}">
            {% for innerRow in row %}
                <td>{{ innerRow.name }}</td>
            {% endfor %}
        </tr>
        {% if loop.last %}
            {# if this is too weird, you could use the |length filter and then divide by the batch_num and round up #}
            {% text "number_of_rows", value="{{ loop.index }}", export_to_template_context=true %}
        {% endif %}
    {% endfor %}

 

HubSpot Moderator

@mkthgrd I went through and found a way to still make the above code work with a custom module instead of a Custom HubL module. 

 

{# create our table #}
{% set table = hubdb_table_rows(673679) %}

{# set the number of items per batch #}
{% set batch_num = 2 %}

{# set the active batch #}
{% if not Query.page_num %}
    {% set page_num = 1 %}
{% elif Query.page_num %}
    {% set page_num = Query.page_num %}
{% endif %}

<table>

    {% for row in table|batch(batch_num) %}
        {% if loop.index == page_num %}
            <tr class="batch-{{ loop.index }}">
                {% for innerRow in row %}
                    <td>{{ innerRow.name }}</td>
                {% endfor %}
            </tr>
        {% endif %}
        {% if loop.last %}
          <div class="hubdb-pagination">
              {% if page_num > 1 %}<div class="previous"><a href="{{ content.absolute_url }}?page_num={{ page_num|add(-1) }}">Previous</a></div>{% endif %}
              {% if page_num < module.number_of_rows %}<div class="next"><a href="{{ content.absolute_url }}?page_num={{ page_num|add(1) }}">Next</a></div>{% endif %}
          </div>
        {% endif %}
    {% endfor %}



</table>

The main change here was I moved the blog-pagination div into the loop.last conditional so we don't need to export  to the template context. The next step would be to add a field of us to hold the number of the rows we want. 

HubDB_pagi.png

 

The above image shows how I added in a field called Number of rows and I set the default to three and then used that in my conditional above. Theres not a long of new code but rather modifying this to work with custom modules so that the above should work for you as we are now using fields instead of trying to export to the template. 

 

Let me know if you have any additional questions on this

 

10 Replies
HubSpot Moderator

Hi,

 

I worked with the team on putting together a couple examples of this. Currently there isn’t anything documented on creating pagination for HubDB but hopefully these solutions will better help you create these for your events calendar.


I can think of two different ways to do this. Both of these are going to use batch filtering to create different sets of rows and this is how we can create groupings for the rows and then we can print these to the page. We can get all the rows in the table by creating a for loop like

 {% for row in rows %}

but instead with batch filtering we can create something like 

 {% for row in row|batch(3, ‘&nbsp;’) %}

The first parameter is setting the number of rows you want to set in each “batch” which will create page 1. You can read more about the batch filter here.

 

The first solution would be to set a query string for the table and then use the query string in the URL to select which batch is being printed to the page and we can also set the number of items in each batch or how many events will print to the page by using the following 

 {% set batch_num = 2 %}.
{# create our table #}
{% set table = hubdb_table_rows(673679) %}

{# set the number of items per batch #}
{% set batch_num = 2 %}

{# set the active batch #}
{% if not Query.page_num %}
    {% set page_num = 1 %}
{% elif Query.page_num %}
    {% set page_num = Query.page_num %}
{% endif %}

After we have this setup we can then create the table that will start to print out our rows to the page. This will work but what will happen is when we call loop.last we have to use ‘export_to_template_context=true’ and export to the template due to how these modules are scoped in HubL and this wouldn’t live outside due to how the scope of the module. If that is too weird what we can do is use the |length filter and then use some simple math to calculate each batch number.

<table>

    {% for row in table|batch(batch_num) %}
        {% if loop.index == page_num %}
            <tr class="batch-{{ loop.index }}">
                {% for innerRow in row %}
                    <td>{{ innerRow.name }}</td>
                {% endfor %}
            </tr>
        {% endif %}
        {% if loop.last %}
            {# if this is too weird, you could use the |length filter and then divide by the batch_num and round up #}
            {% text "number_of_rows", value="{{ loop.index }}", export_to_template_context=true %}
        {% endif %}
    {% endfor %}

    <div class="hubdb-pagination">
        {% if page_num > 1 %}<div class="previous"><a href="{{ content.absolute_url }}?page_num={{ page_num|add(-1) }}">Previous</a></div>{% endif %}
        {% if page_num < widget_data.number_of_rows.value %}<div class="next"><a href="{{ content.absolute_url }}?page_num={{ page_num|add(1) }}">Next</a></div>{% endif %}
    </div>

</table>

 

The second way to do this still uses batches but this time we will print all the rows to the document and then we will set a display: none; on each of the rows and for the row that we want to show on the page we can remove that active class we added to hide the elements and this makes them appear on the page. First we can setup the table with

 {# set the number of items per batch #}
{% set batch_num = 2 %}

{# set the active batch #}
{% set batch_num_active = 1 %}

 Once we have this setup we can then create our table with something like the below. We would be adding in the additional class and then we could set a style in CSS to hide the table rows such as tr { display: none; } and then for the items that we want to display we can add a class of active which would change the display to block and this would display the “ next page” and then hide the “previous page”. I won’t go too much into the JS Solution as this would be straightforward JS and adding the active class and running that function on a click event to change the CSS attribute.

{% for row in table|batch(batch_num) %}
        <tr class="batch-{{ loop.index }} {% if loop.index == batch_num_active %}active{%endif%}">
            {% for innerRow in row %}
                <td>{{ innerRow.name }}</td>
            {% endfor %}
        </tr>
        {% if loop.last %}
            {# if this is too weird, you could use the |length filter and then divide by the batch_num and round up #}
            {% text "number_of_rows", value="{{ loop.index }}", export_to_template_context=true %}
        {% endif %}
    {% endfor %}

 

Regular Contributor

Thank you very mutch 

It's a great solution. 

Now I will have to integrate it into a filter structure with a queryparam like this:

{% set queryparam = "&orderBy=-event_date" %} 
<h4 style="text-align: center;">Filter the events</h4>

<p style="text-align: center;">You are searching for:
{% if request.query_dict.event_type in ["1", "2", "3"] %}
{% set queryparam = queryparam ~ "&event_type="~request.query_dict.event_type|urlencode %}
{% endif %}

...

and thi is the form for the filter

 

<form id="form_id" method="get">

<div class="row-fluid">

<div class="span3">
<div>Event type: </div>
<p>
<select name="tipo_evento" form="form_id" >
<option value="show-all">Show all</option>
{% set types = hubdb_table_column(671570, "event_type").options %}
{% for choice in types %}
{% set type_list = type_list~choice.id|list%}
{% if choice.id == request.query_dict.event_type%}
<option selected="selected" value="{{ choice.id }}">{{ choice.name }}</option>
{% else %}
<option value="{{ choice.id }}">{{ choice.name }}</option>
{% endif %}
{% endfor %}
</select>
</p>
</div>

...

You can see an example here http://www.think-inbound.com/calendario-eventi

 

Giovanni

Reply
0 Upvotes
New Contributor

Hi @jzilch,

 

I tried to create an HubDB pagination with your code within a custom module.

 

I have the following message in the editor: export_to_template_context is not usable in custom modules.

 

Because of this, the pagination is not working...

 

Do you have an idea to create this feature in the new CMS?

 

Thanks a lot!

Justin

 

Reply
0 Upvotes
New Contributor

Hello,

 

I tried again to use your solution, but:

 

  • The expression widget_data.number_of_rows.value does not seem to work and does not generate the link "Next".
  • We can't use anymore export_to_template_context in custom modules.

 

Do you have an idea how to solve these issues?

 

Thanks a lot for you help!

Reply
0 Upvotes
HubSpot Moderator

@mkthgrd I went through and found a way to still make the above code work with a custom module instead of a Custom HubL module. 

 

{# create our table #}
{% set table = hubdb_table_rows(673679) %}

{# set the number of items per batch #}
{% set batch_num = 2 %}

{# set the active batch #}
{% if not Query.page_num %}
    {% set page_num = 1 %}
{% elif Query.page_num %}
    {% set page_num = Query.page_num %}
{% endif %}

<table>

    {% for row in table|batch(batch_num) %}
        {% if loop.index == page_num %}
            <tr class="batch-{{ loop.index }}">
                {% for innerRow in row %}
                    <td>{{ innerRow.name }}</td>
                {% endfor %}
            </tr>
        {% endif %}
        {% if loop.last %}
          <div class="hubdb-pagination">
              {% if page_num > 1 %}<div class="previous"><a href="{{ content.absolute_url }}?page_num={{ page_num|add(-1) }}">Previous</a></div>{% endif %}
              {% if page_num < module.number_of_rows %}<div class="next"><a href="{{ content.absolute_url }}?page_num={{ page_num|add(1) }}">Next</a></div>{% endif %}
          </div>
        {% endif %}
    {% endfor %}



</table>

The main change here was I moved the blog-pagination div into the loop.last conditional so we don't need to export  to the template context. The next step would be to add a field of us to hold the number of the rows we want. 

HubDB_pagi.png

 

The above image shows how I added in a field called Number of rows and I set the default to three and then used that in my conditional above. Theres not a long of new code but rather modifying this to work with custom modules so that the above should work for you as we are now using fields instead of trying to export to the template. 

 

Let me know if you have any additional questions on this

 

New Contributor

Awesome @jzilch!

 

Thank you for the solution, this is exactly what I was looking for!

Reply
0 Upvotes
New Contributor

Hi @jzilch!

 

Do you know if there is a way to create a pagination more SEO friendly with HubDB?

 

I would like to have links like mydomain.com/resources/page/2 instead of mydomain.com/resources/page/2 for example.

 

Looking forward to hearing from you Smiley Happy

Reply
0 Upvotes
Top Contributor

@jzilch - Can you share your "hubdb-pagination" class css styling? Any idea how to incorporate this code with multiple query filters? 

Reply
0 Upvotes
HubSpot Moderator

Hi @leckerman,

 

I can't speak to the CSS that was used here. In fact (and I could be wrong), but it's possible that there was never any default styling built out for this. Sometimes we're more concerned with just getting the functionality working.

 

As for the multiple query parameters, are you asking about filtering the table rows in addition to grabbing the batch number from the query string? You should be able to just grab any additional query string parameters through HubL, and then add those as a filter on the HubDB table rows function. HubL generates a dict for the query string parameters. So for example if you had a query string parameter of "type", you could grab its value in HubL using {{ Query.type }} . So for example if the URL is https://www.domain.com/hubdb-page?page_num=1&type=event, then {{ Query.type }} would evaluate to event .

 

We have some documentation on HubDB filtering here: https://designers.hubspot.com/docs/tools/hubdb. This is also linked from that page, but the full list of available filters can be found here: https://developers.hubspot.com/docs/methods/hubdb/v2/get_table_rows, since the http API filtering works the same way. You'll probably want to first create a variable for your query/filter:

 

{% set filter = "event="~Query.type~"&"~"location="~Query.location %}

^This is just an example, but if you're having trouble with something specific, let me know. Then you'd just add that filter into the hubdb_table_rows() function like so:

 

{% for row in hubdb_table_rows(XXXX, filter) %}

The rest of the code above should be the same, assuming you're using the batch filter. But this would enable you to optionally use filters, and then continue to page through like before.

 

Let me know if that helps?

 

Leland Scanlan

HubSpot Developer Support
Reply
0 Upvotes
Top Contributor

Hi @lscanlan,

 

Thanks for your response. Yes, I am trying to figure out how to filter the table rows (by location and month) in addition to adding pagination. I got the pagniation to work with all rows, but can't get it to work if I select either of the two filters I have. I'm guessing I'm not combining queries or filters somewhere correctly. Also, do I need to add something to the pagination URL string to get the filters to print to the URL as well?

 

The code in red the is NEW code I added to the module to try and get the pagination working... any idea where I might be going wrong?

 

{# set the number of items per batch #}
{% set batch_num = 6 %}

{# 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 %}

 

<!-- sets the different query parameters using submitted input for hubdb query -->
{% set locationquery = "limit=" ~ batch_num ~ "&offset=" ~ offset_num ~ "&date_and_time_start__gt=0s&orderBy=date_and_time_start" %}
{% if request.query_dict.location in ["1", "2", "3", "4", "5", "6", "7", "8", "10"] %}
{% set locationquery = locationquery ~ "&location="~request.query_dict.location|urlencode %}
{% endif %}
{% if request.query_dict.location == "show-all" %}
{% set locationquery = locationquery %}
{% endif %}
{% set month_startquery = "" %}
{% if request.query_dict.month_start in ["1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12"] %}
{% set month_startquery = month_startquery ~ "&month_start="~request.query_dict.month_start|urlencode %}
{% endif %}
{% if request.query_dict.month_start == "show-all" %}
{% set month_startquery = month_startquery %}
{% endif %}
{% set filter = "location=" ~ locationquery ~ "&" ~ "month_start=" ~ month_startquery %}

 

<!-- Set Up Pagination Rules -->
{% set nav = hubdb_table_rows(XXXXXX) %}

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

<div class="hubdb__pagination" style="clear: both;">
<nav>
{% if page_num > 1 %}
<a class="previous__page" href="{{ content.absolute_url }}?page_num={{ page_num|add(-1) }}" title="Previous">&laquo; Previous</a>
{% endif %}
{% if total_pages > page_num %}
<a class="next__page" href="{{ content.absolute_url }}?page_num={{ page_num|add(1) }}" title="Next">Next &raquo;</a>
{% endif %}
</nav>
</div>

<div class="resource-row-header row-fluid">
<div class="resource-cell-first span3">COURSE NAME</div>
<div class="resource-cell span3">START DATE &amp; TIME</div>
<div class="resource-cell span1">LENGTH</div>
<div class="resource-cell span2">LOCATION</div>
<div class="resource-cell-last span3" style="text-align: center">REQUEST A SEAT</div>
</div>

 

{# Get rows from HubDB #}
{% set table = hubdb_table_rows(XXXXXX, locationquery + month_startquery, filter) %}

{% if table == [] %}
<p style="text-align: center;"><br>Sorry, there are currently no courses scheduled in this location and/or month.<br>Try changing your filter and search again or contact our training coordinator to <a href="XXX">request a course</a>.<p style="text-align: center;">{% cta guid="{{ module.cta_field }}" %}</p>
{% else %}
{% endif %}
{% for row in table %}
<div class="resource-row-container">
<div class="resource-row row-fluid">
<div class="resource-row-info">
<div class="resource-cell-first span3"><p><strong><a href="/courses/{{ row["course_url"].name }}">{{ row["course_name"].name|striptags }}</a></strong></p></div>
<div class="resource-cell span3">{{ row.date_and_time_start|datetimeformat('%a, %B %e, %Y at %l:%M %p') }}</div>
<div class="resource-cell span1">{{ row["course_length"].name }}</div>
<div class="resource-cell span2">{{ row["city_and_state"].name }}</div>
<div class="resource-cell-last span3"><h4 style="text-align: center;"><a class="link=scroll" href="XXX"><strong>REGISTER</strong><a/></h4></div>
</div>
</div>
</div>
{% endfor %}

 

 

Reply
0 Upvotes