Skip to main content

Faceted filtering and category pages

This page covers faceted filtering queries for ranking items within filtered sets, such as category pages or multi-faceted search results. For query fundamentals, see Query Basics.

Faceted filtering allows you to filter items by attributes (category, price range, brand, etc.) and then rank the filtered results. This is essential for building category pages, filtered product listings, and faceted search interfaces where users can apply multiple filters and still get personalized, well-ranked results.

Basic category filtering​

Filter items by a single category and rank them by popularity or other attributes.

Prerequisites​

  1. An engine with item data configured
  2. Item attributes to filter on (e.g., category, brand, price)

Query example​

Filter items by category and rank by popularity:

SELECT *
FROM column_order(columns='_derived_popular_rank ASC', limit=100)
WHERE category = 'Electronics'
LIMIT 20

Personalized category pages​

Combine category filtering with personalization models to show users the most relevant items within a category based on their preferences.

Prerequisites​

  1. An engine with item data configured
  2. A trained scoring model (e.g., lightgbm, bert4rec)
  3. A user_id to personalize for
  4. Item attributes to filter on

Query example​

Filter by category and rank using a personalization model:

SELECT *
FROM column_order(columns='_derived_popular_rank ASC', limit=1000)
WHERE category = '$category'
ORDER BY lightgbm
LIMIT 20

Combining multiple models in category pages​

Use an ensemble of models to balance different signals when ranking within a category:

SELECT *
FROM column_order(columns='_derived_popular_rank ASC', limit=1000)
WHERE category = '$category'
ORDER BY 0.6 * lightgbm + 0.4 * bert4rec
LIMIT 20

Multi-faceted filtering​

Apply multiple filters simultaneously (e.g., category + price range + brand) and rank the results. This is useful for building sophisticated filtering interfaces where users can narrow down results with multiple criteria.

Prerequisites​

  1. An engine with item data configured
  2. Multiple item attributes to filter on
  3. Optionally, a trained scoring model for personalization

Query example​

Filter by multiple attributes and rank by popularity:

SELECT *
FROM column_order(columns='_derived_popular_rank ASC', limit=1000)
WHERE category = '$category' 
  AND price >= $min_price 
  AND price <= $max_price
  AND brand = '$brand'
LIMIT 20

Personalized multi-faceted filtering​

Combine multiple filters with personalization to show users the most relevant items matching their filter criteria:

SELECT *
FROM column_order(columns='_derived_popular_rank ASC', limit=1000)
WHERE category = '$category' 
  AND price >= $min_price 
  AND price <= $max_price
ORDER BY lightgbm + 0.1 * user.age - 0.05 * item.price + 0.2 * item.rating
LIMIT 20

Retrieve candidates using similarity search within a filtered set. This is useful for "more like this" recommendations within a specific category or filtered context.

Prerequisites​

  1. An engine with a trained embedding (e.g., ALS, content embedding)
  2. Item attributes to filter on
  3. An item or user to find similar items for

Query example​

Find similar items within a category:

SELECT *
FROM similarity(embedding_ref='als_embedding',
               encoder='precomputed_item',
               input_item_id='$item_id', limit=50)
WHERE category = '$category'
ORDER BY lightgbm
LIMIT 20

Advanced: Blending retrieval scores with filters​

Combine multiple retrieval strategies within a filtered set, then score using both retrieval scores and model predictions:

SELECT *
FROM similarity(embedding_ref='als_embedding',
               encoder='precomputed_user',
               input_user_id='$user_id', limit=50, name='collab'),
     column_order(columns='_derived_popular_rank ASC', limit=50, name='popular')
WHERE category = '$category'
ORDER BY 0.5 * retrieval.collab + 0.3 * retrieval.popular + 0.2 * lightgbm
LIMIT 20

Filtering with text search​

Combine text search with attribute filtering to build search results that respect both query relevance and filter constraints:

SELECT *
FROM text_search(query='$query_text', mode='vector', 
                 text_embedding_ref='text_embedding', limit=50,
                 name='vector_search'),
     text_search(query='$query_text', mode='lexical', fuzziness=2, limit=50,
                 name='lexical_search')
WHERE category = '$category' AND price <= $max_price
ORDER BY 0.5 * retrieval.vector_search + 0.3 * retrieval.lexical_search + 
         0.2 * lightgbm
LIMIT 20