如何在Eloquent查询中将聚合字段转换为Float类型？

我懂你的困扰——revenue和change_rate是动态计算出来的聚合字段，没法像模型原生属性那样用$casts来做类型转换对吧？这里有两个实用的方法帮你把它们转成Float类型：

方法1：在SQL查询层面直接转换类型 #

你可以在selectRaw里用CAST()函数把计算结果直接转成浮点型，数据库会直接返回数值类型而不是字符串。修改后的查询示例如下：

$productStats->selectRaw("
    product.id,
    TRIM(product.name) AS item_name,
    CAST(SUM(IF(order.order_paid_date BETWEEN '" . $start->timestamp . "' AND '" . $end->timestamp . "' AND order.order_type = 'order' AND (open_count > 0 OR click_count > 0), order_item.total_price, 0)) AS FLOAT) as revenue,
    COALESCE(
        CAST(SUM(IF(order.updated_date BETWEEN '" . $start->timestamp . "' AND '" . $end->timestamp . "' AND order.order_type = 'order' AND (open_count > 0 OR click_count > 0), 1, 0)) AS SIGNED) 
        / NULLIF(CAST(SUM(IF(order.updated_date BETWEEN '" . $start->timestamp . "' AND '" . $end->timestamp . "' AND order.order_type = 'order', 1, 0)) AS SIGNED), 0),
        0
    ) AS change_rate
");

• 这里用CAST(... AS FLOAT)把revenue的求和结果转成浮点型；
• 额外给change_rate加了NULLIF(..., 0)来避免除以零的错误，如果分母为0的话，整个除法结果会变成NULL，再通过COALESCE转成0，更安全。

方法2：在Eloquent集合层面转换 #

如果不想修改SQL语句，也可以在查询结果返回后，用集合的map()方法遍历转换字段类型：

$stats = $productStats->selectRaw("
    product.id,
    TRIM(product.name) AS item_name,
    SUM(IF(order.order_paid_date BETWEEN '" . $start->timestamp . "' AND '" . $end->timestamp . "' AND order.order_type = 'order' AND (open_count > 0 OR click_count > 0), order_item.total_price, 0)) as revenue,
    COALESCE(CAST(SUM(IF(order.updated_date BETWEEN '" . $start->timestamp . "' AND '" . $end->timestamp . "' AND order.order_type = 'order' AND (open_count > 0 OR click_count > 0), 1, 0)) as SIGNED), 0) / CAST(SUM(IF(order.updated_date BETWEEN '" . $start->timestamp . "' AND '" . $end->timestamp . "' AND order.order_type = 'order', 1, 0)) as SIGNED) AS change_rate
")->get()->map(function ($item) {
    // 把字符串转成Float类型
    $item->revenue = (float)$item->revenue;
    // 处理change_rate可能为null的情况，转成0.0
    $item->change_rate = is_null($item->change_rate) ? 0.0 : (float)$item->change_rate;
    return $item;
});

两种方法的对比 #

• SQL层面转换：性能更优，数据库直接返回数值，减少PHP的处理开销；
• 集合层面转换：更灵活，适合需要额外处理（比如空值、特殊格式）的场景，或者当SQL转换有兼容性问题时使用。

内容的提问来源于stack exchange，提问作者danni114