thinkphp5 中 使用ORDER BY FIELD() 自定义排序

这是我参与11月更文挑战的第1天，活动详情查看：2021最后一次更文挑战

前言

产品：要给每个商品加上品牌方的信息, 然后要根据品牌方排序顺序来对商品进行排序，
     可以实现吗?
我：  我觉得吧,这个需求需要再……
产品：(打断施法) 就这样吧，下班前改给我
我：  (老人、地铁、手机）

os:既然需求是这样，那就埋头干吧,还能说什么呢

正文

常规排序 order

    // 商品列表
    $goodsList = db('goods')
        ->field('id, title, weigh')
        ->order('weigh DESC')
        ->select();

数据库返回结果：

id	title	weigh
3	商品A	3
2	商品A	2
1	商品A	1

自定义排序 order by field()

我们在查询数据的时候，可以在ORDER BY 后面通过使用 FIELD() 函数，通过控制val1,val2的值来获取到我们想要的排序数据

    ORDER BY FIELD(`field_name`, 'val1', 'val2', 'val3')

需求例子

1. 新增 brand 表，记录品牌方的信息，添加几条数据进去

brand表

    SELECT `id`, `name`, `weigh` FROM `brand`;

数据库返回结果：

id	name	weigh
1	掘金	1
2	抖音	3
3	今日头条	2

2. goods表这边新增字段 brand_id , 并关联上 brand 表那边的id

goods表

    SELECT `id`, `title`, `weigh`, `brand_id` FROM `goods`;

数据库返回结果：

id	title	weigh	brand_id
1	商品A	1	1
2	商品B	2	2
3	商品C	3	3

3. 接下来我们使用FIELD()函数来对商品进行自定义排序

    // 获取已经排序好品牌方`id`
    $brandList = db('brand')
        ->order('weigh DESC')
        ->column('id');
    // ==>>> [2, 3, 1];
        
    // 转换为字符串
    $brandWeighString = implode(',', $brandList);
    // ==>> 2,3,1
    
    // 排序
    $goodsOrder = 'FIELD(`brand_id`, ' . $brandWeighString . ')';
    
    // 使用 field() 函数
    // `V5.0.17+`版本开始，当你的order排序中使用了SQL函数的时候，请使用`orderRaw`方法替代`order`
    $goodsList = db('goods')
        ->field('id, title, weigh')
        ->orderRaw($goodsOrder)
        ->select();
        
    // 生成的SQL
    SELECT `id`,`title`,`weigh`,`brand_id` FROM `goods` ORDER BY FIELD(`brand_id`, 2,3,1);

goods表返回结果

id	title	weigh	brand_id
2	商品B	2	2
3	商品C	3	3
1	商品A	1	1

此时可以看到 $goodsList 的数据便是按照$brandWeighString 的值来输出的。

$goodsList 会先显示 brand_id 的值为2 然后是3，最后才是1

ORDER BY FIELD() 结合 CASE WHEN ELSE END

如果我们在goods表加上商品D的话，再使用ORDER BY FIELD()会发生什么结果呢?

goods表

id	title	weigh	brand_id
1	商品A	1	1
2	商品B	2	2
3	商品C	3	3
4	商品D	4

使用ORDER BY FEILD()

SELECT `id`,`title`,`weigh`,`brand_id` FROM `goods` ORDER BY FIELD(`brand_id`, 2,3,1);

id	title	weigh	brand_id
4	商品D	4
2	商品B	2	2
3	商品C	3	3
1	商品A	1	1

可以看到，商品D跑到了最前面，然后才是按照field()函数里给定的顺序2,3,1来查询数据。

这是因为FIELD()函数，如果在值列表中找不到指定的值，则此函数将返回0。如果value为NULL，则此函数将返回0。

那如果我们想要先显示给定顺序的数据，然后再显示带有空值的数据，该怎么办呢?

这里可以用到MYSQL另外的一个语法， CASE WHEN ELSE END

thinkphp5写法：

    // 获取已经排序好品牌方`id`
    $brandList = db('brand')
        ->order('weigh DESC')
        ->column('id');
    // ==>>> [2, 3, 1];
        
    // 转换为字符串
    $brandWeighString = implode(',', $brandList);
    // ==>> 2,3,1

    // CASE WHEN ELSE END 语法
    $goodsCase = "CASE WHEN `brand_id` = '' THEN 1 ELSE 0 END";
    
    // FIELD排序
    $orderField = 'FIELD(`brand_id`, ' . $brandWeighString . ')';

    // 拼接条件
    $goodsOrderRaw = $goodsCase . ',' . $orderField;
        
    $goodsList = db('goods')
        ->field('id, title, weigh, brand_id')
        ->orderRaw($goodsOrderRaw)
        ->select();

生成SQL：

    SELECT `id`,`title`,`weigh`, `brand_id` FROM `goods` ORDER BY CASE WHEN `brand_id` = '' THEN 1 ELSE 0 END, FIELD(`brand_id`, 2,3,1);

返回结果：

id	title	weigh	brand_id
2	商品B	2	2
3	商品C	3	3
1	商品A	1	1
4	商品D	4

结尾

第一次发文，欢迎大家指出不足之处,可以一起探讨问题!

PHP是世界上最好的语言!! (大声)