Menu
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.
255
Is it possible to search in Solr over two fields using two different words and get back only those results which contain both of them? For example, if I have fields "type" and "location" , I want only those results who have type='furniture' and location = 'office' in them. You can use boolean operators and search on individual fields.
Can you LEFT JOIN three tables in SQL? Yes, indeed! You can use multiple LEFT JOINs in one query if needed for your analysis. In this article, I will go through some examples to demonstrate how to LEFT JOIN multiple tables in SQL and how to avoid some common pitfalls when doing so.
Because non-matching rows in the right table are filled with the NULL values, you can apply the LEFT JOIN clause to miss-match rows between tables. For example, to find the country that does not have any locations in the locations table, you use the following query:
Yes, indeed! You can use multiple LEFT JOINs in one query if needed for your analysis. In this article, I will go through some examples to demonstrate how to LEFT JOIN multiple tables in SQL and how to avoid some common pitfalls when doing so.
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?
70
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
