La fonction de suggestion automatique permet d'affiner rapidement votre recherche en suggérant des correspondances possibles au fur et à mesure de la frappe.
Accessing row in HubDB (alternatives to WHERE clause)
Résolue
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:
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.
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.
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.
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.