Normalize response through multiple joins in a single query with solr

Question

It's possible to perform a query to the following solr collection

{
    "id":1,
    "book_name":"Solr book",
    "category":[1,2,3],
    "author":[1]
},
{
    "id":1,
    "book_name":"Rocky",
    "category":[3],
    "author":[2]
},
{
    "category_id": 1,
    "category_name":"Technology"
},
{
    "category_id": 2,
    "category_name":"Apache Lucene"
},
{
    "category_id": 3,
    "category_name":"Sport"
},
{
    "author_id": "1",
    "author_name" "Chuck Norris"
},
{
    "author_id": "1",
    "author_name" "John Rambo"
}

which will return something like:

{
    "id": 1,
    "book_name": "Solr book",
    "categories": ["Technology", "Apache Lucene"],
    "Authors": ["Chuck Norris"]
}

searching by book_name AND/OR category_name AND/OR author name in a typical Searchbox input? or an alternative response, which I will return something similar?

Otherwise which is the preferred workaround/best practice to achieve it?

...we would like to perform a single query through out the Frontend web App without using the Backend web App to structure the response, because that data is already in solr.

do you have this type data on development environment or its on the production environment...? — Abhijit Bashetti, May 27 '16 at 06:42
it's in development environment. But I already found the solution here http://wiki.apache.org/solr/DataImportHandler#Full_Import_Example — jj-aa, May 27 '16 at 06:48
Could you share what exactly been done or helped you to resolve this...? — Abhijit Bashetti, May 27 '16 at 06:57

score 0 · Answer 1 · answered May 27 '16 at 07:55

The way I've settled up the collection was wrong, I did it this way:

<dataConfig>
  <dataSource type="JdbcDataSource"
            driver="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/circulo_lectores"
            user=""
            password="" /> 
  <document>
        <entity name="book" query="SELECT * FROM book">

            <field column="id" name="id"/>
            <field column="book" name="book" />

            <entity name="book_author" query="SELECT * FROM book_author WHERE book_id= ${book.id}">         
                <field column="author_id" name="author_id" />
                <entity name="author" query="select * FROM author WHERE id = ${book_author.author_id}">
                    <field column="author_name" name="author_name" />
                </entity>                   
            </entity>   

            <entity name="book_category" query="SELECT * FROM book_category WHERE book_id= ${book.id}">         
                <field column="category_id" name="category_id" />
                <entity name="category" query="select * FROM category WHERE id = ${book_category.category_id}">
                    <field column="category_name" name="category_name" />
                </entity>                   
            </entity>               

        </entity>   
  </document>
</dataConfig>

This reference gave me the clue. Hope it helps someon else

Normalize response through multiple joins in a single query with solr

1 Answers1