7.15.13. in_records#

7.15.13.1. Summary#

Added in version 7.0.2.

You can use in_records for using an existing table as condition patterns. Each record in the existing table is treated as a condition pattern.

You may be able to reduce multiple queries to only one query by in_records.

in_records is similar to sub_filter. Here are differences of them:

  • sub_filter requires a reference column to condition table but in_records doesn’t require.

  • sub_filter requires an index column for the reference column but in_records doesn’t require.

  • sub_filter can use all logical operations but in_records can use only AND logical operation in one pattern.

  • sub_filter can use only the value of one reference column for condition but in_records can use one or more values for condition. You can use multiple columns, functions and so on for multiple values.

  • sub_filter uses index search but in_records uses sequential search.

7.15.13.2. Syntax#

in_records has four or more parameters:

in_records(condition_table,
           value1, mode1, condition_column_name1,
           ...,
           valueN, modeN, condition_column_nameN)

7.15.13.3. Usage#

Here is a schema definition and sample data.

Sample schema:

Execution example:

table_create Tags TABLE_PAT_KEY ShortText
# [[0,1337566253.89858,0.000355720520019531],true]
table_create Conditions TABLE_PAT_KEY ShortText
# [[0,1337566253.89858,0.000355720520019531],true]
column_create Conditions user_pattern COLUMN_SCALAR ShortText
# [[0,1337566253.89858,0.000355720520019531],true]
column_create Conditions tag COLUMN_SCALAR Tags
# [[0,1337566253.89858,0.000355720520019531],true]
column_create Conditions max_length COLUMN_SCALAR UInt32
# [[0,1337566253.89858,0.000355720520019531],true]
table_create Memos TABLE_HASH_KEY ShortText
# [[0,1337566253.89858,0.000355720520019531],true]
column_create Memos user COLUMN_SCALAR ShortText
# [[0,1337566253.89858,0.000355720520019531],true]
column_create Memos tag COLUMN_SCALAR Tags
# [[0,1337566253.89858,0.000355720520019531],true]

Sample data:

Execution example:

load --table Memos
[
{"_key": "Groonga is fast",          "user": "alice", "tag": "groonga"},
{"_key": "Mroonga is fast",          "user": "alice", "tag": "mroonga"},
{"_key": "Groonga is very good!",    "user": "alice", "tag": "groonga"},
{"_key": "Droonga is fast",          "user": "david", "tag": "droonga"},
{"_key": "Groonga is a HTTP server", "user": "david", "tag": "groonga"}
]
# [[0,1337566253.89858,0.000355720520019531],5]

Sample conditions:

Execution example:

load --table Conditions
[
{"_key": "lic + groonga", "user_pattern": "lic", "tag": "groonga", max_length: 20},
{"_key": "dav + droonga", "user_pattern": "dav", "tag": "droonga", max_length: 50}
]
# [[0,1337566253.89858,0.000355720520019531],2]

Here is a simple usage of in_records that searches by records in Conditions table. Each record is used as a condition:

Execution example:

plugin_register functions/string
# [[0,1337566253.89858,0.000355720520019531],true]
select \
  --table Memos \
  --filter 'in_records(Conditions, \
                       user,                 "@", "user_pattern", \
                       tag,                 "==", "tag", \
                       string_length(_key), "<=", "max_length")' \
  --sort_by _id \
  --output_columns _key,user,tag
# [
#   [
#     0,
#     1337566253.89858,
#     0.000355720520019531
#   ],
#   [
#     [
#       [
#         2
#       ],
#       [
#         [
#           "_key",
#           "ShortText"
#         ],
#         [
#           "user",
#           "ShortText"
#         ],
#         [
#           "tag",
#           "Tags"
#         ]
#       ],
#       [
#         "Groonga is fast",
#         "alice",
#         "groonga"
#       ],
#       [
#         "Droonga is fast",
#         "david",
#         "droonga"
#       ]
#     ]
#   ]
# ]

The filter tries the following three conditions for each record:

  • Memos.user matches (@) Conditions.user_pattern.

  • Memos.tag equals (==) Conditions.tag.

  • The number of characters in Memos._key is less than or equals to Conditions.max_length.

If at least one record in Conditions table returns true for the all three conditions, the record in Memos is matched.

The first record in Conditions table use the following conditions:

  • Memos.user has "lic" substring.

  • Memos.tag is "groonga".

  • The number of characters in Memos._key is less than or equals to 20.

This condition matches the following records:

  • {"_key": "Groonga is fast", "user": "alice", "tag": "groonga"}

The second record in Conditions table use the following conditions:

  • Memos.user has "dav" substring.

  • Memos.tag is "droonga".

  • The number of characters in Memos._key is less than or equals to 50.

This condition matches the following records:

  • {"_key": "Droonga is fast", "user": "david", "tag": "droonga"}

The result has the all above records.

7.15.13.4. Parameters#

in_records requires four or more parameters.

7.15.13.4.1. Required parameters#

condition_table and tuples of value, mode_name and condition_column_name are required. You can specify multiple tuples of value, mode_name and condition_column_name

7.15.13.4.1.1. condition_table#

Specifies a table that has conditions as its records.

7.15.13.4.2. value#

Specifies a value to be compared.

7.15.13.4.3. mode_name#

Specifies a mode name that specifies how to compare value with a value of condition_column_name.

See default_mode for available mode names. All mode names except "NEAR", "SIMILAR" and "SUFFIX" are supported.

7.15.13.4.4. condition_column_name#

Specifies a column name of condition_table to be used as condition.

7.15.13.4.5. Optional parameter#

There are no optional parameter.

7.15.13.5. Return value#

in_records returns whether the record is matched one of records of the specified condition table or not.

If the record is matched, it returns true. Otherwise, it returns false.