CODEWITHSUNDEEP
joinsolr
Janie
Janie

Reputation: 646

Select join table fields in SOLR

I have a SQL query something like this

SELECT
    P . ID,
    P .code,
    l.parent_id
FROM
    properties P
LEFT JOIN locations l ON l. ID = P .location_id;

I want to convert this query to SOLR query. I can join two cores by below system

http://example.com:8999/solr/properties/select?q=*:*&fq={!join from=id to=location_id fromIndex=locations}p_id:12345

But I cant select the fields of locations core.How can I do this? Your valuable suggestion will be appreciated.

Upvotes: 3

Views: 2242

Answers (2)

user3389
user3389

Reputation: 489

You can use subquery in the fl. Something like this fl=*,locations:[subquery fromIndex=locations]&locations.q={!terms f=id v=$row.location_id} More info here https://lucene.apache.org/solr/guide/6_6/transforming-result-documents.html#TransformingResultDocuments-subquery

Upvotes: 4

MatsLindh
MatsLindh

Reputation: 52912

You can't. Solr does not support returning fields from both ends of an join. Solr is not a relational database, so you're usually better off trying to not use it as one.

Instead, index the information about each location to each property, and query based on that.

If any location info changes (which it turns out, usually happens very rarely), reindex the documents assigned that location.

Upvotes: 2

Related Questions