読者です 読者をやめる 読者になる 読者になる

tohokuaikiのチラシの裏

技術的ネタとか。

Confluenceのプラグイン開発を承ります。ご連絡はこちらのホームページからお願いいたします。

前方一致・後方一致・NOT LIKEに対応したEthnaのAppSQL

php Ethna

AppSQL::getCondition()をこんな感じで変更。
OBJECT_CONDITION_LIKE_R
OBJECT_CONDITION_LIKE_L
OBJECT_CONDITION_NOT_LIKE
が使える。

<?php
    /**
     *  WHERE条件文を生成する
     *
     *  @access public
     *  @param  string  $field      検索対象のフィールド
     *  @param  mixed   $value      検索値
     *  @param  int     $condition  検索条件(OBJECT_CONDITION_NE,...)
     *  @return string  検索条件文
     *  @static
     */
    public static function getCondition($field, $value, $condition = OBJECT_CONDITION_EQ)
    {
        switch ($condition) {
        case OBJECT_CONDITION_EQ:
            $op = "="; break;
        case OBJECT_CONDITION_NE:
            $op = "!="; break;
        case OBJECT_CONDITION_LIKE:
        case OBJECT_CONDITION_LIKE_R:
        case OBJECT_CONDITION_LIKE_L:
            $op = "LIKE"; break;
        case OBJECT_CONDITION_NOT_LIKE:
            $op = "NOT LIKE"; break;
        case OBJECT_CONDITION_GT:
            $op = ">"; break;
        case OBJECT_CONDITION_LT:
            $op = "<"; break;
        case OBJECT_CONDITION_GE:
            $op = ">="; break;
        case OBJECT_CONDITION_LE:
            $op = "<="; break;
        }

        // default operand
        $operand = $value;

        if (is_array($value)) {
            if (count($value) > 0) {
                switch ($condition) {
                case OBJECT_CONDITION_EQ:
                    $op = "IN"; break;
                case OBJECT_CONDITION_NE:
                    $op = "NOT IN"; break;
                }
                $operand = sprintf("(%s)", implode(',', $value));
            } else {
                // always be false
                $op = "=";
                $operand = "NULL";
            }
        } else {
            if ($value == 'NULL') {
                switch ($condition) {
                case OBJECT_CONDITION_EQ:
                    $op = "IS"; break;
                case OBJECT_CONDITION_NE:
                    $op = "IS NOT"; break;
                }
            }
            switch ($condition){
              case OBJECT_CONDITION_LIKE:
              case OBJECT_CONDITION_LIKE_R:
              case OBJECT_CONDITION_LIKE_L:
              case OBJECT_CONDITION_NOT_LIKE:
                Ethna_AppSQL::unescapeSQL($value);
                if ($condition == OBJECT_CONDITION_LIKE || $condition == OBJECT_CONDITION_NOT_LIKE) {
                    $value = '%' . str_replace('%', '\\%', $value) . '%';
                }
                else if ($condition == OBJECT_CONDITION_LIKE_L) {
                    $value = '%' . str_replace('%', '\\%', $value);
                }
                else if ($condition == OBJECT_CONDITION_LIKE_R) {
                    $value = str_replace('%', '\\%', $value) . '%';
                }
                Ethna_AppSQL::escapeSQL($value);
                $operand = $value;
                break;
            }
        }
        
        $ret = "$field $op $operand";
        if ($condition == OBJECT_CONDITION_NOT_LIKE){
            $ret = sprintf('(%s OR %s IS NULL)', $ret, $field);
        }
        return $ret;
    }