kohana i18n errorException with query builder (kohana->leap->mssql)

Question

首先，我对kohana很陌生，但我对更受欢迎的Yii & &;Yii2。

我正在处理复杂的配置，使用查询生成器从leap模型调用MsSQL数据库。现在我不得不优化代码，但遇到了一个很难自己解决的错误。

对MsSQL数据库的请求分为两部分。下面是一个子查询，按照它应该执行的方式执行:

$leap_subquery = DB_SQL::select($this->data_source())
        ->column('lg.SessionID','SESSION_ID')
        ->column(DB_SQL::expr("CAST(DATEADD(HOUR, 3, lg.MsgTimestamp) AS DATE)"),'ACTIVITY_DT')
        ->column(DB_SQL::expr("CAST(DATEADD(HOUR, 3, lg.MsgTimestamp) AS TIME(0))"),'ACTIVITY_TM')
        ->column(DB_SQL::expr("RIGHT(REPLACE(cr.ORIGINATINGNUMBER,'tel:',''),10)"),'PHONE_NUM')
        ->column(DB_SQL::expr("RIGHT(REPLACE(cr.DESTINATIONNUMBER,'tel:',''),5)"),'DESTINATION_NUM')
        ->column(DB_SQL::expr("CAST(CASE
            WHEN UPPER(lg.ActivityName) = 'VDUID' THEN lg.VarValue
            ELSE NULL
            END AS VARCHAR(50))"),'CALL_ID')
        ->column(DB_SQL::expr("CASE
            WHEN UPPER(LEFT(lg.ActivityName,21)) = 'КАЧЕСТВО_ОБСЛУЖИВАНИЯ'
                THEN CAST(RIGHT(lg.ActivityName,1) AS NUMERIC)
            ELSE NULL
        END"),'SERVICE_QUALITY')
        ->column(DB_SQL::expr("CASE
            WHEN UPPER(LEFT(lg.ActivityName,15)) = 'СКОРОСТЬ_ОТВЕТА'
                THEN CAST(RIGHT(lg.ActivityName,1) AS NUMERIC)
            ELSE NULL
        END"),'RESPONSE_SPEED')
        ->column(DB_SQL::expr("CASE
            WHEN UPPER(LEFT(lg.ActivityName,12)) = 'ВОПРОС_РЕШЁН' AND RIGHT(lg.ActivityName,1) = '1' THEN 1
            WHEN UPPER(LEFT(lg.ActivityName,12)) = 'ВОПРОС_РЕШЁН' AND RIGHT(lg.ActivityName,1) = '2' THEN 0
            ELSE NULL
        END"),'QUESTION_SOLUTION')
        ->column(DB_SQL::expr("CASE
            WHEN UPPER(LEFT(lg.ActivityName,18)) = 'ДОБРОЖЕЛАТЕЛЬНОСТЬ'
                THEN CAST(RIGHT(lg.ActivityName,1) AS NUMERIC)
            ELSE NULL
        END"),'AMIABILITY')
        ->column(DB_SQL::expr("CASE
            WHEN UPPER(LEFT(lg.ActivityName,7)) = 'ЯСНОСТЬ'
                THEN CAST(RIGHT(lg.ActivityName,1) AS NUMERIC)
            ELSE NULL
        END"),'CLEARNESS')
        ->column(DB_SQL::expr("CASE
            WHEN UPPER(LEFT(lg.ActivityName,14)) = 'ВНИМАТЕЛЬНОСТЬ'
                THEN CAST(RIGHT(lg.ActivityName,1) AS NUMERIC)
            ELSE NULL
        END"),'ATTENTION')
    ->from('VPStats.dbo.VPAppLog','lg')
    ->join('INNER','VPStats.dbo.CDR','cr')->on('cr.SESSIONID','=','lg.SessionID')
    ->where(DB_SQL::expr("CAST(DATEADD(HOUR, 3, lg.MsgTimestamp) AS DATE)"), '=', DB_SQL::expr("CAST('".$date."' AS DATE)"))
    ->where(DB_SQL::expr("UPPER(lg.ApplicationID)"), '=', 'QUALITY')
    ->where_block('(')
        ->where(DB_SQL::expr("UPPER(LEFT(lg.ActivityName,21))"), '=', 'КАЧЕСТВО_ОБСЛУЖИВАНИЯ'   )
        ->where(DB_SQL::expr("UPPER(LEFT(lg.ActivityName,15))"), '=', 'СКОРОСТЬ_ОТВЕТА',    'OR')
        ->where(DB_SQL::expr("UPPER(LEFT(lg.ActivityName,12))"), '=', 'ВОПРОС_РЕШЁН',       'OR')
        ->where(DB_SQL::expr("UPPER(LEFT(lg.ActivityName,18))"), '=', 'ДОБРОЖЕЛАТЕЛЬНОСТЬ', 'OR')
        ->where(DB_SQL::expr("UPPER(LEFT(lg.ActivityName,7))"),  '=', 'ЯСНОСТЬ',            'OR')
        ->where(DB_SQL::expr("UPPER(LEFT(lg.ActivityName,14))"), '=', 'ВНИМАТЕЛЬНОСТЬ',     'OR')
        ->where(DB_SQL::expr("UPPER(lg.ActivityName)"),          '=', 'VDUID',              'OR')
    ->where_block(')')
    ->statement(FALSE);

但是下一个查询应该查询第一个的一些数据:

$leap_query = DB_SQL::select($this->data_source())
        ->column(DB_SQL::expr("MAX(VP.ACTIVITY_DT"),                   'date'       )
        ->column(DB_SQL::expr("MAX(VP.ACTIVITY_TM"),                   'time'       )
        ->column(DB_SQL::expr("MAX(VP.PHONE_NUM)"),                    'number'     )
        ->column(DB_SQL::expr("MAX(VP.SERVICE_QUALITY)"),              'answer_1'   )
        ->column(DB_SQL::expr("MAX(VP.QUESTION_SOLUTION)"),            'answer_2'   )
        ->column(DB_SQL::expr("MAX(VP.RESPONSE_SPEED)"),               'answer_3'   )
        ->column(DB_SQL::expr("MAX(VP.AMIABILITY)"),                   'answer_4'   )
        ->column(DB_SQL::expr("MAX(VP.CLEARNESS)"),                    'answer_5'   )
        ->column(DB_SQL::expr("MAX(VP.ATTENTION)"),                    'answer_6'   )
        ->column(DB_SQL::expr("MAX(VP.DESTINATION_NUM)"),              'skill'      )
        ->column(DB_SQL::expr("MAX(VP.CALL_ID)"),                      'call_id'    )

    ->from(DB::expr("(".$leap_subquery.")"), 'VP')
    ->group_by(array('VP.SESSION_ID'))
    ->having(DB_SQL::expr("MAX( CALL_ID )"), 'IS NOT', NULL)
    ->having_block('(')
        ->having(DB_SQL::expr("MAX(VP.SERVICE_QUAILITY)"),  'IS NOT', NULL      )
        ->having(DB_SQL::expr("MAX(VP.RESPONSE_SPEED)"),    'IS NOT', NULL, 'OR')
        ->having(DB_SQL::expr("MAX(VP.QUESTION_SOLUTION)"), 'IS NOT', NULL, 'OR')
        ->having(DB_SQL::expr("MAX(VP.AMIABILITY)"),        'IS NOT', NULL, 'OR')
        ->having(DB_SQL::expr("MAX(VP.CLEARNESS)"),         'IS NOT', NULL, 'OR')
        ->having(DB_SQL::expr("MAX(VP.ATTENTION)"),         'IS NOT', NULL, 'OR')
    ->having_block(')');

但是如果第一个块给了我一个结果，调用第二个块会给我一个错误，这个错误与…echm…翻译? ?…:

ErrorException [ Notice ]: Array to string conversion
SYSPATH/classes/kohana/i18n.php [ 164 ]
return empty($values) ? $string : strtr($string, $values);

我一点也不明白。我猜在某些地方出现了语法错误，构建器无法解决这个问题，但我只能猜测&谷歌…请帮我了解一下这个员工的性质…！?

Up:对正在尝试翻译的字符串进行var dump，并得到错误的字符串)):

function __($string, array $values = NULL, $lang = 'en-us')
{
           var_dump($string, $values, $lang);die();
     .......
    return empty($values) ? $string : strtr($string, $values);
}
string(55) "Message: Failed to query SQL statement. Reason: :reason" array(1) { ':reason' => array(5) { [0] => string(5) "00000" [1] => int(0) [2] => string(3276) "(null) [0] (severity 0) [SELECT MAX(VP.ACTIVITY_DT AS [date], MAX(VP.ACTIVITY_TM AS [time], MAX(VP.PHONE_NUM) AS [number], MAX(VP.SERVICE_QUALITY) AS [answer_1], MAX(VP.QUESTION_SOLUTION) AS [answer_2], MAX(VP.RESPONSE_SPEED) AS [answer_3], MAX(VP.AMIABILITY) AS [answer_4], MAX(VP.CLEARNESS) AS [answer_5], MAX(VP.ATTENTION) AS [answer_6], MAX(VP.DESTINATION_NUM) AS [skill], MAX(VP.CALL_ID) AS [call_id] FROM (SELECT [lg].[SessionID] AS [SESSION_ID], CAST(DATEADD(HOUR, 3, lg.MsgTimestamp) AS DATE) AS [ACTIVIT"... [3] => int(0) [4] => int(0) } } string(5) "en-us"

也许任何人…我发现这个问题看起来很奇怪。是否应该有"["&"]"在SQL…?

Answer 1

错误:

->from(DB::expr("(".$leap_subquery.")"), 'VP')

应:

->from($leap_subquery, 'VP')

和不执行$leap_subquery