Solr Data Config Queries per Field
Hi,
I am new to Solr, and I am using the DataImportHandler to Query a SQL
Server and populate Solr. I specify the SQL Query in the db-data-config.xml
file. Each SQL Query seems to be associated with an entity. Is it possible
to have a query per field? I think it would be easier to explain this using
an example:
I have products that are classified in a hierarchy of Categories. A single
product can be in multiple Categories. I want to provide the user the
ability to drill down i.e. first select the top level category Category1,
next select the next level category Category2 etc. Since a single product
can be in multiple Categories, all of these i.e. Category1, Category2,
Category3 etc. are multi-valued.
SQL Database Schema:
Table: Prod_Table
Column 1: SKU - ID/Primary Key
Column 2: Title
Table: Cat_Table
Column 1: SKU - Foreign Key
Column 2: CategoryLevel
Column 3: CategoryName
Where CategoryLevel is 1, I would like to save the value to Category1 field,
where CategoryLevel is 2, I would like to save this to the Category2 field
etc. My db-data-config.xml looks like:
dataConfig
dataSource driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
url=jdbc:sqlserver://localhost…/
document
entity name=Product
query=SELECT SKU, Title FROM PROD_TABLE
field column=SKU name=SKU /
field column=Title name=Title /
entity name=quot;Categoriesquot;
query=quot;SELECT CategoryName from CAT_TABLE where
SKU='${Product.SKU}' AND CategoryLevel=1quot;
lt;field column=quot;Category1quot;
name=quot;Category1quot; /
Query: SELECT CategoryName from
CAT_TABLE where
SKU='${Product.SKU}' AND CategoryLevel=2
field column=Category2 name=Category2 /
Query: SELECT CategoryName from
CAT_TABLE where
SKU='${Product.SKU}' AND CategoryLevel=3
field column=Category3 name=Category3 /
/entity
/entity
/document
/dataConfig
How do I populate Category2 and Category3??
Thank you for all your help.
O. O.
--
View this message in context:
http://lucene.472066.n3.nabble.com/Solr-Data-Config-Queries-per-Field-tp4037092.html
Sent from the Solr - User mailing list archive at Nabble.com.
Re: Solr Data Config Queries per Field
On 29 January 2013 22:42, O. Olson olson_...@yahoo.it wrote:
[...]
SQL Database Schema:
Table: Prod_Table
Column 1: SKU - ID/Primary Key
Column 2: Title
Table: Cat_Table
Column 1: SKU - Foreign Key
Column 2: CategoryLevel
Column 3: CategoryName
Where CategoryLevel is 1, I would like to save the value to Category1 field,
where CategoryLevel is 2, I would like to save this to the Category2 field
etc.
[...]
It is not very clear from your description, nor from your example,
what you want saved to the Category1, Category2,... fields, and
how you expect your user searches to function. You seem to imply
that the categories are hierarchical, but there is no relationship in
the database to define this hierarchy.
For a given product SKU, do you want the multi-valued Category1
field to contain all CategoryName values from Cat_Table that have
CategoryLevel = 1 and SKU matching the product SKU, and so on
for the other categories? If so, this should do it:
entity name=Product query=SELECT SKU, Title FROM PROD_TABLE
field column=SKU name=SKU /
field column=Title name=Title /
entity name=Cat1 query=SELECT CategoryName from CAT_TABLE where
SKU='${Product.SKU}' AND CategoryLevel=1
field column=CategoryName name=Category1 /
/entity
entity name=Cat2 query=SELECT CategoryName from CAT_TABLE where
SKU='${Product.SKU}' AND CategoryLevel=1
field column=CategoryName name=Category2 /
/entity
entity name=Cat3 query=SELECT CategoryName from CAT_TABLE where
SKU='${Product.SKU}' AND CategoryLevel=1
field column=CategoryName name=Category3 /
/entity
/entity
Regards,
Gora
Re: Solr Data Config Queries per Field
Gora Mohanty-3 wrote
On 29 January 2013 22:42, O. Olson lt;
olson_ord@
gt; wrote:
[...]
SQL Database Schema:
Table: Prod_Table
Column 1: SKU - ID/Primary Key
Column 2: Title
Table: Cat_Table
Column 1: SKU - Foreign Key
Column 2: CategoryLevel
Column 3: CategoryName
Where CategoryLevel is 1, I would like to save the value to Category1
field,
where CategoryLevel is 2, I would like to save this to the Category2
field
etc.
[...]
It is not very clear from your description, nor from your example,
what you want saved to the Category1, Category2,... fields, and
how you expect your user searches to function. You seem to imply
that the categories are hierarchical, but there is no relationship in
the database to define this hierarchy.
For a given product SKU, do you want the multi-valued Category1
field to contain all CategoryName values from Cat_Table that have
CategoryLevel = 1 and SKU matching the product SKU, and so on
for the other categories? If so, this should do it:
entity name=Product query=SELECT SKU, Title FROM PROD_TABLE
field column=SKU name=SKU /
field column=Title name=Title /
entity name=Cat1 query=SELECT CategoryName from CAT_TABLE where
SKU='${Product.SKU}' AND CategoryLevel=1
field column=CategoryName name=Category1 /
/entity
entity name=Cat2 query=SELECT CategoryName from CAT_TABLE where
SKU='${Product.SKU}' AND CategoryLevel=1
field column=CategoryName name=Category2 /
/entity
entity name=Cat3 query=SELECT CategoryName from CAT_TABLE where
SKU='${Product.SKU}' AND CategoryLevel=1
field column=CategoryName name=Category3 /
/entity
/entity
Regards,
Gora
Thank you. Good call Gora, I forgot to mention about the query. I am trying
to query something like the following in the URL for the Example:
http://localhost:8983/solr/db/select
?q=queryfacet=truefacet.field=Category1
I expect the above query to give me the counts for the products that satisfy
the query in Category1. For example given my query I get: Hardware (21),
Software (3), Office Supplies (10). These are Category1 values. Lets then
say a user selects Hardware. I think I would do something like:
?q=queryfacet=truefq=Category1:Hardwarefacet.field=Category2
I assume this would be give me the list of Category 2 values e.g. Printers
(7), Fax Machines (11), LCD Monitors (3) (7 + 11 + 3 = 21).
You suggest I create separate entities for each Category Level. Would this
affect my schema? i.e. would the above queries work??
Thanks again Gora,
O. O.
--
View this message in context:
http://lucene.472066.n3.nabble.com/Solr-Data-Config-Queries-per-Field-tp4037092p4037118.html
Sent from the Solr - User mailing list archive at Nabble.com.
Re: Solr Data Config Queries per Field
On 29 January 2013 23:34, O. Olson olson_...@yahoo.it wrote: [...] Thank you. Good call Gora, I forgot to mention about the query. I am trying to query something like the following in the URL for the Example: http://localhost:8983/solr/db/select ?q=queryfacet=truefacet.field=Category1 I expect the above query to give me the counts for the products that satisfy the query in Category1. For example given my query I get: Hardware (21), Software (3), Office Supplies (10). These are Category1 values. Lets then say a user selects Hardware. I think I would do something like: ?q=queryfacet=truefq=Category1:Hardwarefacet.field=Category2 I assume this would be give me the list of Category 2 values e.g. Printers (7), Fax Machines (11), LCD Monitors (3) (7 + 11 + 3 = 21). You suggest I create separate entities for each Category Level. Would this affect my schema? i.e. would the above queries work?? Yes, things should function as you describe, and no you should not need any change in your schema from changing the DIH configuration file. Please take a look at http://wiki.apache.org/solr/SolrFacetingOverview#Facet_Indexing for how best to define faceting fields. Also, see this tutorial on faceted search with Solr: http://searchhub.org/2009/09/02/faceted-search-with-solr/ Regards, Gora
Re: Solr Data Config Queries per Field
Gora Mohanty-3 wrote Yes, things should function as you describe, and no you should not need any change in your schema from changing the DIH configuration file. Please take a look at http://wiki.apache.org/solr/SolrFacetingOverview#Facet_Indexing for how best to define faceting fields. Also, see this tutorial on faceted search with Solr: http://searchhub.org/2009/09/02/faceted-search-with-solr/ Regards, Gora Thank you Gora. I implemented it the way you suggested, and it worked perfectly! O. O. -- View this message in context: http://lucene.472066.n3.nabble.com/Solr-Data-Config-Queries-per-Field-tp4037092p4037189.html Sent from the Solr - User mailing list archive at Nabble.com.
