Accessing row in HubDB (alternatives to WHERE clause)

SOLVE
Highlighted
Occasional Contributor

I have a database called 'Products'. Products have the following rows:

  • ID (Hubspot generated)
  • Page Title
  • Page Path
  • Product ID
  • Name
  • Price

I'm trying to extract the price from this database to use in a custom module.

 

The custom module, `Products Hero` is displayed on several pages (for several different products). 

 

Since HubDB isn't a fully fledged relational database, I'm struggling to query without the WHERE clause. 

 

What I want to type:

SELECT all FROM products WHERE product_id is equal to {{ module.link_product }} (which is a field I've created to link the product  page to its DB row).

 

However, since HubSpot doesn't support this query type, I have the following:

 

<div class="products__hero" id="top" style="background-color:{{ module.background_color }}; color: {{ module.text_color }}">
  <div class="container container-custom products__hero--wrapper" style="color:{{ module.text_color }};">

    <!-- get products table-->
    {% set query = hubdb_table_rows([table_id], queryparam) %}
    
      <!-- loop though -->
      {% for row in query %}

        <!-- Get all data and assign to vars -->
        {% set product_name = row["Name"] %}
        {% set product_price = row["Price"] %}
        {% set product_id = row["product_id"] %}
        {% set rows_returned = loop.index %}

        <h1>test:{{ product_price }} {{ product_id }}</h1>
  
        <div class="col-xs-12 col-sm-4">
          <div id="img" class="products__hero--img" style="background-image: url({{ module.hero_image.src }})"></div>
        </div>
        <div class="col-xs-12 col-sm-8">
          <h1>{{ module.hero_header }}</h1>
          <p>{{ module.hero_content }}</p>
          {% if module.add_button == 'yes' %}
          <div class="btn-holder"><a href="{{ module.button_link }}" class="button__core {{ module.button_color }}" style="background-color: "> {{ module.price }}</a></div>
          {% endif %}
        </div> 
    
      {% endfor %}

  </div>
</div>

Note:  [table_id] in the snippet is only a placeholder, I have my database ID in the real code.

 

Example (in practise).

  • I have two rows (two products) in table `products`.
    • Product ID: 1, Name: Mini, Price: €199
    • Product ID: 2, Name: Plus, Price: €269
  • I have the custom module `Products Hero` on two page templates:
    • Mini
    • Plus
  • On each page, I'm editing the `link_product` field type to match the page it's on. 
    • link_product: Choice field where the label equals the product name and value is equal to it's product id. I.e. Mini will have the value 1, Plus 2 etc.

 

What I'm currently seeing:

My current results, with the above code, is that it's showing data (it is showing the price and ID). However it is:

  1. Looping through all the products in the table and printing it X amount of times. So I have two products in Products, so my content in `Products Hero` is being printed twice. This is of course because my content is within the foor loop, but I have tired `limit=1` on the query and it doesn't work.
  2. It doesn't link the products up according to what I've selected in the choice field. I.e. the default value of `link_product` is "Mini" (so ID 1). But on the Plus page template, when I map `link_product` to Plus, it still shows the default (it's always getting the first row in the table). This is where I would want to use the WHERE clause, but can't.

Any suggestions?

Reply
0 Upvotes
1 Accepted solution

Accepted Solutions
HubSpot Moderator

Hi @Amit_95,

 

We should be able to figure this out for you. If I understand what you're trying to do, you're applying a filter in your HubDB function, but that filter isn't working correctly and so you're getting all rows instead of only rows that are equal to the value that you're querying for.

 

I think what you'll need to do is concatenate the column name and operator with the field value that you're using. In HubL we can concatenate strings with the ~ operator. So in this case, your query variable is going to look something like this:

 

{% set query = hubdb_table_rows([table_id], "&product_id="~module.link_product) %}

 

This way you're making a call to your table with a filter that looks something like "&product_id=1" or "&product_id=2", instead of "&product_id=module.link_product".

 

Let me know if this helps or if you have other questions about looping through your table rows. Or if I've misunderstood what's going wrong, feel free to send along a page so I can take a look.

 

- Leland

Leland Scanlan

HubSpot Developer Support
Reply
0 Upvotes
2 Replies 2
Occasional Contributor

Anyone?

Reply
0 Upvotes
HubSpot Moderator

Hi @Amit_95,

 

We should be able to figure this out for you. If I understand what you're trying to do, you're applying a filter in your HubDB function, but that filter isn't working correctly and so you're getting all rows instead of only rows that are equal to the value that you're querying for.

 

I think what you'll need to do is concatenate the column name and operator with the field value that you're using. In HubL we can concatenate strings with the ~ operator. So in this case, your query variable is going to look something like this:

 

{% set query = hubdb_table_rows([table_id], "&product_id="~module.link_product) %}

 

This way you're making a call to your table with a filter that looks something like "&product_id=1" or "&product_id=2", instead of "&product_id=module.link_product".

 

Let me know if this helps or if you have other questions about looping through your table rows. Or if I've misunderstood what's going wrong, feel free to send along a page so I can take a look.

 

- Leland

Leland Scanlan

HubSpot Developer Support
Reply
0 Upvotes