Pagination for table rows of hubDB

SOLVE
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 %}

 

Highlighted
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

 

6 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
Highlighted
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