HAYASHI Kentaro
null+****@clear*****
Fri Nov 30 13:50:28 JST 2012
HAYASHI Kentaro 2012-11-30 13:50:28 +0900 (Fri, 30 Nov 2012) New Revision: cb845d413be1cce9c427eb74ecea837d3d11fca7 https://github.com/groonga/groonga/commit/cb845d413be1cce9c427eb74ecea837d3d11fca7 Log: doc en: add documentation about nested index search Modified files: doc/source/tutorial/match_columns.txt Modified: doc/source/tutorial/match_columns.txt (+125 -0) =================================================================== --- doc/source/tutorial/match_columns.txt 2012-11-29 11:28:52 +0900 (49b6e38) +++ doc/source/tutorial/match_columns.txt 2012-11-30 13:50:28 +0900 (d060100) @@ -83,6 +83,131 @@ match_columnsオプションで、検索対象のカラムを複数指定する .. TODO: match_columnsにインデックス名を指定しての検索についても触れる。 + +Nested index search among related table by column index +-------------------------------------------------------- + +If there are relationships among multiple table with column index, +you can search multiple table by specifying reference column name. + +Here is the concrete example. + +There are tables which store blog articles, comments for articles. +The table which stores articles has columns for article and comment. +And the comment column refers Comments table. +The table which stores comments has columns for comment and column index to article table. + +if you want to search the articles which contain specified keyword in comment, +you need to execute fulltext search for table of comment, then search the records which contains fulltext search results. + +Now, you can search the records by specifying the reference column index at once. + +Here is the sample schema. + +.. groonga-command +.. include:: ../example/tutorial/match_columns-nested-index-schema.log +.. table_create Comments TABLE_HASH_KEY UInt32 +.. column_create Comments content COLUMN_SCALAR ShortText +.. table_create Articles TABLE_NO_KEY +.. column_create Articles content COLUMN_SCALAR Text +.. column_create Articles comment COLUMN_SCALAR Comments +.. table_create Lexicon TABLE_PAT_KEY|KEY_NORMALIZE ShortText --default_tokenizer TokenBigram +.. column_create Lexicon articles_content COLUMN_INDEX|WITH_POSITION Articles content +.. column_create Lexicon comments_content COLUMN_INDEX|WITH_POSITION Comments content +.. column_create Comments article COLUMN_INDEX Articles comment + +Here is the sample data. + +.. groonga-command +.. include:: ../example/tutorial/match_columns-nested-index-data.log +.. load --table Comments +.. [ +.. {"_key": 1, "content": "I'm using groonga too!"}, +.. {"_key": 2, "content": "I'm using groonga and mroonga!"}, +.. {"_key": 3, "content": "I'm using mroonga too!"} +.. ] +.. load --table Articles +.. [ +.. {"content": "Groonga is fast!", "comment": 1}, +.. {"content": "Groonga is useful!"}, +.. {"content": "Mroonga is fast!", "comment": 3} +.. ] + +You can write the query that search the records which contains specified keyword as a comment, then fetch the articles which refers to it. + +Query for searching the records described above:: + + select Articles --match_columns comment.content --query groonga --output_columns "_id, _score, *" + +You need to concatenate comment column of Articles table and content column of Comments table with period(.) as --match_columns arguments. + +At first, this query execute fulltext search from content of Comments table, then fetch the records of Articles table which refers to already searched records of Comments table. +(Because of this, if you comment out the query which create column index 'article' of Comments table, you can't get intended search results.) + +.. groonga-command +.. include:: ../example/tutorial/match_columns-nested-index-select.log +.. select Articles --match_columns comment.content --query groonga --output_columns "_id, _score, *" + +Now, you can search articles which contains specific keywords as a comment. + +The feature of nested index search is not limited to the relationship between two table only. + +Here is the sample schema similar to previous one. The difference is added table which express 'Reply' and relationship is extended to three tables. + +.. groonga-command +.. include:: ../example/tutorial/match_columns-nested-index-schema2.log +.. table_create Replies2 TABLE_HASH_KEY UInt32 +.. column_create Replies2 content COLUMN_SCALAR ShortText +.. table_create Comments2 TABLE_HASH_KEY UInt32 +.. column_create Comments2 content COLUMN_SCALAR ShortText +.. column_create Comments2 comment COLUMN_SCALAR Replies2 +.. table_create Articles2 TABLE_NO_KEY +.. column_create Articles2 content COLUMN_SCALAR Text +.. column_create Articles2 comment COLUMN_SCALAR Comments2 +.. table_create Lexicon2 TABLE_PAT_KEY|KEY_NORMALIZE ShortText --default_tokenizer TokenBigram +.. column_create Lexicon2 articles_content COLUMN_INDEX|WITH_POSITION Articles2 content +.. column_create Lexicon2 comments_content COLUMN_INDEX|WITH_POSITION Comments2 content +.. column_create Lexicon2 replies_content COLUMN_INDEX|WITH_POSITION Replies2 content +.. column_create Comments2 article COLUMN_INDEX Articles2 comment +.. column_create Replies2 reply_to COLUMN_INDEX Comments2 comment + +Here is the sample data. + +.. groonga-command +.. include:: ../example/tutorial/match_columns-nested-index-data2.log +.. load --table Replies2 +.. [ +.. {"_key": 1, "content": "I'm using rroonga too!"}, +.. {"_key": 2, "content": "I'm using groonga and mroonga and rroonga!"}, +.. {"_key": 3, "content": "I'm using nroonga too!"} +.. ] +.. load --table Comments2 +.. [ +.. {"_key": 1, "content": "I'm using groonga too!", "comment": 1}, +.. {"_key": 2, "content": "I'm using groonga and mroonga!", "comment": 2}, +.. {"_key": 3, "content": "I'm using mroonga too!"} +.. ] +.. load --table Articles2 +.. [ +.. {"content": "Groonga is fast!", "comment": 1}, +.. {"content": "Groonga is useful!", "comment": 2}, +.. {"content": "Mroonga is fast!", "comment": 3} +.. ] + +Query for searching the records described above:: + + select Articles2 --match_columns comment.content --query mroonga --output_columns "_id, _score, *" + select Articles2 --match_columns comment.comment.content --query mroonga --output_columns "_id, _score, *" + +The first query searches 'mroonga' from Comments2 table, the second one searches 'mroonga' from Replies2 and Comment2 table by using reference column index. + +First .. groonga-command +.. include:: ../example/tutorial/match_columns-nested-index-select2.log +.. select Articles2 --match_columns comment.content --query mroonga --output_columns "_id, _score, *" +.. select Articles2 --match_columns comment.comment.content --query mroonga --output_columns "_id, _score, *" + +As a result, the first query matches two article, but the second one matches one article only. + インデックスの重み ------------------ -------------- next part -------------- HTML����������������������������...ダウンロード
