Solr sort on a dynamic column

Question

I want to solve a problem related to sorting based on products in a category:

I have 3 tables

Product

|-------id----------|-----name-------|
|       p1          |      Prod 1    |
|       p2          |      Prod 2    |
|       p3          |      Prod 3    |
|       p4          |      Prod 4    |
|       p5          |      Prod 5    | 
|-------------------|----------------|

Category

|-------id----------|-----name-------|
|       c1          |      Cat 1     |
|       c2          |      Cat 2     |
|       c3          |      Cat 3     |
|       c4          |      Cat 4     |
|-------------------|----------------|

Product_Category

|-----prod id-------|-----cat id-----|----score----|
|       p1          |      c1        |     120     |
|       p1          |      c2        |     130     |
|       p2          |      c1        |     150     |
|       p2          |      c3        |     120     |
|       p2          |      c2        |     140     |
|       p3          |      c2        |     180     |
|       p3          |      c3        |     160     |
|-------------------|----------------|-------------|

This means I have products listed in multiple categories. I have a generate listing page dynamically for each category by solr query.

Currently my solr doc looks like

{
    product_id:p1,
    category_id:[c1, c2]
}

The challenge I am facing now is I need to support sorting based on product category weight, i.e. listing page of c1 will have products p2, p1 in order and listing of c3 will be p3, p2, p1 (descending order of score)

If I change the schema like to doc look like

{
    product_id:p1,
    category_id:[c1, c2],
    c1_weight: 120,
    c2_weight: 130
}

This way I need to add a field cx_weight to schema every time we add a new category so that I can sort by cx_weight field.

Let me know a solution where I can use solr sort mechanism to sort by category weight and need not change schema every time I add a category.

Thanks Dheerendra

Answer 1

Why not try modeling your solr doc as a Product_Category row?

{
    product_id:p1,
    category_id:c1,
    weight:120
},
{
    product_id:p1,
    category_id:c2,
    weight:130
}

This will support your category-page requirements.

The only complicating factors appear if you search for some product attribute and need to de-duplicate across categories (see field-collapsing doc for this)