什么“类型”我在addCase中指定要返回一列吗？ [英] What "type" do I specify in addCase to return a column?

问题描述

我想使用case语句获取一个查询工作，并且不能弄明白如何得到返回一个列值而不是常量的情况。我有查询工作完美，除了我为结果提供的列名称被引用或以其他方式处理由Cake或者PDO下面的一个层，我不能挖通过。我得到了最低的bindValue，但没有一个文档，我遇到的路上告诉我如何做这个。

I'm trying to get a query working using a case statement, and can't figure out how to get the case to return a column value instead of a constant. I have the query working perfectly, except that the column names I'm providing for the results are being quoted or otherwise mishandled by Cake or maybe PDO somewhere down in a layer that I can't dig my way through. I got as far down as bindValue, but none of the documentation I encountered along the way tells me how to do this.

我发现这个例子注释：

$statement->bindValue(1, 'a title');
$statement->bindValue(2, 5, PDO::INT);
$statement->bindValue('active', true, 'boolean');
$statement->bindValue(5, new \DateTime(), 'date');

但在所有这些情况下，提供的值是一个常数。我需要传入一个字符串，这是我想要返回的列的名称。

but in all these cases, the value provided is a constant. I need to pass in a string that is the name of the column that I want returned.

我试过'string'（导致引用的列名）和'integer'（导致0）。我试过 PDO :: FETCH_COLUMN （似乎不太可能，但看起来像下一个最好的赌注从 http://php.net/manual/en/pdo.constants.php ，容易尝试...）。我尝试了'literal'，灵感来自于你可以把文字字符串放入表达式（导致错误：未知类型literal）。该错误消息让我到 src / Database / Type.php ，但没有什么帮助我。

I tried both 'string' (resulted in quoted column name) and 'integer' (resulted in 0). I tried PDO::FETCH_COLUMN (seemed highly unlikely, but looked like the next best bet from http://php.net/manual/en/pdo.constants.php, and easy to try it...). I tried 'literal', inspired by the way you can put literal strings into expressions (resulted in Error: unknown type "literal"). That error message led me to src/Database/Type.php, but nothing in there helped me either.

所以，我几乎很累。这里有一个简单的代码版本（省略了几个条件和不相关的列）：

So, I'm pretty much stumped. Here's a simple version of the code I have (leaving out a couple of conditions and unrelated columns):

$query = $this->Games->find();
$team_id = $query->newExpr()->addCase(
    [$query->newExpr()->eq('Games.status', 'home_default')],
    ['home_team_id', 'away_team_id'],
    ['string', 'string']
);
$defaulting = $query
    ->select([
        'id' => $team_id,
        'count' => 'COUNT(Games.id)',
    ])
    ->where([
        'Games.status IN' => ['home_default', 'away_default'],
    ])
    ->group('id')
    ->toArray();

这会生成此SQL：

SELECT
    (CASE WHEN Games.status = 'home_default'
        THEN 'home_team_id' ELSE 'away_team_id' END) AS `id`,
    COUNT(Games.id) AS `count`
FROM games Games
WHERE Games.status in ('home_default','away_default')
GROUP BY id

注意 THEN'home_team_id'ELSE'away_team_id'END 应该简单地 THEN home_team_id ELSE away_team_id END 。

推荐答案

use Cake\Database\Expression\IdentifierExpression;

// ...

$team_id = $query->newExpr()->addCase(
    [$query->newExpr()->eq('Games.status', 'home_default')],
    [new IdentifierExpression('home_team_id'), new IdentifierExpression('away_team_id')]
);

在这种情况下，还要删除第三个参数，你不希望这些值是字符串文字对于表达式，类型将被忽略）。

Also ditch the third argument in this case, you don't want the values to be string literals (for expressions the types would be ignored anyways).

这篇关于什么“类型”我在addCase中指定要返回一列吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！