Hello:

I wonder how should we create our table to contain multiple stores per row in order to index that/those field(s) to retrieve data from Solr by issuing cql faster?

This is the query we have:

select upc, division, description, store_level_filters, deleted from item_ent_divs where solr_query = '{"q":"{!edismax boost=\"if(exists(query({!edismax qf=upc_search v=\\\"none\\\"})),300,add(def(synthetic_boost,0),div(1,sum(1,pow(e(),product(-0.00003,sub(min(200000,popularity),100000)))))))\
qf=\"description_search^5 brand_search taxonomy_search^0.5 keywords_search^0.5 semantic_boost_search^100 upc_search^100\
}((milk) OR \"milk\"^25 OR (milk) OR \"milk\"^5)
"fq":"(+division:(14) (+store_level_filters_terms:(383-a*) OR +store_level_filters_terms:(383-?c*) OR +store_level_filters_terms:(383-??d*)))"}';

store_level_filters_terms field is a string field that contain the store number then hyphen then modalities like "available in store", "available to pick up", "available to deliver" that marked by "a.." or ".c." or "..d" letters in different positions. Each division might have up to 50 stores and each store might have different letter combinations (1 to 1 although, e.g. store 383 might have all "acd" and store 85 ="xcd"). I wonder how can we redo our schema to make store field and modality as indexed fields without repeating the same row except the store-modality one?