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
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 %}
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
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
Hello,
I tried again to use your solution, but:
Do you have an idea how to solve these issues?
Thanks a lot for you help!
@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.
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
Awesome @jzilch!
Thank you for the solution, this is exactly what I was looking for!
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 
@jzilch - Can you share your “hubdb-pagination” class css styling? Any idea how to incorporate this code with multiple query filters?
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: HubDB - HubSpot docs. 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?
Hi @lscanlan - I was able to get it working with a combination of a few different references/posts on pagination that I found. Just stuck on one thing… I can’t get the pagination to hide if the results are equal or less than the batch_num = 6 (in other words hide if the filters show equal to or less than 6 results).
Right now all 8 pages (6 results per page) are showing for all combinations of the filters, even if there is only 1 result. Then pages 2-8 say “sorry there are no results”. These are the pagination rules I have in place. Is there an “if statement” that needs to be added?
<!-- Set Up Pagination Rules -->
{% set total_pages = nav|length|divide(batch_num) %}
{% set more_pages = total_pages - page_num %}
{% if request.query_dict %}
{% set paginationPath = request.path + “?location=” + request.query_dict.location|urlencode +“&month_start=” + request.query_dict.month_start|urlencode %}
{% else %}
{% set paginationPath = request.path + “?&page_num=” + page_num %}
{% endif %}
<div class=“hubdb__pagination row-fluid” style=“clear: both;”>
<nav class=“span12” style=“text-align: center; padding-bottom: 30px;”>
{% if page_num > 1 %}
<a class=“previous__page” href=“{{ paginationPath }}&page_num={{ page_num|add(-1) }}” title=“Previous”>\« Previous</a>
{% endif %}
{% if more_pages == 0 %}
{% if page_num|add(-4) >= 1 -%} <a href=“{{ paginationPath }}&page_num={{ page_num|add(-4) }}”></a>{%- endif %}
{% endif %}
{% if more_pages <= 1 %}
{% if page_num|add(-3) >= 1 %}<a href=“{{ paginationPath }}&page_num={{ page_num|add(-3) }}”>{{ page_num|add(-3) }}</a>{% endif %}
{% endif %}
{% if page_num|add(-2) >= 1 %} <a href=“{{ paginationPath }}&page_num={{ page_num|add(-2) }}”>{{ page_num|add(-2) }}</a>{% endif %}
{% if page_num|add(-1) >= 1 %}<a href=“{{ paginationPath }}&page_num={{ page_num|add(-1) }}”>{{ page_num|add(-1) }}</a>{% endif %}
<a class=“active” href=“{{ paginationPath }}&page_num={{ page_num }}”>{{ page_num }}</a>
{% if page_num|add(1) <= total_pages %}<a href=“{{ paginationPath }}&page_num={{ page_num|add(1) }}”>{{ page_num|add(1) }}</a>{% endif %}
{% if page_num|add(2) <= total_pages %}<a href=“{{ paginationPath }}&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=“{{ paginationPath }}&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=“{{ paginationPath }}&page_num={{ page_num|add(4) }}”>{{ page_num|add(4) }}</a>{% endif %}
{% endif %}
{% if total_pages > page_num %}
<a class=“next__page” href=“{{ paginationPath }}&page_num={{ page_num|add(1) }}” title=“Next”>Next \»</a>
{% endif %}
</nav>
</div>
Throwing this up here for posterity.
I ended up using something along these lines - this is based off of Jzilch’s example also in this thread.
This version doesn’t need the number of page’s specified, it just counts calculates the number of pages using the table legnth. That may not have been possible a few years ago.
{# create our table #}
{% set table = hubdb_table_rows(3487890) %}
{# set the number of items per batch #}
{% set batch_num = 5 %}
{# set the number of pages #}
{% if table|length % batch_num == 0 %}
{% set number_of_pages = (table|length / batch_num) %}
{% else %}
{% set number_of_pages = (table|length / batch_num)|round(0, 'floor') + 1 %}
{% endif %}
{# set the active batch #}
{% if Query.page_num %}
{% set page_num = Query.page_num %}
{% else %}
{% set page_num = 1 %}
{% endif %}
{% for row in table|batch(batch_num) %}
{% if loop.index == page_num %}
{% for innerRow in row %}
{% if innerRow %}
<div>{{ innerRow.any_info_you_set_in_db }}</div>
{% endif %}
{% endfor %}
{% endif %}
{% if loop.last %}
{% if page_num > 1 %}
<a href="{{ content.absolute_url }}?page_num={{ page_num|add(-1) }}">Previous</a>
{% endif %}
{% if page_num < number_of_pages %}
<a href="{{ content.absolute_url }}?page_num={{ page_num|add(1) }}">Next</a>
{% endif %}
{% endif %}
{% endfor %}
Adding onto Alex’s answer If you want actual numbers in your pagination in adition to the Previous and Next links, you can do something like this -
<div class="pagination_wrapper">
{% if loop.last %}
{% if page_num > 1 %}
<a href="{{ content.absolute_url }}?page_num={{ page_num|add(-1) }}">Previous</a>
{% endif %}
<ul class="pagination">
{% for num in range(0,number_of_pages) %}
{% if loop.index == page_num %}
<li class="active-page">{{loop.index}}</li>
{% else %}
<li><a href="{{ content.absolute_url }}?page_num={{ loop.index }}">{{loop.index}}</a></li>
{% endif %}
{% endfor %}
</ul>
{% if page_num < number_of_pages %}
<a href="{{ content.absolute_url }}?page_num={{ page_num|add(1) }}">Next</a>
{% endif %}
{% endif %}
</div>