CODEWITHSUNDEEP
joinsolr
user3209385
user3209385

Reputation: 61

How to use join with sort in Solr?

I'm trying to sort documents of type 'Case' by the 'Name' of the 'Contact' they belong to in Solr. But cases have no 'ContactName' field or similar, only 'ContactId'.

Only examples I could find are iterations of the example on this link: https://wiki.apache.org/solr/Join But I couldn't apply it to my situation because of the sorting afterwards. The following gives me the cases I want but I can't sort it by the contact name afterwards because it only returns the fields of the cases.

{!join from=Id to=ContactId}*:*

SQL equivalent of what I want would be something like:

SELECT Case.Id, Contact.Name
FROM Case
LEFT JOIN Contact
ON Case.ContactId = Contact.Id
ORDER BY Contact.Name ASC;

Upvotes: 3

Views: 769

Answers (1)

user3209385
user3209385

Reputation: 61

So to answer my own question after some digging and a Solr training:

It is not best practice to use joins in a NoSql database like Solr. If you need joins, then your database is structured wrong. You should index everything you need, in the document itself, even if it is redundant. So in my case, I should index 'Contact.Name' field in my 'Case' documents.

Still, it is apparently possible to use SQL queries in Solr in case it is absolutely needed, if you're using SolrCloud but it doesn't support joins. However it is possible to work around that as follows:

SELECT s1.Id 
FROM salesforce s1, salesforce s2 
WHERE s1._type_ = 'Case' and s2._type_ = 'Contact' AND s1.ContactId = s2.Id 
ORDER BY s2.Name ASC

It should be noted that the fields after '.' like the 'Id' in 's1.Id' must have 'docValues' activated in the schema. More info on docValues is here.

Upvotes: 3

Related Questions