CODEWITHSUNDEEP
mysqljoinsolr
Sumeet Sharma
Sumeet Sharma

Reputation: 2583

Replicate Left join on multiple fields in solr

I have a mysql query:

select t1.to_step,count(t1.to_step) from tmp t1 left join tmp t3 on 
(t1.to_step = t3.from_step and t1.applicant_id=t3.applicant_id)
where t3.to_step is null group by t1.to_step

I am trying to do the above in solr using joins. I know joins in solr work like nested query but i am not able to figure out a proper way to get all the records as i get from the mysql query.

below is what i am using:

q: "-_query_:\"{!join from=from_step_s to=to_step_s}from_step_s:[* TO *]\"",

This gives me partial set of results. Basically my solr document consists of fields applicant_id, from_step_s and to_step_s and I want to get the document where a join from a to_step_s to from_step_s doesn't exist for a particular set of applicant_id. I think the problem is somewhere because of the applicant_idjoin not done in the solr query (which i dont know how to do) because of which the from_step_s of one document gets matched to to_step_s of a different document with different applicant_id.

Upvotes: 6

Views: 1849

Answers (1)

Karsten R.
Karsten R.

Reputation: 1758

Your question is about a join based on two fields (on each side).

The short answer: You cannot do this.

The main logic for JoinQuery is in org.apache.solr.search.JoinQuery.JoinQueryWeight.getDocSet(). As you will see there is no use of stored fields or a search which you could change from one field to two fields.

The Erick Erickson answer:

You should not do this.

Solr lives from de-normalization. Why not add a new field and concat the old ones? Why not use your sql join at indexing time and add the information you need directly to the index?

Upvotes: 2

Related Questions