Printing Values from HubDB "Select" Column & Grouping Content by Column

[oliviadurr]

I'm working on building a custom module that displays a company's locations and grouping them by name. This is the desired output/design: 

I can't seem to figure out how to nest the rows under each state or how to get the state name to populate. This is the code block that I have so far:

{# Base Naming #}
{% set module_name = 'db-cards' %}
{% set modifier = ' module--' %}

{% set states = dynamic_page_hubdb_row.state %}


{% set tableID = module.db_setting.table %}
{% set resource = hubdb_table_column(tableID, state) %}
{% set test = hubdb_table_row(tableID, state) %}
{% set locations = hubdb_table_rows(tableID, "orderBy=state") %}
{% set state_title = hubdb_table_column(tableID, state) %}
{% set query = '' %}
{% set column = hubdb_table_column(tableID, State) %}

<div class="{{ module_name }}">
  {% for row in locations %}
		<h2>{{ row["state"].name }}</h2>
    <h3>{{ row["location_name"] }}</h3>
    <h4>{{ row["address_name"] }}</h4>
		<h4>{{ row["address_1"] }}</h4>
		<h4>{{ row["address_2"] }}</h4>
		<h4>{{ row["office_number"] }}</h4>
		<h4>{{ row["office_email"] }}</h4>
		
	{% endfor %}
</div>

Would anyone be able to help me out or point me in the right direction? Thank you!

------

EDIT: After some more playing around, this is the current code block: 

{# Base Naming #}
{% set module_name = 'db-cards' %}
{% set modifier = ' module--' %}

{% set tableID = module.db_setting.table %}
{% set allHubDBRows = hubdb_table_rows(tableID) %}
{% set table_select = hubdb_table_column(tableID, 'state') %}
{% set location = table_select.optionsByName %}

{% for item in location|sort(False, False, 'name') %}
  {% set query = '&state__eq='~item.name|replace(' ','%20')~'&orderBy=location_name' %}
  {% set locations = hubdb_table_rows(tableID, query) %}
  {% set locations2 = allHubDBRows|selectattr('state', 'equalto', item.name ) %}
  <h2>{{item.name}}</h2>
  {% for row in locations %}
    <h3>{{ row["location_name"] }}</h3>
    <h4>{{ row["address_name"] }}</h4>
		<h4>{{ row["address_1"] }}</h4>
		<h4>{{ row["address_2"] }}</h4>
		<h4>{{ row["office_number"] }}</h4>
		<h4>{{ row["office_email"] }}</h4>
	{% endfor %}
{% endfor %}

While it prints the right information, I'm met with the 10-row call limit per page. I tried to work around it by adding the "locations2" call, but can't see to get that one to print correctly.

[oliviadurr · Accepted solution]

Someone in the Developer Slack helped me realize that the 

 {% set locations2 = allHubDBRows|selectattr('state', 'equalto', item.name ) %}

had to be modified to be:

 {% set locations3 = allHubDBRows|selectattr('state.name', 'equalto', statename ) %}

Which resulted in everything being printed correctly!

For anyone running into the same issue, here's the final code block that prints entries from a single HubDB table but groups them by a column and has the column option label:

{% set tableID = module.db_setting.table %}
{% set allHubDBRows = hubdb_table_rows(tableID) %}
{% set table_select = hubdb_table_column(tableID, 'state') %}
{% set location = table_select.optionsByName %}
{% for item in location|sort(False, False, 'name') %}
  {% set statename = item.name %}
  {% set query = '&state__eq='~item.name|replace(' ','%20')~'&orderBy=location_name' %}
  {# set locations = hubdb_table_rows(tableID, query) #}
  {% set locations2 = allHubDBRows|selectattr('state.name', 'equalto', statename ) %}
  <h2>{{statename}}</h2>
  {% for row in locations2 %}
    <h3>{{ row["location_name"] }}</h3>
    <h4>{{ row["address_name"] }}</h4>
		<h4>{{ row["address_1"] }}</h4>
		<h4>{{ row["address_2"] }}</h4>
		<h4>{{ row["office_number"] }}</h4>
		<h4>{{ row["office_email"] }}</h4>
	{% endfor %}
{% endfor %}

Here's a snippet of the results:

[stefen]

@oliviadurr can you show us what your hubdb table looks like?

[oliviadurr]

@stefen 

Here's some screenshots of the DB:

[dennisedson]

@stefen , do you have any thoughts on this? 😀