Nested / subquery / Inner queries in Solr with facets

[Vamshidhar Gandham <vg...@salesforce.com>]

Hi,

Can somebody help me with this on Solr querying capabilities about nested / subquery / inner queries with facets. Here is my use case:

1) Fetch unique jobs for a given team:
<solrhost>/solr/select/?q=teamname%3ANinjas+AND+jobname:*&facet=true&facet.field=jobname&facet.zeros=false&rows=0

Each unique job returned in the above query will be substituted against the jobname in the below query:

2) Job info of latest build ID for a given team and job:
<solrhost>/solr/select/?q=teamname:Mobile&jobname:<JOBNAME>&buildnumber:*&sort=buildnumber%20desc&rows=1

My ultimate goal using these 2 queries is to fetch those solr docs/data for a given teamname pointing to Max (most recent) buildnumber for all the Unique jobs related to given teamname. If a team has n unique jobs then with the above two step approach I am expected to fire (n+1) queries which is performance overhead.

If I were to fetch this data from a RDBMS using a single query, the SQL query would look something like this:

Select * form <table> where teamname='Ninjas' AND jobname IN ( select DISTINCT jobname from TABLE ) ;

One way to approach this problem is to frame a nested query by merging both the above queries. If I query something like the one below, i will receive all the solr docs in the response and cannot limit the docs being returned only to the one which have Max buildnumber, which would result in overhead of parsing and filtering of unnecessary  data:

<solrhost>/solr/select/?q=teamname:Ninjas&jobname:*&buildnumber:*&sort=buildnumber%20desc&rows=1000

So, can somebody help me with forming a compound / nested query for fetching the data I want in a single request? Or perhaps may be any work around / solr feature you may have used to overcome such use case?



Also does Solr support stored procedure or triggers?


Thanks & Regards
Vamshi