Sort/Reorder Hubdb rows on listing page

[Anton101]

Hi all,

I'm busy developing a resources listing page making use of Hubdb. Problem is every time a new content asset (eBook, Infographic etc.) gets added it displays at the bottom of the listing page which isn't ideal. The most recent content download should be displayed first followed by the rest. Is there a way to sort the table row items according to date created?

Below the module I've created to pull in the Hubdb data:

<!-- sets the different query parameters using submitted input for hubdb query -->
{% set queryparam = "" %}
{% if request.query_dict.solution in ["1", "2", "3", "4"] %}
{% set queryparam = queryparam ~ "&solution="~request.query_dict.solution|urlencode %}
{% endif %}
{% if request.query_dict.solution == "show-all" %}
{% set queryparam = queryparam %}
{% endif %}

<div class="row">
{% set table = hubdb_table_rows('674581', queryparam ) %}

{% if table == [] %}

{% else %}
{% for row in hubdb_table_rows('674581', "&orderBy=-a_date") %}
<div class="col-md-4">
<div class="holder-box">
<div class="{{ row["type"].name }}">
<div><img src="{{ row["listing_image"].url}}"></div>
<div>
<h5>{{ row["listing_heading"] }}</h5>
<p>{{ row["listing_intro"] }}</p>
<div class="listing-btn"><a class="btn-primary" href="{{ row["listing_url"] }}">{{ row["button_text"] }}</a></div>
</div>
</div>
</div>
</div>
{% endfor %}

{% endif %}
</div>

Any help would be much appreciated.

Thanks,

Anton

[Crystal_Hopper]

If coding is not your thing, here's another way to solve this problem until Hubspot allows rows to be reordered in the UI:

• Populate all your content in a HubDB table
• Export all the content to a CSV
• Delete all the rows in the HubDB table
• Remove unnecessary columns in CSV export file
  • i.e. hs_id, hs_path, hs_created_at, hs_name, hs_child_table_id
  • Keep your custom columns
• Reorder the rows in the CSV File
  • Use the sort or filter functions to do this
• Save the CSV file
• Import the CVS file back to the now empty HubDB

I realize it is still quite a few steps but I just did this for several HubDB tables that store event details. Something was causing the list of events to not display in the order I entered them in or based on the chronological date. So, I did the steps above and now everything is the way I want it.

[Footem]

Hey Crystal,

Thank you for sharing this.  I have a request to reorder a HubDB table so that newer resources appear early in the sort order. I followed the steps you've listed, then replaced the old rows with the new, then re-published my table. Inside the CMS, I see the items listed as desired, but on the front end, the order has not changed. A couple of additional notes: 1) I did one thing slightly differently from your solution which was to upload and replace all rows as opposed to deleting all rows first (there are 190 rows). 

2) On the front end, it looks like they are ordered alphabetically so I'm wondering if there's something in the code that's forcing it to show that way. (?)

Any thoughts?

Thanks!

Matt

[Crystal_Hopper]

@Footem sorry for the delay. Yes, there is probably something in the code of the page resorting the data. This did happen with one of my pages that was supposed to be pulling in HubDB data. 

I'm glad the upload and replace option worked for you. I'm going to have to give that one another go. 

[jeffreydabbott]

Hi Crystal, 

This was just the kind of non-technical fix I was hoping to find.  However, whether I exported to CSV or XLS, it dumped all the data into a single column called "event description."  This made it impossible to sort, or reimport.  So, I ended up restoring the private version (so nice to have that option). 

Any thoughts on where I went wrong? 

[Crystal_Hopper]

Hi Jeffery!

Does your HubDB have multiple columns? I don't see any export options, so I don't think you are doing anything wrong at all. If your HubDB has multiple columns of data and they are not exporting to multiple columns in the spreadsheet file, my suggestion would be to contact Hubspot support.

I just did an export of one of my HubDBs and it came out fine in the spreadsheet. So I can confirm the export tool is working for me. I'm not sure why it would take all your data and dump it in to a single column. 

[ashleyidesign]

More info on ordering is here: https://developers.hubspot.com/docs/methods/hubdb/get_table_rows

"Return the rows in the natural order of the specified column. You can reverse the sort by adding a - to the column name: orderBy=-bar."

[Graham_USMC]

Thanks you Ashley! I had to reverse the orer of a numbered column and the minus sign did the trick!!

[BenStark]

This link is broken now.

[Anton101]

@ashleyidesign Thanks Ashley, this does seem to order the rows according to published date but when adding the orderBy= it messes up the filtering queryparam. Any ideas as to why this is?

<!-- sets the different query parameters using submitted input for hubdb query -->
{% set queryparam = "" %}
{% if request.query_dict.solution in ["1", "2", "3", "4"] %}
{% set queryparam = queryparam ~ "&solution="~request.query_dict.solution|urlencode %}
{% endif %}
{% if request.query_dict.solution == "show-all" %}
{% set queryparam = queryparam %}
{% endif %}

<div class="row">
{% set table = hubdb_table_rows('674581', queryparam ) %}

{% if table == [] %}

{% else %}
{% for row in hubdb_table_rows('674581', "&orderBy=-publish_date") %}
<div class="col-md-4">
<div class="holder-box">
<div class="{{ row["type"].name }}">
<div><img src="{{ row["listing_image"].url}}"></div>
<div>
<h5>{{ row["listing_heading"] }}</h5>
<p>{{ row["listing_intro"] }}</p>
<div class="listing-btn"><a class="btn-primary" href="{{ row["listing_url"] }}">{{ row["button_text"] }}</a></div>
</div>
</div>
</div>
</div>
{% endfor %}

{% endif %}

[martaz]

Hello,

Were you able to solve this issue?

Thanks, Marta

[Crystal_Hopper]

If you are still looking for a solution, I posted a non-coding answer to this thread.

[Anton101 · Accepted solution]

For anyone interested I solved it by adding a date column to the table rows and adding an orderBy= parameter to the row if statement.