Doing complex multi-table search queries using Solr

Question

I just started learning Solr and the reason I learned it is because I want to do advance search queries (something considered simple in SQL) but on large amount of data. From what I read up to now (using solarium) I can index, update, select and delete but only on a single kind of data (relation/table). What I would like to do is to be able to perform operation between table (like SQL would in his own way). Here is an example scenario of what I could be working on.

Here are samples of data based on the relation above.

<root>
    <!-- ID for Solr --->
    <id>some_id</id>
    <table>house</table>
    <house_id>1</house_id>
    <house_name>Gryffindor</house_name>
</root>

<root>
    <!-- ID for Solr --->
    <id>some_other_id</id>
    <table>student</table>
    <student_id>1</student_id>
    <firstname>Albus</firstname>
    <lastname>Dumbledore</lastname>
    <house_id>1</house_id>
</root>

<root>
    <!-- ID for Solr --->
    <id>some_different_id</id>
    <table>battle</table>
    <student_id_1>1</student_id_1>
    <student_id_2>3</student_id_2>
</root>

An example search query would be "full name of students from different houses who fought each other and the name of their respective house.

In SQL I would do something like:

SELECT * FROM houses housA, students studA, houses housB, students studB, battles
WHERE studA.id_house == housA.house_name AND studB.id_house == housB.house_name AND
((studA.id == battles.id_1 AND studB.id == battles.id_2) OR (studA.id == battles.id_2 AND studB.id == battles.id_1));

And the solution would be every field (all three tables) for Dumbledore vs Snape and Potter vs Who.

Can it be done with Solr?

Answer 1

You have to think about Solr backwards starting from the queries. And then you flatten the information to match your needs. In your case, it seems the entity in Solr would be a fight and then you flatten all the other information (house, name, etc) into that record. That allows you to do queries like "what house had most matches", etc.

Solr also support nested documents, but their use is not quite the same as with database joins and does not seem to match your use case. I am just mentioning it there for you to be aware of it.