HubDB Product Catalog: Faceted Filtering Without a Search Service
A building-supply manufacturer runs its catalog on HubSpot CMS. The products HubDB table has 8,500 published rows and 14 columns; categories has 40 rows. Product detail pages use a dynamic page at /products/{slug}. Each detail page renders a related-products module that calls hubdb_table_rows() with the product's category ID and loops the results. The catalog listing page calls hubdb_table_rows() with no filter or limit, then uses HubL for loops and if statements to show only rows matching a ?category= query parameter. TTFB on the listing page is 4.1 seconds; the page editor times out when previewing. Marketing now wants three filters: category, material, and price band, with pagination and clean URLs. There is no external search service and no budget for one this quarter.
1. Diagnose the performance problem on the listing page and describe the concrete HubL and HubDB changes you would make first.
Read the worked answer
The listing page is slow because it asks HubDB for every published row and every column, then throws most of it away in HubL. The query result is large, the loop runs 8,500 times, and the server renders HTML for rows the visitor never sees. HubL filtering is not a database filter; it happens after the data has already been fetched and serialized.
First change: call hubdb_table_rows(tableId, query, columns, limit, offset) and pass only the columns the card needs, such as name, slug, thumbnail, and price. Add a server-side query for the active category and a page size such as 24. Use offset for pagination. Build the query string from validated URL parameters, and default to no filter only when the listing page is unfiltered. Do not call hubdb_table_rows() twice for the same data. If the template currently filters inside the loop, remove that and let the query do the narrowing. Verify that the HubDB query operators you rely on are supported in the portal before promising multi-facet behavior.
2. How would you implement the three filters and pagination robustly given HubDB query limitations, and what tradeoffs would you tell marketing about?
Read the worked answer
HubDB is not a general-purpose search index. It can filter server-side, but complex combinations, relevance, and facet counts are limited. I would implement the highest-value facet server-side - category - because it maps cleanly to a query parameter and clean URL structure, and paginate with limit and offset. Material can also be a server-side equality filter if the table stores a controlled value. Price bands are riskier because they combine comparisons and because 'under $50' and '$50-$100' need careful boundary handling.
For combinations the query API cannot express, fetch a bounded candidate set using the most selective supported filter, then apply the remaining filters in HubL. Do not fetch all 8,500 rows; fetch enough to fill the requested page plus a small buffer, and be explicit with marketing that page counts may be approximate. A cleaner alternative is to render the initial page server-side for SEO and crawlability, embed a JSON array of only the card fields for the current category, and let JavaScript apply material and price filters instantly. The tradeoff is that no-JS visitors see only the server-rendered subset, and the JSON payload must stay small. I would not promise true multi-facet counts without a search service.
3. What would you change in the related-products module, and how would you validate that the dynamic page still works when a product has no related items?
Read the worked answer
The related-products module should make one narrow HubDB call: filter by the current row's category, request only name, slug, and thumbnail, set limit=4, and exclude the current product by comparing row.id in HubL. If the table has a related column, use that instead of guessing by category.
Wrap the output in {% if related_products|length %} so a product with no matches renders nothing rather than an empty grid. Do not let an empty query fall back to an unfiltered table query; that is how a small module becomes a full-table scan. Test three cases in the dynamic page editor: a product with several matches, a product that is the only item in its category, and a product whose category ID is empty. Then publish and check the live URL, because dynamic page preview and published rendering can differ in how the row object is populated.