7.15.12. in_records

7.15.12.1. Summary

New 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.12.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.12.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.12.4. Parameters

in_records requires four or more parameters.

7.15.12.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.12.4.1.1. condition_table

Specifies a table that has conditions as its records.

7.15.12.4.2. value

Specifies a value to be compared.

7.15.12.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.12.4.4. condition_column_name

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

7.15.12.4.5. Optional parameter

There are no optional parameter.

7.15.12.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.

previous

7.15.11. html_untag

next

7.15.13. in_values