CODEWITHSUNDEEP
solr
Daniel Kaplan
Daniel Kaplan

Reputation: 67300

Solr: How do I join on a subquery?

I've found documentation on joins and documentation on subqueries, but I'm not sure how to combine them.

I want to search for something by id (this is a variable that's passed in). That result will have a sectionpath. I then want to find all the documents that start with that sectionpath. Can I do this in one query?

Here's what I've tried so far:

sectionpath:_query_:"{!fl=sectionpath}id:<variable>"*

I know this doesn't work, but here's the intent of this: give me results where sectionpath starts with (that's what the * is for) the sectionpath of a document that has the id of <variable>. I know this is far from working, but I'm not sure how to get close to my intent.

I've also tried something like this:

{!join from=sectionpath to=sectionpath}_query_:"{!fl=sectionfullpath}id:<variable>"

But this seems to be returning close to everything. It doesn't seem to be restricting by sectionpath.

Upvotes: 3

Views: 2679

Answers (1)

tozlu
tozlu

Reputation: 4865

There are multiple phases to achieve desired result but in short; yes it's possible to use a field's value in a single query.

We are going to use [subquery]* transformer and after that {!prefix}* query parser. Also, we will use a field value as input in subquery.

First, query the documents whose values you will use in subquery:

q=id:123

or if you will only use id to filter main documents you can use filter query;

q=*:*
fq=id:123

Specify subquery and choose the fields you want to retrieve for main documents:

fl=id,section_path,childpaths:[subquery]

Provide query for subquery, also using main document field (in raw query params):

childpaths.q={!term f=section_path v=$row.section_path}

Here we are using !term query parser to indicate the field we want to search on. Also by using v we are referencing an auto variable named $row to get the main document and its section_path value.

Remember, this will construct the query like section_path:xyz but we want to get documents whose section_path values start with main records'. Basically we want to construct a query like section_path:xy*. Luckily we have a !prefix query parser for that. So change the childpaths.q to;

childpaths.q={!prefix f=name_en v=$row.name_en}

Specify child document fields you want to retrieve (also in raw query params):

childpaths.fl=id,section_path

You can also limit child documents to get only 5 rows like so;

childpaths.rows=5

And you can modify other query parameters too, just like a regular query.

Fire the request and voila!

Here is the full query:

q=*:*&fq=id:123&fl=id,section_path,childpaths:[subquery]&childpaths.q={!prefix f=name_en v=$row.name_en}&childpaths.fl=id,section_path

Upvotes: 8

Related Questions