Query join on multiple fields

[Haiying Wang <ha...@yahoo.com.INVALID>]

Can Solr query join on multiple fields? Or, anyway to achieve equivalent function with current join syntax?

From the Solr Wiki and examples found on the web, the syntax only supports join on one field from a doc to another field of other docs, like below:


{!join from=customer_name to=name}state:TX

We have two type of docs and want logically join on more than one field, and we want to retrieve one type of doc with filters on the other type of docs; the equivalent SQL query will be like

select * from order where exists (select 1 from customer where customer.name = order.customer_name and customer.address=order.ship_from_address and customer.state='TX')

Can the Solr query achieve this type of join? Or, any technical reason not support join on multiple fields?


Thanks,

Haiying

Re: Query join on multiple fields

[Erick Erickson <er...@gmail.com>]

Joins can be chained, don't quite know if that fits your use-case... But....

Whenever I see a question that looks like "How can I make Solr behave
like a database", I have to ask two questions:

1> Is Solr the right tool? It's a marvelous search engine, but not a RDBMS.
     if your problem really is only solvable by a database, use a database.

2> Can you re-think your index process, denormalize your data and not
     _need_ to think about joins in Solr? This is often the best option.

Best,
Erick


On Thu, Jul 17, 2014 at 10:35 PM, Haiying Wang <
haiyingwang1@yahoo.com.invalid> wrote:

> Can Solr query join on multiple fields? Or, anyway to achieve equivalent
> function with current join syntax?
>
> From the Solr Wiki and examples found on the web, the syntax only supports
> join on one field from a doc to another field of other docs, like below:
>
>
> {!join from=customer_name to=name}state:TX
>
> We have two type of docs and want logically join on more than one field,
> and we want to retrieve one type of doc with filters on the other type of
> docs; the equivalent SQL query will be like
>
> select * from order where exists (select 1 from customer where
> customer.name = order.customer_name and
> customer.address=order.ship_from_address and customer.state='TX')
>
> Can the Solr query achieve this type of join? Or, any technical reason not
> support join on multiple fields?
>
>
> Thanks,
>
> Haiying