CODEWITHSUNDEEP
phpsqlsearchsolr5
Rakesh Shetty
Rakesh Shetty

Reputation: 4578

How to run a sql query in solr

Ok now I have successfully install solr and index by database with following structure : DB_NAME - solr, Table - users

enter image description here

my db-data-config.xml file :

<dataConfig>
  <dataSource type="JdbcDataSource"
            driver="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/solr"
            user="root"
            password="" /> 
  <document>
    <entity name="users" query="select id,name from users;" />

    <field column="ID" name="id" /> 
    <field column="NAME" name="name" />

  </document>
</dataConfig>

Which returns rows of database table users.

Problem 1 :

But what should I do if I want to search like 'Rakesh Shetty' and it should return result "Rakesh Vasant Shetty" ?

I have tried <entity name="users" query="select id,name from users where name like '%Rakesh Shetty%' ;" /> but it returns empty result.

Problem 2 :

Also, how can I make this search query dynamic ? I mean there will be a form with search textbox and it should query to solr ? I am using php and solr 5.1.0.

Thanks in advance.

Upvotes: 2

Views: 821

Answers (2)

Dimag Kharab
Dimag Kharab

Reputation: 4519

Refer : SOLR DIH USING CUSTOM VARIABLES

So basically you need to pass custom variables to your DIH.xml file like

http://localhost:8983/solr/{collection_name}/dataimport?command=full-import&name=Rakesh&commit=true

And in DIH file you can access this variable with ${dataimporter.request.name}, and use in your DIH query.

eg ,

 <entity name="users" query="select id,name from users where name ='${dataimporter.request.name}' ;" />

And for your dynamic search refer link below

http://solr.pl/en/2010/10/18/solr-and-autocomplete-part-1/

Upvotes: 0

Abhijit Bashetti
Abhijit Bashetti

Reputation: 8658

try withe the below schema.

<schema name="simple" version="1.5"> 

<types> 
<fieldType name="text" class="solr.TextField" positionIncrementGap="100" autoGeneratePhraseQueries="true">
<analyzer type="index">
<tokenizer class="solr.WhitespaceTokenizerFactory"/>
<filter class="solr.WordDelimiterFilterFactory" generateWordParts="1" generateNumberParts="1" catenateWords="1" catenateNumbers="1" catenateAll="0" preserveOriginal="1" splitOnCaseChange="1"/>
<filter class="solr.LowerCaseFilterFactory"/>

</analyzer>
<analyzer type="query">
<tokenizer class="solr.WhitespaceTokenizerFactory"/>
<filter class="solr.WordDelimiterFilterFactory" generateWordParts="1" generateNumberParts="1" catenateWords="0" catenateNumbers="0" catenateAll="0" splitOnCaseChange="0"/>
<filter class="solr.LowerCaseFilterFactory"/>
</analyzer>
</fieldType>


<fieldType name="int" class="solr.TrieIntField" precisionStep="0" omitNorms="true" positionIncrementGap="0"/> 
</types> 

<fields> 

<field name='id' type='int' required='true' indexed="true" stored="true"/> 
<field name='name' type='text' required='true' indexed="true" stored="true"/> 


</fields> 

<uniqueKey>id</uniqueKey> 


<defaultSearchField>name</defaultSearchField> 
<solrQueryParser defaultOperator='OR' /> 

</schema>

Upvotes: 1

Related Questions