If you flatten this to make a single table with rows for each combination of
parent, child1, and child2, the query will be simple and probably very fast.
You've said it makes more sense to have three tables, and that is true for a
relational database. For a search engine, like Solr, it makes more sense to
flatten the data.
wunder
On Jul 6, 2013, at 11:22 AM, mihaela olteanu wrote:
> Hello,
>
> I have 3 indices that form a hierarchy. Basically these were constructed from
> 3 tables: parent, child1 and child2 and between parent and children there is
> a one to many relationship.
> parent (id,name)
>
> child1(id,join_key,field1)
>
> child2(id,join_key,field2)
>
>
> "join_key" is the foreign key in each of the child tables
>
> I would like to perform searches like the following: "give me the parents
> whose name is x or whose child1.field1 is y or child2.field2 is z".
>
> I tried with a query like the following:
> #first attempt
> /parent/select?q=(name:x) OR ({!join from=join_key to=id
> fromIndex=child1}field1:y) OR ({!join from=join_key to=id
> fromIndex=child2}field2:z)
>
> but it didn't work.
>
> If I put all these documents in a single index and I add also a type field
> whose value would be "Parent" for documents that come from parent table,
> "Child1" and "Child2" for the documents that come from the table child1 and
> child2 respectively I can perform the following query to get the results that
> I want:
>
>
> /parent/select?q={!join from=join_key to=id}field1:y OR field2:z
>
> The most important thing about this query is that I want to have back as
> results only documents of type "Parent"
>
> With the previous query I could not capture though also the search of parents
> whose name equals "x", but if i do a small trick and add a "join_key" field
> in documents of type "Parent" whose value equals the value in "id" field then
> I could write something like:
>
> /parent/select?q={!join from=join_key to=id}field1:y OR field2:z OR name:x
>
>
> and had all the results that I wanted.
>
> But in my use case it makes more sense to have 3 different indices and not
> storing all the documents in the same index.
>
> Does anyone know if I can somehow enable the first query that I gave as
> example above? I would say that it would be great to have such a feature in
> the next versions of Solr 4.x
>
> Thanks,
> Mihaela
