CODEWITHSUNDEEP
solr
Avner Levy
Avner Levy

Reputation: 6751

Solr join "not in" subselect

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').

Upvotes: 4

Views: 2137

Answers (3)

Artem Titov
Artem Titov

Reputation: 61

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!)

Upvotes: 6

Haiying Wang
Haiying Wang

Reputation: 652

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)

Upvotes: 1

lebolo
lebolo

Reputation: 2160

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.

Upvotes: 0

Related Questions