{"id":215,"date":"2011-02-21T09:17:08","date_gmt":"2011-02-21T08:17:08","guid":{"rendered":"http:\/\/sematext.solr.pl\/?p=215"},"modified":"2020-11-11T09:17:41","modified_gmt":"2020-11-11T08:17:41","slug":"waiting-for-4-0-solr-2272-solr-and-join-functionality","status":"publish","type":"post","link":"https:\/\/solr.pl\/en\/2011\/02\/21\/waiting-for-4-0-solr-2272-solr-and-join-functionality\/","title":{"rendered":"Waiting for 4.0: SOLR-2272 – Solr and JOIN functionality"},"content":{"rendered":"

Recently, my attention caught the functionality described in the SOLR-2272<\/a> Jira ticket – functionality of SQL JOIN implemented in Solr. In today’s entry I take a look at this functionality.<\/p>\n\n\n\n\n\n

Preparation<\/h3>\n

The first thing we need to do is download the code from the main branch (trunk<\/em>) of Lucene\/Solr SVN repository and add the the patch attached to the SOLR-2272. After building Solr we can proceed to testing.<\/p>\n

Index structure<\/h3>\n

For testing purposes, I decided to define the following index structure:\n<\/p>\n

<fields>\n  <field name=\"id\" type=\"string\" indexed=\"true\" stored=\"true\" required=\"true\" \/>\n  <field name=\"name\" type=\"text\" indexed=\"true\" stored=\"true\"\/>\n  <field name=\"color\" type=\"string\" indexed=\"true\" stored=\"true\"\/>\n  <field name=\"parent\" type=\"string\" indexed=\"true\" stored=\"true\"\/>\n<\/fields><\/pre>\n
The structure is rather simple. The id<\/em> field is responsible for storing a unique identifier of the document. The  name<\/em> field holds the name of the document, the color<\/em> is the color of  the product, and the parent<\/em> field is the identifier for the parent  document.<\/p>\n
Test data<\/h3>\n
File with the sample data is as follows:\n<\/p>\n
<add>\n <doc>\n  <field name=\"id\">1<\/field>\n  <field name=\"name\">First document<\/field>\n  <field name=\"color\">Red<\/field>\n <\/doc>\n <doc>\n  <field name=\"id\">2<\/field>\n  <field name=\"name\">Second document<\/field>\n  <field name=\"color\">Yellow<\/field>\n <\/doc>\n <doc>\n  <field name=\"id\">3<\/field>\n  <field name=\"name\">Third document<\/field>\n  <field name=\"color\">Red<\/field>\n  <field name=\"parent\">1<\/field>\n <\/doc>\n <doc>\n  <field name=\"id\">4<\/field>\n  <field name=\"name\">Fourth document<\/field>\n  <field name=\"color\">Yellow<\/field>\n  <field name=\"parent\">1<\/field>\n <\/doc>\n<\/add><\/pre>\n
Query example<\/h3>\n
For the short tests I conducted I’ve chosen a fairly simple case. I wanted to get documents that are the parents of documents with the value Yellow <\/em>in the color<\/em> field. Nothing  fancy, at least in the meaning of SQL. To execute this query in the  standard Solr we would have to make at least two queries – first to get  all documents with the appropriate value in the color<\/em> field, and then their  parent documents. And how it looks when talking about patched Solr 4.0 ? Take a look at the following query:\n<\/p>\n
http:\/\/localhost:8983\/solr\/select?q={!join from=parent to=id}color:Yellow<\/pre>\n
Response for the above query looks like this:\n<\/p>\n
<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<response>\n<lst name=\"responseHeader\">\n  <int name=\"status\">0<\/int>\n  <int name=\"QTime\">1<\/int>\n  <lst name=\"params\">\n    <str name=\"q\">{!join from=parent to=id}color:Yellow<\/str>\n  <\/lst>\n<\/lst>\n<result name=\"response\" numFound=\"1\" start=\"0\">\n  <doc>\n    <str name=\"color\">Red<\/str>\n    <str name=\"id\">1<\/str>\n    <str name=\"name\">First document<\/str>\n  <\/doc>\n<\/result>\n<\/response><\/pre>\n
As you can see we got the results we wanted. But how does that query work ? As you can see in the example, apart from the standard query for a document with the given value in the field (color:Yellow<\/em>) I’ve added a strange looking fragment {!join from=parent to=id}<\/em>. This fragment tells Solr to show not the documents with the given field value, but those, which have the value in the id<\/em> field the same as the parent<\/em> field of the document that match the given query.<\/p>\n
Verification<\/h3>\n
Let’s see if the result we got are the right ones. First let’s fetch the documents that have Yellow<\/em> value in the color<\/em> field. To do that, we send the following query to Solr:\n<\/p>\n
http:\/\/localhost:8983\/solr\/select?q=color:Yellow<\/pre>\n
The response was as follows:\n<\/p>\n
<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<response>\n<lst name=\"responseHeader\">\n  <int name=\"status\">0<\/int>\n  <int name=\"QTime\">2<\/int>\n  <lst name=\"params\">\n    <str name=\"q\">color:Yellow<\/str>\n  <\/lst>\n<\/lst>\n<result name=\"response\" numFound=\"2\" start=\"0\">\n  <doc>\n    <str name=\"color\">Yellow<\/str>\n    <str name=\"id\">2<\/str>\n    <str name=\"name\">Second document<\/str>\n  <\/doc>\n  <doc>\n    <str name=\"color\">Yellow<\/str>\n    <str name=\"id\">4<\/str>\n    <str name=\"name\">Fourth document<\/str>\n    <str name=\"parent\">1<\/str>\n  <\/doc>\n<\/result>\n<\/response><\/pre>\n
So we got one document which have a value in the parent<\/em> field – this value is 1<\/em>. Document with id <\/em>field equals to 1<\/em> is the following one:<\/p>\n
 <\/em> <\/em>\n<\/p>\n
<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<response>\n<lst name=\"responseHeader\">\n  <int name=\"status\">0<\/int>\n  <int name=\"QTime\">4<\/int>\n  <lst name=\"params\">\n    <str name=\"indent\">true<\/str>\n    <str name=\"q\">id:1<\/str>\n  <\/lst>\n<\/lst>\n<result name=\"response\" numFound=\"1\" start=\"0\">\n  <doc>\n    <str name=\"color\">Red<\/str>\n    <str name=\"id\">1<\/str>\n    <str name=\"name\">First document<\/str>\n  <\/doc>\n<\/result>\n<\/response><\/pre>\n
As you can see, the JOIN query worked correctly \ud83d\ude09<\/p>\n
A few words at the end<\/h3>\n
Keep in mind that at the moment the functionality is under development and may change. The important this is that with Solr version 4.0 we will be able to enjoy the functionality similar to SQL JOIN.<\/p>","protected":false},"excerpt":{"rendered":"
Recently, my attention caught the functionality described in the SOLR-2272 Jira ticket – functionality of SQL JOIN implemented in Solr. In today’s entry I take a look at this functionality.<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_links_to":"","_links_to_target":""},"categories":[27],"tags":[305,306,164],"class_list":["post-215","post","type-post","status-publish","format-standard","hentry","category-solr-en","tag-2272-2","tag-join-2","tag-solr-2"],"_links":{"self":[{"href":"https:\/\/solr.pl\/en\/wp-json\/wp\/v2\/posts\/215","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/solr.pl\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/solr.pl\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/solr.pl\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/solr.pl\/en\/wp-json\/wp\/v2\/comments?post=215"}],"version-history":[{"count":1,"href":"https:\/\/solr.pl\/en\/wp-json\/wp\/v2\/posts\/215\/revisions"}],"predecessor-version":[{"id":216,"href":"https:\/\/solr.pl\/en\/wp-json\/wp\/v2\/posts\/215\/revisions\/216"}],"wp:attachment":[{"href":"https:\/\/solr.pl\/en\/wp-json\/wp\/v2\/media?parent=215"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/solr.pl\/en\/wp-json\/wp\/v2\/categories?post=215"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/solr.pl\/en\/wp-json\/wp\/v2\/tags?post=215"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}