カテゴリー内の他の記事

第4回 クエリ指定して更新・並び順変更をしてみよう

フォローする

(著者:Cstap 瀧ヶ平 充

はじめに

前回までで、cli-kintoneを使ったレコード操作は一通りできるようになったのではないでしょうか。

今回は、クエリを使ったレコードの絞り込みや並び替えの方法をご紹介します。

詳細はこちらで確認していただくとして、ここではクエリの基本的な書き方をおぼえていきましょう。

クエリの書き方

基本的な式の書き方

フィールドの値または関数で扱うレコードを絞り込む場合、「フィールドコード(またはシステム識別子)」「演算子」「値(または関数)」で構成される式を使います。

▼ 式の基本書式

たとえば「数値_0」フィールドの値が10以上のレコードを抽出する場合、式は以下のようになります。

フィールドコード「数値_0」、演算子「>=」、条件の境界になる値「10」、これらを、フィールドコード、演算子、値の順に組み合わたものを1セットとして記述します。

 in, not in を使用する場合

式の書き方は多くの場合上の書式で対応できますが、一部の演算子を使用する場合は若干異なります。

演算子に in, not in を使用する場合、以下の書式になります。

▼ in, not in 演算子を使用する場合の書式

値の記述

クエリ内部では、関数を除いたすべての値をダブルクォーテーション「"」で囲って表記します。

▼ 値の記述例

式のグループ化

単独の式で条件を指定できるケースもありますが、より詳細な条件を指定したい場合も多いと思います。

式は丸括弧「()」で括ることでグループ化できます。また and, or を用いて、「かつ」「または」を含む条件を表現できます。

これらの演算子で複数の式を結合すると、より詳細な条件を指定することができます。

▼ グループ化された式の例

この例の場合、下記2つのどちらかの条件にあてはまるレコードが抽出されます。

  • 「数値」フィールドの値が100以上で、かつ、「文字列一行」フィールドに文字列「テスト」を含む値を持つレコード
  • 「更新日時」フィールドの値が、前月1日であるレコード

and, orの優先順位

式がグループ化されていない場合、論理積の演算子andが優先されます。

前項の例の場合、or よりも and が優先的に処理されるので、括弧を外した場合も同じ条件となります。

関連レコードに含まれるフィールドの指定

関連レコードに含まれるフィールドを指定する場合、下記いずれかの書式で式を書くことができます。

▼ 関連レコードを指定する場合の基本書式

▼ in, not in 演算子を使用する場合の書式

cli-kintoneのログインユーザー

cli-kintoneでは、APIトークン認証の場合はAdministrator、パスワード認証の場合はそのユーザーがログインユーザーとなります。

したがって LOGINUSER() または PRIMARY_ORGANIZATION() の関数を利用する場合、そのログインユーザーを基準に関数が処理されます。

演算子および関数と利用可能なフィールドの一覧

基本的なクエリの書き方がわかったところで、各演算子・関数に対して使えるフィールドの種類をひと通り確認してみましょう。

▼ 演算子と利用可能なフィールド

演算子 フィールドまたはシステム識別子
= レコード番号, $id, 作成日時, 更新日時, 文字列(1行), リンク, 数値, 日付, 時刻, 日時, ステータス
!= レコード番号, $id, 作成日時, 更新日時, 文字列(1行), リンク, 数値, 日付, 時刻, 日時, ステータス
> レコード番号, $id, 作成日時, 更新日時, 数値, 日付, 時刻, 日時
< レコード番号, $id, 作成日時, 更新日時, 数値, 日付, 時刻, 日時
>= レコード番号, $id, 作成日時, 更新日時, 数値, 日付, 時刻, 日時
<= レコード番号, $id, 作成日時, 更新日時, 数値, 日付, 時刻, 日時
in レコード番号, $id, 作成者, 更新者, 文字列(1行), リンク, 数値, チェックボックス, ラジオボタン, ドロップダウン, 複数選択, ユーザー選択, 組織選択, グループ選択
not in レコード番号, $id, 作成者, 更新者, 文字列(1行), リンク, 数値, チェックボックス, ラジオボタン, ドロップダウン, 複数選択, ユーザー選択, 組織選択, グループ選択
like 文字列(1行), リンク, 文字列(複数行), リッチエディター, 添付ファイル
not like 文字列(1行), リンク, 文字列(複数行), リッチエディター, 添付ファイル

テーブル化されたフィールドは =, != の代わりにin, not inを利用する必要があります。

▼ 関数と利用可能なフィールド

関数 フィールドまたはシステム識別子
LOGINUSER() 作成者, 更新者, ユーザー選択
PRIMARY_ORGANIZATION() 組織選択
NOW() 作成日時, 更新日時, 日時
TODAY() 作成日時, 更新日時, 日付, 日時
FROM_TODAY() 作成日時, 更新日時, 日付, 日時
THIS_WEEK() 作成日時, 更新日時, 日付, 日時
LAST_WEEK() 作成日時, 更新日時, 日付, 日時
THIS_MONTH() 作成日時, 更新日時, 日付, 日時
LAST_MONTH() 作成日時, 更新日時, 日付, 日時
THIS_YEAR() 作成日時, 更新日時, 日付, 日時

クエリで使えるオプション

並び順の指定

フィールドコードまたはシステム識別子を指定して、その値でレコードを並び替えることができます。

▼ 昇順で並び替える場合の書式

▼ 降順で並び替える場合の書式

エクスポート対象レコードの件数指定

エクスポートするレコード数を limit の後に指定した数に制限します。

▼ エクスポート対象レコード件数を指定する場合の書式

エクスポート対象レコードを一定数スキップさせる

エクスポート対象レコードから任意の件数分をスキップしてエクスポートすることができます。

このとき、スキップするレコードはorder by <フィールドコード または システム識別子> <asc または desc> によって指定した順で数えられます。

▼ エクスポート対象レコードを一定数スキップする場合の書式

cli-kintoneでクエリを利用しレコードを絞り込む

それではクエリを使ってcli-kintoneでレコードをエクスポートする具体例を見ていきましょう。

今回もこれまで使ってきた 交通費申請アプリで試してみましょう。

クエリ作成のコツ

cli-kintoneでクエリによる絞り込みが必要な場合、式を一つずつ個別に試してエクスポートしていき、そのあと and や or でつなげた条件で試すようにします。

このようにしてエクスポートが想定通りに動くことを確認してから、保存や削除を実行すると意図しないデータ操作を減らすことができます。

通常のフィールドで絞り込み・並び順を指定する

今回は、

  • 「ステータス」が「承認」

かつ

  • 「所属部署」が「営業部」または「経理部」

または

  • 「合計金額」が「10000円以上」

となるレコードを抽出し、並び順は「合計金額」の降順を指定します。

▼「ステータス」が「承認」の式

▼「所属部署」が「営業部」または「経理部」の式

▼「合計金額」が「10000円以上」の式

▼「合計金額」の降順指定

これらの式を条件通りに組み合わせると、下記のようなクエリができあがります。

▼ 式の組み合わせでできたクエリの例

次はこれをcli-kintone上で試してみましょう。

エクスポートするレコード件数を絞り込みたい場合、limit <数>を、スキップする件数を指定したい場合は offset <数> を、それぞれクエリに追加してみてください。

前回まででは触れていませんでしたが、クエリには必ず空白が含まれるので全体のクエリはダブルクォーテーション「"」で囲う必要があります。

そのため、クエリ内部のダブルクォーテーション「"」はバックスラッシュ「\」を利用してエスケープする必要があります。

PowerShellやBashなどのシェルでは「'」で括った文字列もコマンドの引数として渡せるので、その場合はバックスラッシュ「\」によるダブルクォーテーション「"」のエスケープは必要ありません。

テーブル内のフィールドで絞り込む

最後に、テーブル内のフィールドを使ってレコードを絞り込んでみましょう。

クエリ内でテーブル内のフィールドを指定する場合、 =, != の代わりにin, not in を使う必要があります。

また、クエリの指定条件に当てはまる要素を含んだテーブルを持つレコードすべてが選択されます。

これらを踏まえて、下記いずれかの条件にあてはまるレコードを絞り込んでみましょう。

絞り込み条件

  • 「金額」が「500円以上」
  • 「交通手段」が「電車(片道)」「電車(往復)」ではない
  • 「訪問先」が「サイボウズ株式会社」

以下、これらの条件にもとづいた式です。

▼ 「金額」が「500円以上」

▼ 「交通手段」が「電車(片道)」「電車(往復)」ではない

「訪問先」が「サイボウズ株式会社」は、基本の指定方法だと Place = "サイボウズ株式会社" となりますが、今回はテーブル内の指定なので以下のようになります。

▼ 「訪問先」が「サイボウズ株式会社」

これらの式を組み合わせてcli-kintone上で実行する場合、以下のようになります。

第4回は以上になります。

kintoneでのクエリについておわかりいただけたでしょうか? cli-kintone の機能もあと少しで完璧にできそうですね!

次回は添付ファイルの操作について紹介します。お楽しみに。

記事に関するフィードバック

直接的に記事と関連がないご質問はcybozu developer コミュニティをご活用ください。

Avatar
h.yamada

queryに関する要望と質問です。

チュートリアルで試して、query指定が大変便利とわかりました。
ただ複雑なqueryを指定したい場合、コマンド指定だと少し面倒なので、ファイル指定できるとより便利だと思います。

そこで要望ですが、queryでファイル指定をサポートする予定はないでしょうか?
 例 -Qオプション追加でquery記述ファイル名を指定。 -qと-Qは同時指定不可

以下はqueryに関する質問です。

Q1.  queryで "$id"と”レコード番号”の差異がわからないのですが、どのような意味の違いがあるのか教えてください。
例えば、次のようなコマンドを試したら結果的に同じ表示内容となりましたが、どのような意味の違いがあるのでしょうか?

>cli-kintone.exe -a x -d xxx -t xxx -D -q "$id < 10" 
>cli-kintone.exe -a x -d xxx -t xxx -D -q "レコード番号 < 10" 

以上、よろしくお願いします。

Avatar
cybozu Development team

h.yamada様

回答が遅くなり申し訳ございません。

まずいただいているご要望についてですが、queryでファイル指定をサポートする予定は、今のところございません。

ご期待に添えず申し訳ありませんが、今後の検討課題とさせていただきます。

ご質問に関しましては、確認の上回答いたします。

 

Avatar
cybozu Development team

h.yamada様

ご質問いただいているIDとレコード番号とidの違いですが、アプリコードの設定が無い場合には同じ扱いになります。
なお、アプリコードの指定がある場合は、レコード番号は「アプリコード+id」となります。

こちらに参考になるコメントがございますので、合わせてご参照ください。
https://developer.cybozu.io/hc/ja/community/posts/202066824

 

この度は、ご要望、ご質問をコメントいただきありがとうございました。今後ともcybozu developer networkをどうぞよろしくお願いいたします。

Avatar
Seiji Ogawa

例えば履歴アプリ的なものの日付フィールド(フィールドコードは「対応年月日」)から年と日は関係なく、月だけ抽出したい場合などの様に、条件としてYYYY-MM-DDのMMだけ使用することは出来ますでしょうか。

-q "対応年月日 like \"%-04-%\""

では、「 [GAIA_IQ03] 対応年月日フィールドのフィールドタイプには演算子likeを使用できません。」でした。

 

DATE_FORMATでYYYY-MM-DDをMMだけにしたものと特定の文字列(04など)とを比較しようとおもっても出来ませんでした。

-q "DATE_FORMAT(対応年月日, \"MM\", \"system\")=\"04\"" → 「 [CB_VA01] 入力内容が正しくありません。」

よろしくおねがいします。

Avatar
cybozu Development team

Seiji Ogawa様

お世話になっております。

cybozu developer network 事務局です。

 

現在、クエリでは「月だけを条件」といった設定はできません。

申し訳ございません。

 

日付、日時フィールドではこちらに記載されている演算子や関数のみ利用可能となっております

▼ フィールド、システム識別子ごとの資料可能な演算子と関数一覧

https://developer.cybozu.io/hc/ja/articles/202331474#step2

 

また、クエリで指定できる条件については、

アプリのレコード一覧設定等から設定できる絞り込みでの条件と同じになっております。

合わせてご確認ください。

ご期待に添えず、大変申し訳ございません。

よろしくお願いいたします。

Avatar
Seiji Ogawa

> cybozu developer network 事務局様

お返事どうもありがとうございます。なるほど承知いたしました。

別のアプローチを考えてみます。

それではどうぞ、よろしくお願いいたします。

 

ログインしてコメントを残してください。