You are viewing a plain text version of this content. The canonical link for it is here.
Posted to solr-user@lucene.apache.org by "O. Olson" <ol...@yahoo.it> on 2013/01/29 18:12:34 UTC

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;Categories&quot;  
                    query=&quot;SELECT CategoryName from CAT_TABLE where
SKU='${Product.SKU}' AND CategoryLevel=1&quot;
                &lt;field column=&quot;Category1&quot;
name=&quot;Category1&quot; />
				 ????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

Posted by "O. Olson" <ol...@yahoo.it>.
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.

Re: Solr Data Config Queries per Field

Posted by Gora Mohanty <go...@mimirtech.com>.
On 29 January 2013 23:34, O. Olson <ol...@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=<query>&facet=true&facet.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=<query>&facet=true&fq=Category1:Hardware&facet.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

Posted by "O. Olson" <ol...@yahoo.it>.
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=<query>&facet=true&facet.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=<query>&facet=true&fq=Category1:Hardware&facet.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

Posted by Gora Mohanty <go...@mimirtech.com>.
On 29 January 2013 22:42, O. Olson <ol...@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