ColumnQuery

概要

基本概念

カラム同士の比較の絞り込み条件を設定します。(where FOO_ID = BAR_ID)

Query による絞り込み条件は、基本的にアプリから条件値を設定するものですが、ColumnQuery は条件値は設定せずに、あるカラムと別のあるカラムの比較をします。カラム同士が業務的なつながりを持っているような場合に有効です。

会話上では、からむくえり と表現します。

柔軟な利用方法

カラム、というのに、その場のサブクエリで作り出した疑似のカラム、導出カラムも含みます。これにより、ColumnQuery は非常に柔軟な利用方法が可能な機能となっています。

子テーブルの導出カラム

実装方法

実装の流れ ※1.1.x (Java8版)

ConditionBean の columnQuery() を呼び出し、左辺のカラムを指定するコールバックを引数に設定し、続けて greaterEqual() などの比較条件メソッドを呼び出して、今度は右辺のカラムを指定するコールバックを引数に設定します。 必要であれば、さらに続けて右辺カラムの演算調整が必要であれば演算メソッドを呼び出します。

DBFlute on Java8
e.g. ColumnQueryの実装手順 (Eclipseでコード補完) {BIRTHDATE < FORMALIZED_DATETIME} @Java
cb.coQ // .coQ と打って enter
--

// メソッドが補完されて、引数の "colCBLambda" が選択状態に
cb.columnQuery(colCBLambda)
--

// colCBLambdaの部分で、_li (補完テンプレートが有効なら)
cb.columnQuery(_li)
--

// Lambda引数名はcolCBにして...
// Expression Style (一行スタイル) で SpecifyColumn を
cb.columnQuery(colCB -> colCB.specify().col...)
--

// 左辺のカラム BIRTHDATE を指定
cb.columnQuery(colCB -> colCB.specify().columnBirthdate())
--

// 続けて、比較条件 .leT (lessThan) と打って enter
cb.columnQuery(colCB -> colCB.specify().columnBirthdate())
        .leT
--

// 同じ要領で今度は右辺のカラム FORMALIZED_DATETIME を指定
cb.columnQuery(colCB -> colCB.specify().columnBirthdate())
        .lessThan(colCB -> colCB.specify().columnFormalizedDatetime())

SQL上では、指定された左辺と右辺のカラムを比較する条件が展開されます。

this - 利用できる比較条件

Lambdaの形式は、Expression Style (一行スタイル) でも Block Style (複数行スタイル) でもどちらでも良いですが、Eclipse だと "Lambdaのネストの中での Block Style とメソッドチェーン" が相性が悪いため、ドキュメントとしては Expression Style をメインに書いています。

DBFlute on Java8 - Eclipseのご乱心に注意

実装の流れ ※1.0.x (Java6版)

e.g. 生まれてから正式会員になった会員 @DisplaySql
...
  from MEMBER dfloc
 where dfloc.BIRTHDATE < dfloc.FORMALIZED_DATETIME

e.g. ColumnQueryの実装手順 (Eclipseでコード補完) {BIRTHDATE < FORMALIZED_DATETIME} @Java
MemberCB cb = new MemberCB();
cb.coQ // .coQ と打って enter
--

// メソッドが補完されて、引数の "leftSpecifyQuery" が選択状態に
cb.columnQuery(leftSpecifyQuery)
--

// "new " (new + 空白一つ) と打って ctrl + space そして enter
cb.columnQuery(new )
--

// 実装メソッドの空実装が自動生成される (Eclipse-3.5 以上)
cb.columnQuery(new SpecifyQuery<MemberCB>() {
    
    public void specify(MemberCB cb) {
        // TODO Auto-generated method stub
        
    }
})
--

// ctrl (or command) + D で不要な空行やTODOコメントを消して
// SpecifyColumn を使って左辺のカラムを指定
cb.columnQuery(new SpecifyQuery<MemberCB>() {
    public void specify(MemberCB cb) {
        cb.specify().columnBirthdate();
    }
}).leT // 続けて、比較条件 .leT (lessThan) と打って enter
--

// 同じ要領で今度は右辺のカラムを指定
cb.columnQuery(new SpecifyQuery<MemberCB>() {
    public void specify(MemberCB cb) {
        cb.specify().columnBirthdate();
    }
}).lessThan(new SpecifyQuery<MemberCB>() {
    public void specify(MemberCB cb) {
        cb.specify().columnFormalizedDatetime();
    }
}); // 必要に応じて、右辺の演算メソッドを呼び出す e.g. ...}).plus(3)

関連テーブルのカラム

関連テーブル(many-to-one, one-to-one)のカラムも利用できます。 

e.g. 会員セキュリティ情報の更新日時と退会日時が同じ会員 @Java
...
cb.columnQuery(colCB -> colCB.specify().specifyMemberSecurity().columnUpdateDatetime())
        .equal(colCB -> colCB.specify().specifyMemberWithdrawal().columnWithdrawalDatetime());

但し、バインド変数付きの biz-one-to-one のテーブルに関しては、あらかじめ SetupSelect(BizOneToOne) もしくは Query(BizOneToOne) で固定条件(fixedCondition)を成立させている必要があります。

SetupSelect(BizOneToOne)
Query(BizOneToOne)
e.g. (有効な)会員住所情報の更新日時と退会日時が同じ会員 @Java
...
cb.query().queryMemberAddressAsValid(currentDate());
cb.columnQuery(colCB -> colCB.specify().specifyMemberAddressAsValid()
                                       .columnUpdateDatetime())
        .equal(colCB -> colCB.specify().specifyMemberWithdrawal()
                                       .columnWithdrawalDatetime());

子テーブルの導出カラム

(Specify)DerivedReferrer の利用

子テーブルの導出カラムも利用できます。SpecifyQuery の中で (Specify)DerivedReferrer をエリアス名を null 固定で指定します。(左辺、右辺両方で利用できます)

(Specify)DerivedReferrer
e.g. 生まれる前、もしくは生まれた瞬間に(支払済み)購入をしたことのある会員 @Java
...
cb.columnQuery(colCB -> colCB.specify().columnBirthdate())
    .lessEqual(colCB -> {
        colCB.specify().derivedPurchase().min(purchaseCB -> {
            purchaseCB.specify().columnPurchaseDatetime();
            purchaseCB.query().setPaymentCompleteFlg_Equal_True();
        }, null); // エリアス名は利用しないので null 固定
    });

e.g. 生まれる前、もしくは、生まれた瞬間に(支払済み)購入をしたことのある会員 @DisplaySql
...
  from MEMBER dfloc
 where dfloc.BIRTHDATE <= (select min(sub1loc.PURCHASE_DATETIME)
                             from PURCHASE sub1loc 
                            where sub1loc.MEMBER_ID = dfloc.MEMBER_ID
                              and sub1loc.PAYMENT_COMPLETE_FLG = 1
       )

カテゴリごとの最大値や平均値との比較

導出カラムの利用を応用すると、カテゴリごとの最大値や平均値との比較条件が実現できます。Specify(Relation) で辿ったカテゴリテーブルから、自分方向のリレーションに戻って DerivedReferrer をします。 

e.g. サービスランクごとの平均ポイント数よりも多いポイント数を持っている会員 @Java
...
cb.columnQuery(colCB -> colCB.specify().specifyMemberServiceAsOne().columnPointCount())
  .greaterThan(colCB -> {
      colCB.specify().specifyMemberServiceAsOne()
                     .specifyServiceRank()
                     .derivedMemberService().avg(serviceCB -> {
          serviceCB.specify().columnPointCount();
      }, null, op -> op.coalesce(0));
  });

e.g. サービスランクごとの平均ポイント数よりも多いポイント数を持っている会員 @DisplaySql
...
  from MEMBER dfloc
    left outer join MEMBER_SERVICE dfrel_3 on dfloc.MEMBER_ID = dfrel_3.MEMBER_ID
    left outer join SERVICE_RANK dfrel_3_1 on dfrel_3.SERVICE_RANK_CODE = dfrel_3_1.SERVICE_RANK_CODE 
 where dfrel_3.POINT_COUNT > (select coalesce(avg(sub1loc.POINT_COUNT), 0)
                                from MEMBER_SERVICE sub1loc 
                               where sub1loc.SERVICE_RANK_CODE = dfrel_3_1.SERVICE_RANK_CODE
       )

基点テーブルの導出カラム

基点テーブルの導出カラム(ScalarConditionのような感じで)の指定はサポートされていません。@review

例えば、一番若い会員を検索、というような同じカラム同士で、とあるカラムとそのカラムの導出値を比較するようなパターンであれば、ScalarConditionを利用してください。

ScalarCondition

右辺カラムの演算調整 (数値型カラム)

右辺カラムの演算調整をすることができます。単純なカラム同士の比較ではなく、あるカラムと別のあるカラムに 1 を足したものを比較 という条件が設定できます。ただし、数値型のカラムに限ります。

右辺カラムの指定の後に続けて、演算メソッドを呼び出し、演算値を引数に設定します。

this - 利用できる演算調整
e.g. 支払済み購入最小価格が、会員IDに 500 を掛けて 1 足したものよりも小さい会員 @Java
cb.columnQuery(colCB -> {
    colCB.specify().derivedPurchaseList().min(purchaseCB -> {
        purchaseCB.specify().columnPurchasePrice();
        purchaseCB.query().setPaymentCompleteFlg_Equal_True();
    }, null, op -> op.coalesce(0)); // 購入のない会員も含むため
}).lessThan(colCB -> colCB.specify().columnMemberId())
  .multiply(500).plus(1);

e.g. 支払済み購入最小価格が、会員IDに 500 を掛けて 1 足したものよりも小さい会員 @DisplaySql
...
  from MEMBER dfloc
 where (select coalesce(min(sub1loc.PURCHASE_PRICE), 0)
          from PURCHASE sub1loc 
         where sub1loc.MEMBER_ID = dfloc.MEMBER_ID
           and sub1loc.PAYMENT_COMPLETE_FLG = 1
       ) < (dfloc.MEMBER_ID * 500) + 1

右辺カラムをSQL関数でフィルタ (数値や日付型など)

演算だけでなく、SQL関数を利用して変換(フィルタ)することができます(@since 0.9.8.6)。

右辺カラムの指定の後に続けて、convert() メソッドを呼び出し、ColumnConversionOption で変換処理を指定します。 フィルタ処理の仕様は (Specify)DerivedReferrer の場合と同様で、時分秒ミリ秒を切り取ったり(truncXxx())、日付を進めたり(addXxx())できます。

(Specify)DerivedReferrer - 導出値をSQL関数でフィルタ
e.g. 右辺カラムの日時に対して、時分秒ミリ秒を切り取って 1 日進める @Java
cb.columnQuery(colCB -> colCB.specify().columnBirthdate())
  .greaterThan(colCB -> colCB.specify().columnFormalizedDatetime())
  .convert(op -> op.truncTime().addDay(1));

演算とフィルタは組み合わせて利用することができます。(指定された順序に処理される)

左辺カラムの演算やフィルタも (数値や日付型など)

右辺だけではなく左辺のカラムに対しても演算やSQL関数フィルタが利用できます(@since 0.9.8.6)。

右辺カラムの指定の後に続けて、left() メソッドを呼び出した後に続けて演算処理やフィルタ処理を指定すると、それら指定は左辺カラムに対して処理されます。 その後、right() を呼び出して続けて右辺のための処理を指定できます。(つまり、単にデフォルトが右辺カラムになっている) 

e.g. 左辺カラムは 1 を足して、右辺カラムは 2 を引く @Java
...
cb.columnQuery(colCB -> colCB.specify().specifyMemberServiceAsOne().columnPointCount())
  .greaterThan(colCB -> {
      colCB.specify().specifyMemberServiceAsOne()
                     .specifyServiceRank()
                     .derivedMemberService()
                     .avg(serviceCB -> {
                         serviceCB.specify().columnPointCount();
                     }, null, op -> op.coalesce(0));
  }).left().plus(1).right().minus(2);

メソッド仕様

基本仕様

引数の指定
引数の SpecifyQuery、演算調整の演算値、共に必須です。
サブクエリのConditionBean
サブクエリの ConditionBean は、導出カラムの指定だけに利用するものです。 SetupSelect や OrderBy, Query など必要のない機能は呼び出してはいけません。
OnClause や InlineView では不可
OnClause や InlineView の中の条件に利用することはできません。
OnClause
InlineView

利用できる比較条件

利用できる比較条件は、以下の通りです。

equal()
等値
notEqual()
非等値
greaterThan()
大なり
lessThan()
小なり
greaterEqual()
大なりイコール
lessEqual()
小なりイコール

利用できる演算調整

利用できる演算調整は、以下の通りです。

plus()
足し算 (数値のみ)
minus()
引き算 (数値のみ)
multiply()
掛け算 (数値のみ)
divide()
割り算 (数値のみ)

拡張バッファのある機能

ColumnQuery は、自由度が高いインターフェースのため、将来的な拡張バッファのある機能です。 今後のバージョンで機能拡張の可能性が高い機能です。 特にサブクエリ系との連携には、まだ見ぬ宝石が埋もれているかもしれません。