Implementing dynamic product filters with parameter combinations and price ranges in PostgreSQL for e-commerce

Ecom
1

I’m working on an e-commerce app and need help with the product filtering system. The goal is to create dynamic filters where selecting options in one parameter affects the available choices in others, as well as updates the price range.

Here’s what I’m trying to achieve:

  1. User picks values for parameter 1
  2. System updates available options for parameters 2 and 3
  3. Price range adjusts based on selected filters

My current setup:

  • Backend: PostgreSQL (Supabase)
  • Frontend: FlutterFlow

I have a Products table with columns for various parameters and price. I’m wondering how to structure the database or create views in PostgreSQL to efficiently handle all possible parameter combinations. This would allow me to fetch the right data through API calls.

I’ve considered splitting the data into two tables, but I’m not sure if that’s the best approach. I also looked into using Redis bitmaps, but that’s not an option for me.

Has anyone tackled a similar problem? Any suggestions on how to implement this in PostgreSQL? I’d appreciate any tips, articles, or examples that could point me in the right direction.

2

hey mike, try jsonb cols. they’re flex and you can index parameters for fast queries. you can use sql functions to dynamically filter the data. for price ranges, a simple between clause works well. worked for me, trust me!

3

Hey there Mike! Your project sounds super interesting. I’m curious, have you considered using PostgreSQL’s full-text search capabilities for this? It might be a good fit for your dynamic filtering needs.

What if you created a tsvector column in your Products table that combines all the searchable parameters? You could then use tsquery to filter based on user selections. This approach could potentially handle complex combinations pretty efficiently.

For the price range part, maybe you could combine this with a simple WHERE clause on the price column? Something like:

WHERE to_tsvector('english', parameters) @@ to_tsquery('english', 'param1 & param2')
  AND price BETWEEN min_price AND max_price

Just brainstorming here! What do you think about this approach? Have you tried anything similar before?

Also, I’m really interested in how you’re planning to handle the frontend part in FlutterFlow. How are you thinking of updating the UI as users make selections? That sounds like a fun challenge too!

4

I’ve dealt with a similar challenge in my e-commerce projects. For PostgreSQL, I’d recommend using a combination of materialized views and GIN indexes. Create a materialized view that denormalizes your product data, including all parameter combinations. Then, build a GIN index on the parameter columns.

This approach allows for fast querying across multiple parameters. You can use WHERE clauses with array operators to filter based on user selections. For price ranges, add a simple numeric range query.

To keep it performant, refresh the materialized view periodically or trigger updates when products change. This method scales well and provides quick response times for dynamic filtering.

For the API, design endpoints that accept parameter arrays and price ranges. Use these to construct your SQL queries on the backend. This setup should give you the flexibility and speed you need for real-time filter updates.