I have to perform the following SQL query on a Solr collection:
select c.CONTRACT
from contracts c
where c.LIMIT_TYPE = 4
and c.TRANSACTION_DATE_DB = (select max(TRANSACTION_DATE_DB) from contracts c2 where c.CONTRACT = c2.CONTRACT and c2.LIMIT_TYPE = 4)
and c.STATUS = 'ENABLED'
group by c.CONTRACT
order by c.CONTRACT desc;
The objective of the query is the following:
- the subquery find, for each contract, the record with last transaction date for limit type 4
- the upper query checks if, for each contract, if the record returned has status enabled.
I've seen that there are two possible ways to perform this query using solr: join and subquery
Here below I'll paste my 100th attempt using subquery:
http://localhost:8983/solr/contracts_collection/select?
q=*:*
&indent=true
fq=LIMIT_TYPE:4&
fq=STATUS:ENABLED
&fl=*,mySub:[subquery]
&mySub.q=*
&mySub.fq={!term f=CONTRACT_IDENTITY v=$row.CONTRACT_IDENTITY}
&mySub.fq=LIMIT_TYPE:4
&mySub.fl=CONTRACT_IDENTITY,TRANSACTION_DATE_DB,LIMIT_TYPE
&mySub.sort=TRANSACTION_DATE_DB+desc
&mySub.rows=1
The subquery is correct. However, for each contract, the result contains all the records for that contract. In fact, I'm missing here how to make the upper query return, for each contract, only the record having the same transaction_date_db returned by the subquery.
I tried to add the following condition:
&mySub.fq={!term f=TRANSACTION_DATE_DB v=$row.TRANSACTION_DATE_DB}
But still, I don't have the desired result.
I've also saw join, but I've failed miserably :P
Does anyone have any suggestion? Going crazy for two days now. Thanks a lot