Case study filters that build themselves from HubDB

Why the filters could not live in the module settings

I had already built Geobear a case study carousel that pulls matching rows from the same HubDB table. Its editor picks a sector, challenge and solution from dropdowns, and those dropdown options are hardcoded in fields.json. That is not laziness. HubSpot builds a module's editor sidebar when the module is uploaded, long before any HubDB call is allowed, so a field cannot populate its choices from a table. The carousel's source carries a comment telling whoever comes next that retagging the table means editing fields.json by hand.

That is tolerable for an editor setting that changes twice a year. It is not tolerable for a visitor facing filter bar across several countries, each with its own table and its own tags. But the markup a visitor sees is built at request time, and that is the one moment HubDB can be read. So the listing asks the table for its columns while it renders:

{% set all_rows      = hubdb_table_rows(module.table_name, 'hide=false') %}
{% set col_sector    = hubdb_table_column(module.table_name, 'segment') %}
{% set col_challenge = hubdb_table_column(module.table_name, 'challenge') %}
{% set col_solution  = hubdb_table_column(module.table_name, 'solution') %}

Each column's options become the filter's options. Nothing to transcribe, nothing to drift. The editor only picks a table.

Those four lines are also the whole data budget. HubDB functions are capped at ten calls per page and fail silently past that, with no error and no log, just empty results. So it is one bulk row fetch plus one call per filter column, and every count and match after that runs over rows already in memory. There is a comment above the block telling the next developer not to add a lookup inside a loop.

Filtering on the server, state in the URL

There is no JavaScript. Every filter option is a plain link, and filtering happens in HubL against the query string:

/case-studies?sector=social_housing&challenge=cracks_on_walls_foundations

That choice paid for itself straight away. A card tag in the carousel, a campaign email or a bookmark lands on exactly the right filtered view, because the view is the URL. Multi select is a comma separated list per filter, and each option link carries the selection with that one value toggled in or out.

The values in the URL are HubDB's own option name, not a slug I derive from the label. HubDB already stores "Warehouse/Factory" as warehouse_factory. The carousel had to slugify labels itself, and that is where it bit me: piping the slug through escape_url returned an empty string for a bare token, so every tag link quietly went out as ?sub_segment=. Using the database's own key removes our formatting from the picture entirely.

The dropdowns are <details> elements, which open and close accessibly with no script at all. Selected values show as removable chips with a "Clear all filters" link back to the bare path.

Writing predicates in a language without return values

HubL macros print text. They cannot return a boolean. So every predicate prints "1" for a hit and nothing otherwise, and callers test the result with |trim. The one rule that matters is that a miss prints nothing, never "0". Any non empty string is truthy, so a macro that printed "0" would match every row and the filter would look like it was simply ignoring the visitor.

{% macro any_hit(options, wanted_csv) %}
  {%- if not wanted_csv -%}1{%- else -%}
    {%- set ns = namespace(hit=false) -%}
    {%- for o in options -%}
      {%- if (',' ~ o.name ~ ',') in (',' ~ wanted_csv ~ ',') -%}{%- set ns.hit = true -%}{%- endif -%}
    {%- endfor -%}
    {%- if ns.hit -%}1{%- endif -%}
  {%- endif -%}
{% endmacro %}

Both sides are wrapped in commas before the in test. Without that, a visitor filtering by car_park would also get every retail_car_park row, because the shorter name sits inside the longer one. An empty selection prints "1", meaning a filter nobody has touched matches everything. Filters combine with AND across columns and OR within one.

A few more HubL quirks shaped the rest of it:

• trim takes no character argument, so the toggle macro builds its list with a leading comma and strips it with regex_replace('^,', '').
• HubDB rows do not reliably support looking up a column by a name held in a variable. A clever single count macro would silently return zero for every option, so there is one small count macro per column instead. Repetitive, and correct.
• regex_replace runs on RE2, which has no lookahead, so the thousands separator on "1,250 m²" is an anchored two group swap. It caps at 999,999, which is plenty for an area treated.

An honest empty state

The carousel has a widening fallback. If nothing matches all three tags it relaxes one at a time until it has enough cards to fill the slot, which is right for a carousel sitting on a service page. It would be wrong here. A visitor who picks a sector and a challenge and gets three jobs matching neither has been told something false. So the listing has no fallback. A combination with no matches says so, in copy the editor controls, and suggests removing a filter. When more rows match than the card limit, it says how many it is showing out of how many matched, rather than pretending the list is complete.

The card

Each card shows the sector, the solution, the title and up to two headline stats the row itself nominates, such as project duration, lift achieved, area treated or cost saving. Stats format themselves and print nothing when the column is empty or zero, since "0 days" is not a result.

The whole card is clickable through a cover link, but the cover and the "Learn more" link are both aria-hidden with tabindex="-1". Keyboard and screen reader users get one tab stop per card, on the title link, instead of three links to the same page. Cards without an image fall back to a placeholder with an empty alt, because a stock placeholder is not the case study and should not be announced as one.

The module ships hidden from the page editor's module picker, via is_available_for_new_content: false in meta.json, until the listing page is ready for it. That stops it turning up half configured on some other page in the meantime.