PHP+PostgreSQL字符串转Int解决字典排序问题及排障

我正在开发一个基于PHP的API，从PostgreSQL数据库获取数据。其中某个字段的查询结果默认按字典序排序，我想把它转成整数类型调整排序逻辑，但尝试多种CAST方式后label字段没有数据返回（试过用INTEGER替换SIGNED）。以下是相关代码片段及修改后的代码，求正确的实现方案。

原代码：

case( 'location_group' ):
    $filter_label_column = 'location_group_' . $pk_location_group_type . '_name';

    if( !is_missing_or_empty( $request['params'], 'filter_label' ) )
    {
          $filter_label_column = 'location_group_' . $pk_location_group_type . '_' . $request['params']['filter_label'];
    }

    $query->select( [ $filter . "_$pk_location_group_type", 'pk' ], [ $filter_label_column, 'label' ] )
          ->group( $filter . "_$pk_location_group_type", $filter_label_column )
          ->order( 'label', 'asc' );
    break;

修改后的尝试代码：

case 'location_group':
    $filter_label_column = 'location_group_' . $pk_location_group_type . '_name';

    if (!is_missing_or_empty($request['params'], 'filter_label')) {
        $filter_label_column = 'location_group_' . $pk_location_group_type . '_' . $request['params']['filter_label'];
    }

    $query->select([$filter . "_$pk_location_group_type", 'pk'], ["CAST($filter_label_column AS SIGNED)", 'label'])
          ->group($filter . "_$pk_location_group_type", "CAST($filter_label_column AS SIGNED)")
          ->order('label', 'asc');
    break;

问题根源 #

1. PostgreSQL不支持SIGNED类型：SIGNED是MySQL专属语法，PostgreSQL中无此类型，直接使用会导致转换失败，返回空数据。
2. 字段值无法合法转换为整数：如果$filter_label_column对应的字段包含非数字字符，强行用CAST(xxx AS INTEGER)会触发查询错误，导致无数据返回。

可行替代方案 #

方案1：安全转换整数+保留原始字段 #

通过正则提取数字内容，搭配容错函数避免转换失败，同时保留原始label字段返回：

case 'location_group':
    $filter_label_column = 'location_group_' . $pk_location_group_type . '_name';

    if (!is_missing_or_empty($request['params'], 'filter_label')) {
        $filter_label_column = 'location_group_' . $pk_location_group_type . '_' . $request['params']['filter_label'];
    }

    // 提取字段中的数字部分，转换失败则设为0
    $sort_key_expr = "COALESCE(REGEXP_REPLACE($filter_label_column, '[^0-9]', '', 'g')::integer, 0)";

    $query->select(
        [$filter . "_$pk_location_group_type", 'pk'],
        [$filter_label_column, 'label'],
        [$sort_key_expr, 'sort_key']
    )
    ->group($filter . "_$pk_location_group_type", $filter_label_column)
    ->order('sort_key', 'asc');
    break;

• 新增sort_key字段专门用于排序，避免修改原始label的返回内容。
• REGEXP_REPLACE清除非数字字符，确保转换合法性；COALESCE处理转换失败场景，避免查询报错。

方案2：直接按数值逻辑排序（不修改查询字段） #

如果字段本身是纯数字字符串，可直接在排序阶段做类型转换，无需修改SELECT语句：

case 'location_group':
    $filter_label_column = 'location_group_' . $pk_location_group_type . '_name';

    if (!is_missing_or_empty($request['params'], 'filter_label')) {
        $filter_label_column = 'location_group_' . $pk_location_group_type . '_' . $request['params']['filter_label'];
    }

    $query->select([$filter . "_$pk_location_group_type", 'pk'], [$filter_label_column, 'label'])
          ->group($filter . "_$pk_location_group_type", $filter_label_column)
          ->order("$filter_label_column::numeric", 'asc');
    break;

• 利用PostgreSQL的::numeric语法直接转换排序依据，保持查询字段不变。

方案3：混合内容的多规则排序 #

如果字段同时包含数字和非数字内容，可先按数字排序，非数字内容统一排到末尾，再按字典序排序：

case 'location_group':
    $filter_label_column = 'location_group_' . $pk_location_group_type . '_name';

    if (!is_missing_or_empty($request['params'], 'filter_label')) {
        $filter_label_column = 'location_group_' . $pk_location_group_type . '_' . $request['params']['filter_label'];
    }

    // 数字优先排序，非数字内容默认用999999排到最后，再按原字段字典序排序
    $sort_expr = "COALESCE(REGEXP_REPLACE($filter_label_column, '[^0-9]', '', 'g')::integer, 999999), $filter_label_column";

    $query->select([$filter . "_$pk_location_group_type", 'pk'], [$filter_label_column, 'label'])
          ->group($filter . "_$pk_location_group_type", $filter_label_column)
          ->orderRaw($sort_expr);
    break;

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