CRM-provided price and bedrooms are sorting incorrectly in custom module
I am facing two specific problems with the code provided below.
The data is being pulled directly from the CRM itself, as values of properties of product object entries. Product object entries are being used to define real estate properties, projects, and other entries; and properties (HubSpot-provided or custom) are being used to describe these entries.
This specific module builds a typical real estate properties filtering and sorting function. The user - by manipulating and submitting filter and search parameters - ends up seeing properties in cards with some basic information that align with said filter and sort selections. The initial load is such that all properties which are for sale, are shown, in an initial sort order (price ascending).
Problem 1: Price Sorting
When sorting products by price in ascending or descending order, the results do not align with human expectations for numeric values. This is observed, for example, in the following scenarios:
Prices with high values, such as 1000000 or more, are not sorted as expected. As an example, a value of 1100000 is typically sorted as if less than 10.
Prices with small decimal values, such as 9.99 or 999, are also sorted incorrectly. As examples, those two values will typically sorted as if more than 1000000.
Price field type is number, and the number format is currency. The internal name of price is "price".
Problem 2: Bedroom Sorting
When sorting properties by the number of bedrooms, those
Values labelled as "Studio" are not sorted as part of a consistent numerical order. When sorting, instead of a Studio entry being shown as the last possible number of bedrooms, it is shown as the maximum possible number of bedrooms. So ascending sort displays the Studio last, and descending sort displays the Studio first.
The field type settings are as follows:
Field type: dropdown select
Sort: custom
Options as below
This is the module:
<!-- Debugging Section (Essential Information) -->
<div>
<h4>Debugging Output</h4>
<p>Final Filter Query: {{ filter_query }}</p>
</div>
<!-- Filter and Sort Form with Reset Button in the Top Right Corner -->
<form id="filter-sort-form" method="GET" action="{{ request.path }}">
<div class="filter-section" style="position: relative;">
<!-- Reset Filters Button (Top Right Corner) -->
<button type="button" id="reset-filters" style="position: absolute; top: 10px; right: 10px; font-size: 0.8rem; padding: 0.3rem 0.5rem;">Reset</button>
<!-- Group 1: Property Type and Bedrooms -->
<div class="filter-group">
<!-- Property Type -->
<div class="sub-section">
<label for="property_type">Property Type:</label>
<div id="property_type_checkboxes">
{% set property_type_options = crm_property_definitions('product', 'property_type') %}
{% for option in property_type_options[0].options %}
<label>
<input type="checkbox" name="property_type[]" value="{{ option.value }}"
{% if not request.query_dict.property_type or option.value in request.query_dict_list.property_type %}checked{% endif %}>
{{ option.label }}
</label>
{% endfor %}
</div>
</div>
<!-- Bedrooms -->
<div class="sub-section">
<label for="min_bedrooms">Min Bedrooms:</label>
{% set bedrooms_options = crm_property_definitions('product', 'bedrooms') %}
<select id="min_bedrooms" name="min_bedrooms">
<option value="">All</option>
{% for option in bedrooms_options[0].options %}
<option value="{{ option.value }}" {% if request.query_dict.min_bedrooms == option.value %}selected{% endif %}>{{ option.label }}</option>
{% endfor %}
</select>
<label for="max_bedrooms">Max Bedrooms:</label>
<select id="max_bedrooms" name="max_bedrooms">
<option value="">All</option>
{% for option in bedrooms_options[0].options %}
<option value="{{ option.value }}" {% if request.query_dict.max_bedrooms == option.value %}selected{% endif %}>{{ option.label }}</option>
{% endfor %}
</select>
</div>
</div>
<!-- Group 2: Location and Price -->
<div class="filter-group">
<div class="sub-section">
<label for="location">Location:</label>
<div id="location_checkboxes">
{% set location_options = crm_property_definitions('product', 'location') %}
{% for option in location_options[0].options %}
<label>
<input type="checkbox" name="location[]" value="{{ option.value }}"
{% if not request.query_dict.location or option.value in request.query_dict_list.location %}checked{% endif %}>
{{ option.label }}
</label>
{% endfor %}
</div>
</div>
<div class="sub-section">
<label for="min_price">Min Price:</label>
<input type="number" id="min_price" name="min_price" placeholder="Min Price" value="{{ request.query_dict.min_price }}">
<label for="max_price">Max Price:</label>
<input type="number" id="max_price" name="max_price" placeholder="Max Price" value="{{ request.query_dict.max_price }}">
</div>
</div>
<!-- Group 3: VAT Applicability -->
<div class="filter-group">
<label for="vat_applicability">VAT Applicability:</label>
{% set vat_applicability_options = crm_property_definitions('product', 'vat_applicability') %}
<select id="vat_applicability" name="vat_applicability">
<option value="">All</option>
{% for option in vat_applicability_options[0].options %}
<option value="{{ option.value }}" {% if request.query_dict.vat_applicability == option.value %}selected{% endif %}>{{ option.label }}</option>
{% endfor %}
</select>
</div>
<!-- Sort By Section -->
<div class="sort-section">
<label for="sort_by">Sort By:</label>
<select id="sort_by" name="sort_by">
<option value="price_asc" {% if not request.query_dict.sort_by or request.query_dict.sort_by == "price_asc" %}selected{% endif %}>Price (lowest to highest)</option>
<option value="price_desc" {% if request.query_dict.sort_by == "price_desc" %}selected{% endif %}>Price (highest to lowest)</option>
<option value="bedrooms_asc" {% if request.query_dict.sort_by == "bedrooms_asc" %}selected{% endif %}>Bedrooms (lowest to highest)</option>
<option value="bedrooms_desc" {% if request.query_dict.sort_by == "bedrooms_desc" %}selected{% endif %}>Bedrooms (highest to lowest)</option>
<option value="total_covered_area_asc" {% if request.query_dict.sort_by == "total_covered_area_asc" %}selected{% endif %}>Total Covered Area (lowest to highest)</option>
<option value="total_covered_area_desc" {% if request.query_dict.sort_by == "total_covered_area_desc" %}selected{% endif %}>Total Covered Area (highest to lowest)</option>
</select>
</div>
<!-- Apply Filters and Sort Button -->
<button type="submit">Apply</button>
</div>
</form>
<!-- Loading Indicator -->
<div id="loading-indicator" style="display: none;">Loading...</div>
<!-- Constructing the Filter Query with Availability Condition -->
{% set filter_query = "hs_product_type=Property&availability=For Sale" %}
<!-- Property Type Filters -->
{% if request.query_dict.property_type %}
{% set selected_property_types = request.query_dict_list.property_type %}
{% if selected_property_types is iterable %}
{% set filter_query = filter_query ~ "&property_type__in=" %}
{% for type in selected_property_types %}
{% set filter_query = filter_query ~ type %}
{% if not loop.last %} {% set filter_query = filter_query ~ "," %} {% endif %}
{% endfor %}
{% else %}
{% set filter_query = filter_query ~ "&property_type__in=" ~ request.query_dict.property_type %}
{% endif %}
{% endif %}
<!-- Location Filters -->
{% if request.query_dict.location %}
{% set selected_locations = request.query_dict_list.location %}
{% if selected_locations is iterable %}
{% set filter_query = filter_query ~ "&location__in=" %}
{% for loc in selected_locations %}
{% set filter_query = filter_query ~ loc %}
{% if not loop.last %} {% set filter_query = filter_query ~ "," %} {% endif %}
{% endfor %}
{% else %}
{% set filter_query = filter_query ~ "&location__in=" ~ request.query_dict.location %}
{% endif %}
{% endif %}
<!-- Bedrooms Filters -->
{% if request.query_dict.min_bedrooms %}
{% set filter_query = filter_query ~ "&bedrooms__gte=" ~ request.query_dict.min_bedrooms %}
{% endif %}
{% if request.query_dict.max_bedrooms %}
{% set filter_query = filter_query ~ "&bedrooms__lte=" ~ request.query_dict.max_bedrooms %}
{% endif %}
<!-- Price Filters -->
{% if request.query_dict.min_price %}
{% set filter_query = filter_query ~ "&price__gte=" ~ request.query_dict.min_price %}
{% endif %}
{% if request.query_dict.max_price %}
{% set filter_query = filter_query ~ "&price__lte=" ~ request.query_dict.max_price %}
{% endif %}
<!-- VAT Applicability Filter -->
{% if request.query_dict.vat_applicability %}
{% set filter_query = filter_query ~ "&vat_applicability=" ~ request.query_dict.vat_applicability %}
{% endif %}
<!-- Dynamic Page Product Loading -->
{% if dynamic_page_crm_object_type_fqn and dynamic_page_id %}
{% set dynamic_page_product = crm_object_by_id(dynamic_page_crm_object_type_fqn, dynamic_page_id, "hs_sku, name, price, cover_photo, address, property_type, bedrooms, bathrooms, vat_applicability, located_within_project, location, total_covered_area, plot_area") %}
{% if dynamic_page_product %}
<div class="product__card">
<div class="product__header">{{ dynamic_page_product.name }} - {{ dynamic_page_product.price | format_currency("en-US") }}</div>
<div class="product__details">Location: {{ dynamic_page_product.location }}</div>
</div>
{% else %}
<p>Dynamic product not found.</p>
{% endif %}
{% else %}
{% set products = crm_objects("product", filter_query ~ "&limit=200", "hs_sku, name, price, cover_photo, address, property_type, bedrooms, bathrooms, vat_applicability, located_within_project, location, total_covered_area, plot_area") %}
<!-- Parsing and Sorting Adjustments -->
{% for product in products.results %}
{% if product.bedrooms == "Studio" %}
{% set product.bedrooms = 0.5 %}
{% endif %}
{% if product.total_covered_area is none %}
{% set product.total_covered_area = 0 %}
{% endif %}
{% if product.price is string %}
{% set product.price = product.price|replace(",", "")|float %}
{% endif %}
{% endfor %}
{% if products.results|length > 0 %}
<p>Available Properties: {{ products.results|length }}</p>
<div class="product__listing">
{% set sort_by = request.query_dict.sort_by or 'price_asc' %}
{% set sorted_products = products.results %}
{% if sort_by == 'price_asc' %}
{% set sorted_products = products.results|sort(False, False, "price") %}
{% elif sort_by == 'price_desc' %}
{% set sorted_products = products.results|sort(True, False, "price") %}
{% elif sort_by == 'bedrooms_asc' %}
{% set sorted_products = products.results|sort(False, False, "bedrooms") %}
{% elif sort_by == 'bedrooms_desc' %}
{% set sorted_products = products.results|sort(True, False, "bedrooms") %}
{% elif sort_by == 'total_covered_area_asc' %}
{% set sorted_products = products.results|sort(False, False, "total_covered_area") %}
{% elif sort_by == 'total_covered_area_desc' %}
{% set sorted_products = products.results|sort(True, False, "total_covered_area") %}
{% endif %}
{% for product in sorted_products %}
<div class="product__card">
<div class="product__photo">
{% if product.cover_photo %}
<img src="{{ product.cover_photo }}" alt="{{ product.name }} {{ product.price }} {{ product.property_type }}">
{% else %}
<img src="https://f.hubspotusercontent20.net/hubfs/9307273/Imported%20images/plchldr255.png" alt="Picture coming soon">
{% endif %}
</div>
<div class="product__main-info product__primary">
<div class="product__header">
<div class="product__name">{{ product.name }} ({{ product.hs_sku }})</div>
<div class="product__project">Project: {{ product.located_within_project }}</div>
<div class="product__price">{{ product.price | format_currency("en-US") }}</div>
<div class="product__vat">{{ product.vat_applicability }}</div>
</div>
</div>
<div class="product__separator"></div>
<div class="product__secondary-info product__grid">
<div>Type: {{ product.property_type }}</div>
<div>Location: {{ product.location }}</div>
<div>Bedrooms: {{ product.bedrooms }}</div>
<div>Bathrooms: {{ product.bathrooms }}</div>
<div>Total Covered Area (sq.m): {{ product.total_covered_area }}</div>
<div>Plot Area (sq.m): {{ product.plot_area }}</div>
</div>
</div>
{% endfor %}
</div>
{% else %}
<p>No products found.</p>
{% endif %}
{% endif %}
/* Container for the entire layout */
.container {
display: flex;
flex-direction: column;
width: 100%; /* Ensure the full container spans the width */
max-width: 100%;
margin: 0;
padding: 0;
box-sizing: border-box;
}
/* Remove default styles for anchor tags */
a {
text-decoration: none;
color: inherit;
border: none; /* Ensuring no borders are set for the anchor tags */
display: none; /* Hide all anchor tags */
}
/* Ensure empty anchors are hidden */
a:empty {
display: none;
}
/* Common styles for filter, sort, and product listing sections */
.filter-section,
.sort-section,
.product__listing {
width: 100%; /* Ensure all sections take full available width */
max-width: 1200px; /* Set maximum width to match the product card section */
margin: 0 auto; /* Center the sections on the page */
padding: 1rem;
box-sizing: border-box;
display: flex;
flex-direction: column;
background-color: #f9f9f9;
border: 1px solid #ccc;
border-radius: 8px;
}
/* Ensure each filter group covers the full width */
.filter-group {
display: flex;
flex-direction: column;
gap: 1rem;
width: 100%; /* Full width */
}
/* Sub-sections within each group cover full width */
.sub-section {
display: flex;
justify-content: space-between;
align-items: center;
gap: 1rem;
width: 100%; /* Full width */
}
/* Internal layout unchanged */
.sub-section > div {
flex: 1;
display: flex;
flex-direction: column;
width: 100%; /* Full width for each input */
}
/* Adjust property type and location section to stack checkboxes in rows */
#property_type_checkboxes,
#location_checkboxes {
display: grid;
grid-template-columns: repeat(2, 1fr); /* Two columns per row */
gap: 1rem;
width: 100%; /* Full width */
}
/* Centering the filter button at the bottom */
.filter-section button[type="submit"],
.sort-section button[type="submit"] {
align-self: center;
padding: 0.5rem 1rem;
background-color: #007bff;
color: white;
border: none;
cursor: pointer;
border-radius: 5px;
}
.filter-section button[type="submit"]:hover,
.sort-section button[type="submit"]:hover {
background-color: #0056b3;
}
/* Product Listing Styles */
.product__listing {
display: grid;
grid-template-columns: 1fr;
gap: 1rem;
width: 100%;
max-width: 1200px; /* Ensure product listing takes full width */
margin: 0 auto; /* Center product listing */
padding: 1rem;
}
/* Product Card Styles */
.product__card {
display: flex;
flex-direction: column;
border: 1px solid #ddd;
border-radius: 8px;
overflow: hidden;
box-shadow: 0 0.2rem 0.8rem rgba(0, 0, 0, 0.1);
transition: transform 0.3s ease;
background-color: #fff;
padding: 1rem;
width: 100%;
}
/* Primary and Secondary Info Styles */
.product__primary {
font-size: 1.25rem; /* Uniform font size for primary information */
font-weight: bold;
}
.product__secondary-info {
font-size: 1rem; /* Normal size for secondary information */
font-weight: normal;
}
/* Responsive Adjustments */
@media (max-width: 1200px) {
.container,
.filter-section,
.sort-section,
.product__listing {
width: 100%; /* Ensure full width for responsive views */
}
.product__header,
.product__name,
.product__price {
font-size: 1.3rem;
}
.product__main-info,
.product__project-vat {
font-size: 1.1rem;
}
.product__secondary-info {
font-size: 0.9rem;
}
}
@media (max-width: 768px) {
.container,
.filter-section,
.sort-section {
width: 100%;
padding: 1rem;
}
.sub-section {
flex-direction: column;
}
#property_type_checkboxes,
#location_checkboxes {
grid-template-columns: 1fr; /* One column per row on small screens */
}
.product__header,
.product__name,
.product__price {
font-size: 1.2rem;
}
.product__main-info,
.product__project-vat {
font-size: 1rem;
}
.product__secondary-info {
grid-template-columns: 1fr 1fr;
font-size: 0.8rem;
}
}
@media (max-width: 480px) {
.container,
.filter-section {
padding: 0.5rem;
}
.product__header,
.product__name,
.product__price {
font-size: 1rem;
}
.product__main-info,
.product__project-vat {
font-size: 0.9rem;
}
.product__secondary-info {
grid-template-columns: 1fr;
font-size: 0.8rem;
}
}
document.addEventListener('DOMContentLoaded', function () {
// Function to update debugging output dynamically based on the actual filter selections
function updateDebugOutput() {
const debugOutput = document.getElementById('debug-output');
// Simulate multiple select behavior for Property Type checkboxes
const propertyTypeSelected = Array.from(document.querySelectorAll('input[name="property_type[]"]:checked'))
.map(cb => cb.value)
.join(', ') || 'None';
// Simulate multiple select behavior for Location checkboxes
const locationSelected = Array.from(document.querySelectorAll('input[name="location[]"]:checked'))
.map(cb => cb.value)
.join(', ') || 'None';
// Get dropdown values for bedrooms and VAT Applicability
const minBedrooms = document.getElementById('min_bedrooms') ? document.getElementById('min_bedrooms').value : 'All';
const maxBedrooms = document.getElementById('max_bedrooms') ? document.getElementById('max_bedrooms').value : 'All';
const vatApplicability = document.getElementById('vat_applicability') ? document.getElementById('vat_applicability').value : 'All';
// Get input values for price range
const minPrice = document.getElementById('min_price') ? document.getElementById('min_price').value : 'No Minimum';
const maxPrice = document.getElementById('max_price') ? document.getElementById('max_price').value : 'No Maximum';
// Update debugging output with selected filter values
if (debugOutput) {
debugOutput.innerHTML = `
Property Type: ${propertyTypeSelected}<br>
Location: ${locationSelected}<br>
Min Bedrooms: ${minBedrooms}<br>
Max Bedrooms: ${maxBedrooms}<br>
Min Price: ${minPrice}<br>
Max Price: ${maxPrice}<br>
VAT Applicability: ${vatApplicability}<br>
`;
}
}
// Ensure checkboxes remain selected based on query params (initial load)
const checkboxes = document.querySelectorAll('input[type="checkbox"]');
const queryParams = new URLSearchParams(window.location.search);
// Always reflect the initial state as the source of truth
checkboxes.forEach(checkbox => {
const name = checkbox.getAttribute('name');
const value = checkbox.getAttribute('value');
if (queryParams.getAll(name).includes(value)) {
checkbox.checked = true;
}
});
// Ensure dropdowns remain selected based on query params (initial load)
const dropdowns = ['min_bedrooms', 'max_bedrooms', 'vat_applicability'];
dropdowns.forEach(dropdownId => {
const dropdown = document.getElementById(dropdownId);
if (dropdown) {
const selectedValue = queryParams.get(dropdownId);
if (selectedValue) {
dropdown.value = selectedValue;
}
}
});
// Ensure price inputs remain filled based on query params (initial load)
const priceFields = ['min_price', 'max_price'];
priceFields.forEach(priceId => {
const priceInput = document.getElementById(priceId);
if (priceInput) {
const selectedValue = queryParams.get(priceId);
if (selectedValue) {
priceInput.value = selectedValue;
}
}
});
// Update debugging output initially
updateDebugOutput();
// Handle checkbox changes dynamically
checkboxes.forEach(checkbox => {
checkbox.addEventListener('change', updateDebugOutput);
});
// Handle dropdown and price input changes dynamically
const minBedroomsElement = document.getElementById('min_bedrooms');
const maxBedroomsElement = document.getElementById('max_bedrooms');
const vatApplicabilityElement = document.getElementById('vat_applicability');
const minPriceElement = document.getElementById('min_price');
const maxPriceElement = document.getElementById('max_price');
if (minBedroomsElement) minBedroomsElement.addEventListener('change', updateDebugOutput);
if (maxBedroomsElement) maxBedroomsElement.addEventListener('change', updateDebugOutput);
if (vatApplicabilityElement) vatApplicabilityElement.addEventListener('change', updateDebugOutput);
if (minPriceElement) minPriceElement.addEventListener('input', updateDebugOutput);
if (maxPriceElement) maxPriceElement.addEventListener('input', updateDebugOutput);
// Handle form submission for both filters and sorting
const filterSortForm = document.getElementById('filter-sort-form');
if (filterSortForm) {
filterSortForm.addEventListener('submit', function (event) {
event.preventDefault(); // Prevent default form submission
// Collect and concatenate Property Types and Locations (simulating multiple select behavior)
const propertyTypes = Array.from(document.querySelectorAll('input[name="property_type[]"]:checked'))
.map(cb => cb.value)
.join(',');
const locations = Array.from(document.querySelectorAll('input[name="location[]"]:checked'))
.map(cb => cb.value)
.join(',');
// Create a URLSearchParams object to build the query string
const formData = new URLSearchParams(new FormData(filterSortForm));
// Append concatenated values (simulating multiple select behavior)
if (propertyTypes) {
formData.set('property_type', propertyTypes);
}
if (locations) {
formData.set('location', locations);
}
// Append sorting option
const sortBy = document.getElementById('sort_by').value;
formData.set('sort_by', sortBy);
// Redirect with the query string including all selected filters and sorting option
const newUrl = `${window.location.pathname}?${formData.toString()}`;
window.location.href = newUrl;
});
}
// Handle Reset Filters button functionality
const resetFiltersButton = document.getElementById('reset-filters');
if (resetFiltersButton) {
resetFiltersButton.addEventListener('click', function () {
// Reload the page without any query parameters (clearing all filters)
const url = new URL(window.location.href);
url.search = ''; // Clear all query parameters
window.location.href = url.toString();
});
}
// Remove specific anchor tag causing the random blue line issue
const anchors = document.querySelectorAll('a');
anchors.forEach(anchor => {
if (anchor.textContent.includes('Properties') || anchor.href.includes('properties-listings')) {
anchor.style.display = 'none'; // Hide the anchor element
}
});
});
How can I solve these two problems? The solutions must be able to accommodate all possible scenarios within the limits of the value field definitions (i.e. any price, and any number of bedrooms regardless of how they are labelled - numerical, decimal or alphabetical).
Regarding the bedrooms issue, one possible way is to tap into, pull, and use purely and only for sorting purposes, the following (per screenshot): preferably, the ORDER of dropdown options, as shown; or, and less preferably, the INTERNAL NAME of those same options; without adjusting what is displayed (i.e. the LABEL is what we want displayed). But, I don't know how to do that.
CRM-provided price and bedrooms are sorting incorrectly in custom module
Hi, @DrAS 👋 Thanks for your question. I am not an expert, but I know enough to be dangerous 😊 I did some research + worked with our internal AI tool to help me with formatting my reply. We'll also leave this open in case other community experts have additional feedback for you (or me).
- Problem 1: Your prices are being stored as strings which causes isses with lexicographical sorting, e.g., “1000000” < “10”.
Possible solution — Have you tried converting the “prices” to numeric values during parsing? Something like:
{% for product in products.results %}
{% if product.price is string %}
{% set product.price = product.price|replace(",", "")|float %}
{% endif %}
{% endfor %}And for your numeric sorting
{% if sort_by == 'price_asc' %}
{% set sorted_products = products.results|sort(False, False, "price") %}
{% elif sort_by == 'price_desc' %}
{% set sorted_products = products.results|sort(True, False, "price") %}
{% endif %}
- Problem 2: “Studio” is getting treated as text, which upsets the numeric sorting. Have you tried mapping “Studio” to a numeric value? Such as “0.5”?
{% for product in products.results %}
{% if product.bedrooms == "Studio" %}
{% set product.bedrooms = 0.5 %}
{% else %}
{% set product.bedrooms = product.bedrooms|float %}
{% endif %}
{% endfor %}To sort numerically:
{% if sort_by == 'bedrooms_asc' %}
{% set sorted_products = products.results|sort(False, False, "bedrooms") %}
{% elif sort_by == 'bedrooms_desc' %}
{% set sorted_products = products.results|sort(True, False, "bedrooms") %}
{% endif %}
And one suggestion on how to handle default missing values
{% if product.price is none %}{% set product.price = 0 %}{% endif %}
{% if product.bedrooms is none %}{% set product.bedrooms = 0 %}{% endif %}
Talk soon! — Jaycee
|
