Solr join "not in" subselect

Question

In the Solr join documentation Solr Join they say that:

/solr/collection1/select ? fl=xxx,yyy & q={!join from=inner_id to=outer_id}zzz:vvv

is equivalent to:

SELECT xxx, yyy
FROM collection1
WHERE outer_id IN (SELECT inner_id FROM collection1 where zzz = "vvv")

How do I write in Solr (see the NOT):

SELECT xxx, yyy
FROM collection1
WHERE outer_id NOT IN (SELECT inner_id FROM collection1 where zzz = "vvv")

Lets consider the following example:
People Records:

1. name='a', id=1, teacherId=4
2. name='b', id=2, teacherId=4
3. name='c', id=3, teacherId=1
4. name='d', id=4, isTeacher='true'

Now I want to select all students which their teacherId points to non teacher ID (record #3).
In SQL:

select * from people where teacherId not in (select id where isTeacher='true').

http://stackoverflow.com/questions/11855830/how-to-do-not-in-query-in-solr — highland, Jul 09 '14 at 10:54
This doesn't help me since I need to collect the IDs (select id from Y) by another Solr query @highland — Avner Levy, Jul 09 '14 at 10:55
Well, that's what a fq= does, so what kind of query are we talking about here? — MatsLindh, Jul 09 '14 at 11:37
fq adds an additional filter with AND to the main query. I want to fetch all green objects which their owner field isn't in another solr query. @fiskfisk — Avner Levy, Jul 09 '14 at 12:05
@AvnerLevy Shouldn't that be the same as a negative query with the same as you'd put in the WHERE field of the inner query? Do you have a few rows showing the exact issue? (I see where you're going, but I fail to see how the negative query would fail) — MatsLindh, Jul 10 '14 at 13:10
@fiskfisk In my example I have two type of documents. X and X_mirror, where for each X document I create a X_mirror mirror document. Then part of the X documents are removed and I want to find all the X_mirror objects which are redundant (since their original X document was removed). — Avner Levy, Jul 10 '14 at 13:47
@AvnerLevy Ah, thanks for the explanation! I'm not sure if that's possible at the moment, there's been a few unanswered questions regarding the same issue. If there was some way to get a dynamic lookup for the docfreq with the value from another field in the document (instead of a static value), that would be a solution .. but there doesn't seem to be a way at the moment. You might want to try the mailing list for this one. — MatsLindh, Jul 10 '14 at 21:16

score 6 · Answer 1 · answered Nov 28 '14 at 14:56

6

I'm faced with this problem too and that's how I resolved it:

q=-_query_:"{!join from=inner_id to=outer_id}zzz:vvv"

You can add this nested query to fq too.

Hope that helps!)

answered Nov 28 '14 at 14:56

Artem Titov

61
1
3

This solution works also when the query contains parenthesis. You also have to escape double quotes (replace " with \") if they appear in the query – MscG Dec 31 '14 at 14:03
This is better than using the join in the Q param in my oppinion, Thanks :) – Mathieu de Lorimier Jun 21 '19 at 19:27

Haiying Wang · Accepted Answer · 2014-08-22T19:01:29.060

1

Please try put "-" outside of the whole join filter, like below:

/solr/collection1/select ? fl=xxx,yyy & q=-({!join from=inner_id to=outer_id}zzz:vvv)

edited Aug 22 '14 at 19:01

answered Aug 22 '14 at 18:16

Haiying Wang

652
7
10

score 0 · Answer 3 · answered Jul 11 '14 at 18:32

0

If

SELECT xxx, yyy
FROM collection1
WHERE outer_id NOT IN (SELECT inner_id FROM collection1 where zzz = "vvv")

is equivalent to

SELECT xxx, yyy
FROM collection1
WHERE outer_id IN (SELECT inner_id FROM collection1 where zzz != "vvv")

where NOT IN becomes IN and zzz = "vvv" becomes zzz != "vvv", then negating the actual query should work.

/solr/collection1/select ? fl=xxx,yyy & q={!join from=inner_id to=outer_id}-zzz:vvv

Note the -zzz:vvv.

answered Jul 11 '14 at 18:32

lebolo

2,120
4
29
44

Thanks for your answer, but please see comment about the mirror example. This answer doesn't answer this scenario. – Avner Levy Jul 13 '14 at 20:03

Solr join "not in" subselect

3 Answers3

Linked