Support > Repository > Search control > [Advanced] Customize search conditions with script
ja | en

The search condition provided by Wagby is internally managed as "criteria". I will explain how to control this with a script. R7.11

The method described on this page uses "script".For details of the script, see "Business logicPlease read.

"Criteria" is a class that manages search criteria used inside Wagby.It has expressability equivalent to SQL, it has merit that it does not depend on the database to be used, and it can detect errors by checking type at compile time.

Figure 1 Position of criteria

As shown in Fig. 1, the "search condition" entered by the user is imported into Wagby in the form of "condition model" from the Web form.The condition model is further converted to "criteria".This criterion is converted to SQL and the database is queried.

Criteria is provided by O/R Mapper middleware Hibernate bundled with Wagby.Conversion to SQL from the criteria, do Hibernate is.

Benefits of Customizing Criteria

With Wagby's standard, all search conditions of multiple items are AND linked.For example, searching the mainstaff item of customer model (customer) as "Hanako" or substaff item as "Hanako" can not be realized with the standard function.(In the standard function, mainstaff is "Hanako" and substaff is also "Hanako" AND combination.)

By customizing criteria, OR search of multiple items can be realized.

Requirement

Consider an example where search condition values ​​of personnel items specified in the search form are OR searches with primary and secondary staff.

Figure 2 Requirement image

Specifically, we make it possible to realize the search that "mainstaff item" of customer model (customer) is "Jasmine Hanako" or substaff item "Jasmine Hanako".

Script to prepare

Prepare "Screen> Script> Helper> Search".

Figure 3 Creating a script
<Model ID> CriteriaConverter_convert.js is generated in the WEB-INF/script/<model ID> folder.
function process() {
  var meta = new Packages.jp.jasminesoft.wagby.model.customer.CustomerMeta();

  // スクリプトでは criteria.like() 等を利用すると
  // Can't unambiguously select between fixed arity signatures
  // エラーが発生することがあるため、$r を使って検索条件を組み立てます。
  // criteria のメソッドは add() のみを使って下さい。

  var $r = criteria.restrictions();
  // "mainstaff" like '%ジャスミン花子%' OR "substaff" like '%ジャスミン花子%'
  criteria.add(
    $r.or(
      $r.like(meta.mainstaff, customer_c.mainstaff),
      $r.like(meta.substaff, customer_c.mainstaff)
    )
  );

  customer_c.mainstaff = null; // 元の検索条件は消去しておく。(*1)
}
  • Implicit object criteria can be used in script.First let's get the returned value of criteria.restrictions () with $ r.We construct this search condition using this $ r.
  • For the implicit object criteria only the add method is available.
  • When specifying search criteria using criteria, the target item name is not a character string, but a meta-class provided by Wagby is used.This avoids debugging difficult problems such as run-time errors due to misspellings.

Delete original search condition

If you do not process (* 1) in the above script, the next SQL will be executed.

WHERE
    mainstaff LIKE ‘%ジャスミン花子%’
AND (
       mainstaff LIKE ‘%ジャスミン花子%’
    OR substaff LIKE ‘%ジャスミン花子%’
)
AND...

The first mainstaff LIKE ... in the WHERE clause was given by the auto-generated code, but this requirement is unnecessary.Therefore, overwrite the original search condition with null so that this SQL is not generated.When converted from a condition model to criteria, it is interpreted internally as a code called criteria.like (meta.mainstaff, null), but since the second argument is ignored, the null criteria is ignored, so it behaves as expected.

We will explain how to set criteria by concrete code example.

  // criteria.add() を使うと AND で検索条件が追加されます。
  // WHERE "mainstaff" LIKE '%花子%' AND "substaff" LIKE '%花子%'
  criteria.add($r.like(meta.mainstaff, "花子"));
  criteria.add($r.like(meta.substaff, "花子"));

  // $r.and() でも同様に AND で検索条件が追加されます。
  // 次の記述方法でも同じ SQL が生成されます。
  // WHERE "mainstaff" LIKE '%花子%' AND "substaff" LIKE '%花子%'
  criteria.add(
      $r.and(
        $r.like(meta.mainstaff, "花子"),
        $r.like(meta.substaff, "花子")
      )
  );

  // $r.like(), $r.and() の戻り値を変数に格納することも可能です。
  // 次の記述方法でも同じ SQL が生成されます。
  // WHERE "mainstaff" LIKE '%花子%' AND "substaff" LIKE '%花子%'
  var likeCriterion01 = $r.like(meta.mainstaff, "花子");
  var likeCriterion02 = $r.like(meta.substaff, "花子");
  var andCriterion = $r.and(likeCriterion01, likeCriterion02);
  criteria.add(andCriterion);

  // $r.or() を使うことで OR 検索を行うことができます。
  // WHERE "mainstaff" LIKE '%花子%' OR "substaff" LIKE '%花子%'
  criteria.add(
      $r.or(
        $r.like(meta.mainstaff, "花子"),
        $r.like(meta.substaff, "花子")
      )
  );

  // 引数が null の場合はその条件は無視されます。
  // WHERE "substaff" LIKE '%花子%'
  criteria.add(
      $r.or(
        $r.like(meta.mainstaff, null),
        $r.like(meta.substaff, "花子")
      )
  );

  // 単一項目への OR 検索は引数を追加するだけで実現可能です。
  // WHERE "mainstaff" LIKE '%花子%' OR "mainstaff" LIKE '%太郎%' OR "mainstaff" LIKE '%次郎%'
  criteria.add($r.like(meta.mainstaff, "花子", "太郎", "次郎"));

  // 複雑な検索条件。
  // "住所が「沖縄」のお客様で、かつ、主担当が「花子」
  //  または、
  //  住所が「北海道」のお客様で、かつ、主担当が「次郎」"
  // となっている顧客データを抽出する。
  // WHERE (("address" LIKE '%沖縄%' AND "mainstaff" LIKE '%花子%') OR ("address" LIKE '%北海道%' AND "mainstaff" LIKE '%次郎%' ))
  criteria.add(
      $r.or(
        $r.and(
          $r.like(meta.address, "沖縄"),
          $r.like(meta.mainstaff, "花子")
        ),
        $r.and(
          $r.like(meta.address, "北海道"),
          $r.like(meta.mainstaff, "次郎")
        )
      )
  );

$ R has the following functions. We will connect them with $ r.or () and $ r.and ().

Methods providedDescriptionHow to write code
eqequaleq(meta, values)
neNot equalne(meta, values)
likeBroad match of character stringlike(meta, values)
likePrefixPrefix stringslikePrefix(meta, values)
likeSuffixBackward match of character stringlikeSuffix(meta, values)
gethat's allge(meta, value)
gtGreater thangt(meta, value)
leLess thanle(meta, value)
ltLess thanlt(meta, value)
betweenSearch rangebetween(meta, lowValue, highValue)
isNullIs it null?isNull(meta)
isNotNullWhether it is not nullisNotNull(meta)
1. It is possible to specify multiple values ​​for functions whose arguments are values ​​rather than value.
2. When multiple values ​​are specified eq (), like (), likePrefix (), likeSuffix () are OR conditions and ne () is an AND condition.