.. -*- rst -*-

.. highlightlang:: none

.. groonga-command
.. database: logical_select

``logical_select``
==================

Summary
-------

.. note::

   This command is an experimental feature.

.. versionadded:: 5.0.5

``logical_select`` is a sharding version of
:doc:`select`. ``logical_select`` searches records from multiple
tables and outputs them.

You need to :doc:`plugin_register` ``sharding`` plugin because
``logical_select`` is included in ``sharding`` plugin.

Syntax
------

This command takes many parameters.

The required parameters are ``logical_table`` and ``shard_key``. Other
parameters are optional::

  logical_select logical_table
                 shard_key
                 [min=null]
                 [min_border="include"]
                 [max=null]
                 [max_border="include"]
                 [filter=null]
                 [sortby=null]
                 [output_columns="_id, _key, *"]
                 [offset=0]
                 [limit=10]
                 [drilldown=null]
                 [drilldown_sortby=null]
                 [drilldown_output_columns="_key, _nsubrecs"]
                 [drilldown_offset=0]
                 [drilldown_limit=10]
                 [drilldown_calc_types=NONE]
                 [drilldown_calc_target=null]

``logical_select`` has the following named parameters for advanced
drilldown:

  * ``drilldown[${LABEL}].keys=null``
  * ``drilldown[${LABEL}].sortby=null``
  * ``drilldown[${LABEL}].output_columns="_key, _nsubrecs"``
  * ``drilldown[${LABEL}].offset=0``
  * ``drilldown[${LABEL}].limit=10``
  * ``drilldown[${LABEL}].calc_types=NONE``
  * ``drilldown[${LABEL}].calc_target=null``

You can use one or more alphabets, digits, ``_`` and ``.`` for
``${LABEL}``. For example, ``parent.sub1`` is a valid ``${LABEL}``.

Parameters that have the same ``${LABEL}`` are grouped.

For example, the following parameters specify one drilldown:

  * ``--drilldown[label].keys column``
  * ``--drilldown[label].sortby -_nsubrecs``

The following parameters specify two drilldowns:

  * ``--drilldown[label1].keys column1``
  * ``--drilldown[label1].sortby -_nsubrecs``
  * ``--drilldown[label2].keys column2``
  * ``--drilldown[label2].sortby _key``

Differences from ``select``
---------------------------

Most of ``logical_select`` features can be used like corresponding
:doc:`select` features. For example, parameter name is same, output
format is same and so on.

But there are some differences from :doc:`select`:

  * ``logical_table`` and ``shard_key`` parameters are required
    instead of ``table`` parameter.
  * ``sortby`` isn't supported when multiple shards are used. (Only
    one shard is used, they are supported.)
  * ``_value.${KEY_NAME}`` in ``drilldown[${LABEL}].sortby`` doesn't
    work with multiple shards. It works with one shard. ``_key`` in
    ``drilldown[${LABEL}].sortby`` work with multiple shards.
  * ``match_columns`` and ``query`` aren't supported yet.
  * ``cache`` isn't supported yet.
  * ``match_escalation_threshold`` isn't supported yet.
  * ``query_flags`` isn't supported yet.
  * ``query_expander`` isn't supported yet.
  * ``adjuster`` isn't supported yet.

Usage
-----

Let's learn about ``logical_select`` usage with examples. This section
shows many popular usages.

You need to register ``sharding`` plugin because ``logical_select`` is
included in ``sharding`` plugin.

.. groonga-command
.. include:: ../../example/reference/commands/logical_select/usage_plugin_register.log
.. plugin_register sharding


Here are a schema definition and sample data to show usage.

.. groonga-command
.. include:: ../../example/reference/commands/logical_select/usage_setup.log
.. table_create Entries_20150708 TABLE_HASH_KEY ShortText
.. column_create Entries_20150708 created_at COLUMN_SCALAR Time
.. column_create Entries_20150708 content COLUMN_SCALAR Text
.. column_create Entries_20150708 n_likes COLUMN_SCALAR UInt32
.. column_create Entries_20150708 tag COLUMN_SCALAR ShortText
..
.. table_create Entries_20150709 TABLE_HASH_KEY ShortText
.. column_create Entries_20150709 created_at COLUMN_SCALAR Time
.. column_create Entries_20150709 content COLUMN_SCALAR Text
.. column_create Entries_20150709 n_likes COLUMN_SCALAR UInt32
.. column_create Entries_20150709 tag COLUMN_SCALAR ShortText
..
.. table_create Terms TABLE_PAT_KEY ShortText \
..   --default_tokenizer TokenBigram \
..   --normalizer NormalizerAuto
.. column_create Terms entries_key_index_20150708 \
..   COLUMN_INDEX|WITH_POSITION Entries_20150708 _key
.. column_create Terms entries_content_index_20150708 \
..   COLUMN_INDEX|WITH_POSITION Entries_20150708 content
.. column_create Terms entries_key_index_20150709 \
..   COLUMN_INDEX|WITH_POSITION Entries_20150709 _key
.. column_create Terms entries_content_index_20150709 \
..   COLUMN_INDEX|WITH_POSITION Entries_20150709 content
..
.. load --table Entries_20150708
.. [
.. {"_key":       "The first post!",
..  "created_at": "2015/07/08 00:00:00",
..  "content":    "Welcome! This is my first post!",
..  "n_likes":    5,
..  "tag":        "Hello"},
.. {"_key":       "Groonga",
..  "created_at": "2015/07/08 01:00:00",
..  "content":    "I started to use Groonga. It's very fast!",
..  "n_likes":    10,
..  "tag":        "Groonga"},
.. {"_key":       "Mroonga",
..  "created_at": "2015/07/08 02:00:00",
..  "content":    "I also started to use Mroonga. It's also very fast! Really fast!",
..  "n_likes":    15,
..  "tag":        "Groonga"}
.. ]
..
.. load --table Entries_20150709
.. [
.. {"_key":       "Good-bye Senna",
..  "created_at": "2015/07/09 00:00:00",
..  "content":    "I migrated all Senna system!",
..  "n_likes":    3,
..  "tag":        "Senna"},
.. {"_key":       "Good-bye Tritonn",
..  "created_at": "2015/07/09 01:00:00",
..  "content":    "I also migrated all Tritonn system!",
..  "n_likes":    3,
..  "tag":        "Senna"}
.. ]

There are two tables, ``Entries_20150708`` and ``Entries_20150709``,
for blog entries.

.. note::

   You need to use ``${LOGICAL_TABLE_NAME}_${YYYYMMDD}`` naming rule
   for table names. In this example, ``LOGICAL_TABLE_NAME`` is
   ``Entries`` and ``YYYYMMDD`` is ``20150708`` or ``20150709``.

An entry has title, created time, content, the number of likes for the
entry and tag. Title is key of ``Entries_YYYYMMDD``. Created time is
value of ``Entries_YYYYMMDD.created_at`` column. Content is value of
``Entries_YYYYMMDD.content`` column. The number of likes is value of
``Entries_YYYYMMDD.n_likes`` column. Tag is value of
``Entries_YYYYMMDD.tag`` column.

``Entries_YYYYMMDD._key`` column and ``Entries_YYYYMMDD.content``
column are indexed using ``TokenBigram`` tokenizer. So both
``Entries_YYYYMMDD._key`` and ``Entries_YYYYMMDD.content`` are
fulltext search ready.

OK. The schema and data for examples are ready.

Simple usage
^^^^^^^^^^^^

TODO

Parameters
----------

This section describes parameters of ``logical_select``.

Required parameter
^^^^^^^^^^^^^^^^^^

There are required parameters, ``logical_table`` and ``shard_key``.

.. _logical-select-logical-table:

``logical_table``
"""""""""""""""""

Specifies logical table name. It means table name without
``_YYYYMMDD`` postfix. If you use actual table such as
``Entries_20150708``, ``Entries_20150709`` and so on, logical table
name is ``Entries``.

You can show 10 records by specifying ``logical_table`` and
``shard_key`` parameters. They are required parameters.

.. groonga-command
.. include:: ../../example/reference/commands/logical_select/logical_table_existent.log
.. logical_select --logical_table Entries --shard_key created_at

If nonexistent table is specified, an error is returned.

.. groonga-command
.. include:: ../../example/reference/commands/logical_select/logical_table_nonexistent.log
.. logical_select --logical_table Nonexistent --shard_key created_at

.. _logical-select-shard-key:

``shard_key``
"""""""""""""

Specifies column name which is treated as shared key. Shard key is a
column that stores data that is used for distributing records to
suitable shards.

Shard key must be ``Time`` type for now.

See :ref:`logical-select-logical-table` how to specify ``shard_key``.

Optional parameters
^^^^^^^^^^^^^^^^^^^

There are optional parameters.

.. _logical-select-min:

``min``
"""""""

Specifies the minimum value of ``shard_key`` column. If shard doesn't
have any matched records, the shard isn't searched.

For example, ``min`` is ``"2015/07/09 00:00:00"``, ``Entry_20150708``
isn't searched. Because ``Entry_20150708`` has only records for
``"2015/07/08"``.

The following example only uses ``Entry_20150709``
table. ``Entry_20150708`` isn't used.

.. groonga-command
.. include:: ../../example/reference/commands/logical_select/min.log
.. logical_select \
..   --logical_table Entries \
..   --shard_key created_at \
..   --min "2015/07/09 00:00:00"

.. _logical-select-min-border:

``min_border``
""""""""""""""

Specifies whether the minimum value is include or not. Here is
available values.

.. list-table::
   :header-rows: 1

   * - Value
     - Description
   * - ``include``
     - Includes ``min`` value. This is the default.
   * - ``exclude``
     - Doesn't include ``min`` value.

Here is an example for ``exclude``. The result doesn't include the
``"Good-bye Senna"`` record because its ``created_at`` value is
``"2015/07/09 00:00:00"``.

.. groonga-command
.. include:: ../../example/reference/commands/logical_select/min_border.log
.. logical_select \
..   --logical_table Entries \
..   --shard_key created_at \
..   --min "2015/07/09 00:00:00" \
..   --min_border "exclude"

.. _logical-select-max:

``max``
"""""""

Specifies the maximum value of ``shard_key`` column. If shard doesn't
have any matched records, the shard isn't searched.

For example, ``max`` is ``"2015/07/08 23:59:59"``, ``Entry_20150709``
isn't searched. Because ``Entry_20150709`` has only records for
``""2015/07/09"``.

The following example only uses ``Entry_20150708``
table. ``Entry_20150709`` isn't used.

.. groonga-command
.. include:: ../../example/reference/commands/logical_select/max.log
.. logical_select \
..   --logical_table Entries \
..   --shard_key created_at \
..   --max "2015/07/08 23:59:59"

.. _logical-select-max-border:

``max_border``
""""""""""""""

Specifies whether the maximum value is include or not. Here is
available values.

.. list-table::
   :header-rows: 1

   * - Value
     - Description
   * - ``include``
     - Includes ``max`` value. This is the default.
   * - ``exclude``
     - Doesn't include ``max`` value.

Here is an example for ``exclude``. The result doesn't include the
``"Good-bye Senna"`` record because its ``created_at`` value is
``"2015/07/09 00:00:00"``.

.. groonga-command
.. include:: ../../example/reference/commands/logical_select/max_border.log
.. logical_select \
..   --logical_table Entries \
..   --shard_key created_at \
..   --max "2015/07/09 00:00:00" \
..   --max_border "exclude"

.. _logical-select-search-related-parameters:

Search related parameters
^^^^^^^^^^^^^^^^^^^^^^^^^

``logical_select`` provides :doc:`select` compatible search related
parameters.

``match_columns`` and ``query`` aren't supported yet. ``filter`` is
only supported for now.

.. _logical-select-match-columns:

``match_columns``
"""""""""""""""""

Not implemented yet.

.. _logical-select-query:

``query``
"""""""""

Not implemented yet.

.. _logical-select-filter:

``filter``
""""""""""

Corresponds to :ref:`select-filter` in :doc:`select`. See
:ref:`select-filter` for details.

Here is an example:

.. groonga-command
.. include:: ../../example/reference/commands/logical_select/filter.log
.. logical_select \
..   --logical_table Entries \
..   --shard_key created_at \
..   --filter "n_likes <= 5"

.. _logical-select-advanced-search-parameters:

Advanced search parameters
^^^^^^^^^^^^^^^^^^^^^^^^^^

``logical_select`` doesn't implement advanced search parameters yet.

.. _logical-select-match-escalation-threshold:

``match_escalation_threshold``
""""""""""""""""""""""""""""""

Not implemented yet.

.. _logical-select-query-flags:

``query_flags``
"""""""""""""""

Not implemented yet.

.. _logical-select-query-expander:

``query_expander``
""""""""""""""""""

Not implemented yet.

Output related parameters
^^^^^^^^^^^^^^^^^^^^^^^^^

.. _logical-select-output-columns:

``output_columns``
""""""""""""""""""

Corresponds to :ref:`select-output-columns` in :doc:`select`. See
:ref:`select-output-columns` for details.

Here is an example:

.. groonga-command
.. include:: ../../example/reference/commands/logical_select/output_columns.log
.. logical_select \
..   --logical_table Entries \
..   --shard_key created_at \
..   --output_columns '_key, *'

.. _logical-select-sortby:

``sortby``
""""""""""

Corresponds to :ref:`select-sortby` in :doc:`select`. See
:ref:`select-sortby` for details.

``sortby`` has a limitation. It works only when the number of search
target shards is one. If the number of search target shards is larger
than one, ``sortby`` doesn't work.

Here is an example that uses only one shard:

.. groonga-command
.. include:: ../../example/reference/commands/logical_select/sortby.log
.. logical_select \
..   --logical_table Entries \
..   --shard_key created_at \
..   --min "2015/07/08 00:00:00" \
..   --min_border "include" \
..   --max "2015/07/09 00:00:00" \
..   --max_border "exclude" \
..   --sortby _key

.. _logical-select-offset:

``offset``
""""""""""

Corresponds to :ref:`select-offset` in :doc:`select`. See
:ref:`select-offset` for details.

Here is an example:

.. groonga-command
.. include:: ../../example/reference/commands/logical_select/offset.log
.. logical_select \
..   --logical_table Entries \
..   --shard_key created_at \
..   --offset 2

.. _logical-select-limit:

``limit``
"""""""""

Corresponds to :ref:`select-limit` in :doc:`select`. See
:ref:`select-limit` for details.

Here is an example:

.. groonga-command
.. include:: ../../example/reference/commands/logical_select/limit.log
.. logical_select \
..   --logical_table Entries \
..   --shard_key created_at \
..   --limit 2

.. _logical-select-scorer:

``scorer``
""""""""""

Not implemented yet.

.. _logical-select-drilldown-related-parameters:

Drilldown related parameters
^^^^^^^^^^^^^^^^^^^^^^^^^^^^

All drilldown related parameters in :doc:`select` are supported. See
:ref:`select-drilldown-related-parameters` for details.

.. _logical-select-drilldown:

``drilldown``
"""""""""""""

Corresponds to :ref:`select-drilldown` in :doc:`select`. See
:ref:`select-drilldown` for details.

Here is an example:

.. groonga-command
.. include:: ../../example/reference/commands/logical_select/drilldown.log
.. logical_select \
..   --logical_table Entries \
..   --shard_key created_at \
..   --output_columns _key,tag \
..   --drilldown tag

.. _logical-select-drilldown-sortby:

``drilldown_sortby``
""""""""""""""""""""

Corresponds to :ref:`select-drilldown-sortby` in :doc:`select`. See
:ref:`select-drilldown-sortby` for details.

Here is an example:

.. groonga-command
.. include:: ../../example/reference/commands/logical_select/drilldown_sortby.log
.. logical_select \
..   --logical_table Entries \
..   --shard_key created_at \
..   --limit 0 \
..   --output_columns _id \
..   --drilldown tag \
..   --drilldown_sortby -_nsubrecs,_key

.. _logical-select-drilldown-output-columns:

``drilldown_output_columns``
""""""""""""""""""""""""""""

Corresponds to :ref:`select-drilldown-output-columns` in
:doc:`select`. See :ref:`select-drilldown-output-columns` for details.

Here is an example:

.. groonga-command
.. include:: ../../example/reference/commands/logical_select/drilldown_output_columns.log
.. logical_select \
..   --logical_table Entries \
..   --shard_key created_at \
..   --limit 0 \
..   --output_columns _id \
..   --drilldown tag \
..   --drilldown_output_columns _key

.. _logical-select-drilldown-offset:

``drilldown_offset``
""""""""""""""""""""

Corresponds to :ref:`select-drilldown-offset` in :doc:`select`. See
:ref:`select-drilldown-offset` for details.

Here is an example:

.. groonga-command
.. include:: ../../example/reference/commands/logical_select/drilldown_offset.log
.. logical_select \
..   --logical_table Entries \
..   --shard_key created_at \
..   --limit 0 \
..   --output_columns _id \
..   --drilldown tag \
..   --drilldown_offset 1

.. _logical-select-drilldown-limit:

``drilldown_limit``
"""""""""""""""""""

Corresponds to :ref:`select-drilldown-limit` in :doc:`select`. See
:ref:`select-drilldown-limit` for details.

Here is an example:

.. groonga-command
.. include:: ../../example/reference/commands/logical_select/drilldown_limit.log
.. logical_select \
..   --logical_table Entries \
..   --shard_key created_at \
..   --limit 0 \
..   --output_columns _id \
..   --drilldown tag \
..   --drilldown_limit 2

.. _logical-select-drilldown-calc-types:

``drilldown_calc_types``
""""""""""""""""""""""""

Corresponds to :ref:`select-drilldown-calc-types` in
:doc:`select`. See :ref:`select-drilldown-calc-types` for details.

Here is an example:

.. groonga-command
.. include:: ../../example/reference/commands/logical_select/drilldown_calc_types.log
.. logical_select \
..   --logical_table Entries \
..   --shard_key created_at \
..   --limit -1 \
..   --output_columns tag,n_likes \
..   --drilldown tag \
..   --drilldown_calc_types MAX,MIN,SUM,AVG \
..   --drilldown_calc_target n_likes \
..   --drilldown_output_columns _key,_nsubrecs,_max,_min,_sum,_avg

.. _logical-select-drilldown-calc-target:

``drilldown_calc_target``
"""""""""""""""""""""""""

Corresponds to :ref:`select-drilldown-calc-target` in
:doc:`select`. See :ref:`select-drilldown-calc-target` for details.

See also :ref:`logical-select-drilldown-calc-types` for an example.

.. _logical-select-advanced-drilldown-related-parameters:

Advanced drilldown related parameters
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

All advanced drilldown related parameters in :doc:`select` are
supported. See :ref:`select-advanced-drilldown-related-parameters` for
details.

There are some limitations:

  * ``_value.${KEY_NAME}`` in ``drilldown[${LABEL}].sortby`` doesn't
    work with multiple shards. It works with one shard. ``_key`` in
    ``drilldown[${LABEL}].sortby`` work with multiple shards.

.. _logical-select-drilldown-label-keys:

``drilldown[${LABEL}].keys``
""""""""""""""""""""""""""""

Corresponds to :ref:`select-drilldown-label-keys` in
:doc:`select`. See :ref:`select-drilldown-label-keys` for details.

Here is an example:

.. groonga-command
.. include:: ../../example/reference/commands/logical_select/drilldown_label_keys.log
.. logical_select \
..   --logical_table Entries \
..   --shard_key created_at \
..   --limit 0 \
..   --output_columns _id \
..   --drilldown[tag.n_likes].keys tag,n_likes \
..   --drilldown[tag.n_likes].output_columns _value.tag,_value.n_likes,_nsubrecs

.. _logical-select-drilldown-label-output-columns:

``drilldown[${LABEL}].output_columns``
""""""""""""""""""""""""""""""""""""""

Corresponds to :ref:`select-drilldown-label-output-columns` in
:doc:`select`. See :ref:`select-drilldown-label-output-columns` for
details.

Here is an example:

.. groonga-command
.. include:: ../../example/reference/commands/logical_select/drilldown_label_output_columns.log
.. logical_select \
..   --logical_table Entries \
..   --shard_key created_at \
..   --limit 0 \
..   --output_columns _id \
..   --drilldown[tag].keys tag \
..   --drilldown[tag].output_columns _key,_nsubrecs

.. _logical-select-drilldown-label-sortby:

``drilldown[${LABEL}].sortby``
""""""""""""""""""""""""""""""

Corresponds to :ref:`logical-select-drilldown-sortby` in not labeled
drilldown.

``drilldown[${LABEL}].sortby`` has a limitation.

``_value.${KEY_NAME}`` in ``drilldown[${LABEL}].sortby`` doesn't work
with multiple shards. It works with one shard. ``_key`` in
``drilldown[${LABEL}].sortby`` work with multiple shards.

Here is an example that uses ``_value.${KEY_NAME}`` with only one
shard:

.. groonga-command
.. include:: ../../example/reference/commands/logical_select/drilldown_label_sortby.log
.. logical_select \
..   --logical_table Entries \
..   --shard_key created_at \
..   --min "2015/07/08 00:00:00" \
..   --min_border "include" \
..   --max "2015/07/09 00:00:00" \
..   --max_border "exclude" \
..   --limit 0 \
..   --output_columns _id \
..   --drilldown[tag.n_likes].keys tag,n_likes \
..   --drilldown[tag.n_likes].output_columns _nsubrecs,_value.n_likes,_value.tag \
..   --drilldown[tag.n_likes].sortby -_nsubrecs,_value.n_likes,_value.tag

.. _logical-select-drilldown-label-offset:

``drilldown[${LABEL}].offset``
""""""""""""""""""""""""""""""

Corresponds to :ref:`logical-select-drilldown-offset` in not labeled
drilldown.

Here is an example:

.. groonga-command
.. include:: ../../example/reference/commands/logical_select/drilldown_label_offset.log
.. logical_select \
..   --logical_table Entries \
..   --shard_key created_at \
..   --limit 0 \
..   --output_columns _id \
..   --drilldown[tag.n_likes].keys tag \
..   --drilldown[tag.n_likes].offset 1

.. _logical-select-drilldown-label-limit:

``drilldown[${LABEL}].limit``
"""""""""""""""""""""""""""""

Corresponds to :ref:`logical-select-drilldown-limit` in not labeled
drilldown.

Here is an example:

.. groonga-command
.. include:: ../../example/reference/commands/logical_select/drilldown_label_limit.log
.. logical_select \
..   --logical_table Entries \
..   --shard_key created_at \
..   --limit 0 \
..   --output_columns _id \
..   --drilldown[tag.n_likes].keys tag \
..   --drilldown[tag.n_likes].limit 2

.. _logical-select-drilldown-label-calc-types:

``drilldown[${LABEL}].calc_types``
""""""""""""""""""""""""""""""""""

Corresponds to :ref:`logical-select-drilldown-calc-types` in not
labeled drilldown.

Here is an example:

.. groonga-command
.. include:: ../../example/reference/commands/logical_select/drilldown_label_calc_types.log
.. logical_select \
..   --logical_table Entries \
..   --shard_key created_at \
..   --limit 0 \
..   --output_columns _id \
..   --drilldown[tag].keys tag \
..   --drilldown[tag].calc_types MAX,MIN,SUM,AVG \
..   --drilldown[tag].calc_target n_likes \
..   --drilldown[tag].output_columns _key,_nsubrecs,_max,_min,_sum,_avg

.. _logical-select-drilldown-label-calc-target:

``drilldown[${LABEL}].calc_target``
"""""""""""""""""""""""""""""""""""

Corresponds to :ref:`logical-select-drilldown-calc-target` in not
labeled drilldown.

See also :ref:`logical-select-drilldown-label-calc-types`
for an example.

Return value
------------

The return value format of ``logical_select`` is compatible with
:doc:`select`. See :ref:`select-return-value` for details.
