数据库关于拖拽排序功能的字段设计和逻辑

一、背景#


最近做的一个比较简单 CMS 项目,其中最“复杂”的功能就是要对表格中的数据实现拖拽排序

实例效果如下:

2021-04-07-00-00-20

二、前端#


用的是 AntDesign 的组件 Table 其中的 拖拽排序 的示例:

https://ant.design/components/table-cn/#components-table-demo-drag-sorting

传给后端的参数格式为:{dragRowId, hoverRowId},id 为此行在数据表里的真实 id。

三、后端 —— 数据库结构字段设计#


(1)方案1 —— 单表单列#

给原表加入 order 字段,即原表结构:id …… order

(2)方案2 —— 单表多列#

给原表加入 prevIdnextId 字段,即原表结构:id …… prevId nextId

(3)方案3 —— 多表单列#

再开一个新表:order,结构为:id content

content 数据类型为 array ,格式形如:{1,3,2,4},里面记录了原表的 id,并且数组的顺序即排序的顺序(可以数组的首项表示最top,或者数组的尾项表示最 top,随你)。

(4)总结#

关于这一节的讨论,让我想到了当初看《SQL 反模式》的其中一章,关于如何设计 “存放(帖子的)评论(可嵌套回复评论)” 的数据库表结构设计,思想还是挺一致的。

感兴趣可以看我之前的那一篇:《SQL 反模式》 学习笔记“第三章 单纯的树”


Result:因我们这次的需求,涉及拖动的数据量并不大,且拖拽行为并不频繁,所以这里我采用比较简单的方案1

四、后端 —— 算法逻辑#

基于上面的方案1。

order 规则:从 1 开始,逐步递增 +1,值越大表示顺序越靠前。

1、order 字段值用整数#

(1)增#

取出原表中最大的 order 值,order+1 即为新行的 order。

(2)删#

直接删

(3)查#

要按顺序 select 出来,很方便,直接按 order 的值递减排序即可。

(4)改(即拖拽排序)#

当用户进行拖拽操作,我们并不需要把原表所有行的 order 字段进行更新,而是只更新 [dragRow, hoverRow] 之间的行的 order

伪代码如下:

  • 1、根据 dragRowId 和 hoverRowId 取出 dragRowOrder 和 hoverRowOrder
  • 2、判断是向上拖拽(up)还是向下拖拽(down)
  • 3、取出 dragRow 与 hoverRow 之间的行列表(注意要按照 order 的顺序取出),下面待用
  • 4、对上面取出的结果:① 赋值:drag 行的 order = hover 行 order ② 剩余的行,order 全部 -1(up) / +1(down)

注意:最好在整个操作期间加上表锁,例如用事务

具体代码如下:

  • 应用代码:Node.js(express)
  • 数据库 ORM:Sequelize (PostgreSQL)
Copy
async function sort(req, res, next) { const {dragRowId, hoverRowId} = req.body; let transaction; try { transaction = await models.sequelize.transaction({isolationLevel: 'SERIALIZABLE'}); // 1、根据 dragRowId 和 hoverRowId 取出 dragRowOrder 和 hoverRowOrder const dragRowItem = await Designer.findOne({ where: { id: dragRowId }, transaction }) const dragRowOrder = dragRowItem.order const hoverRowItem = await Designer.findOne({ where: { id: hoverRowId }, transaction }) const hoverRowOrder = hoverRowItem.order // 2、判断是向上拖拽(up)还是向下拖拽(down) let dragType = null if (dragRowOrder < hoverRowOrder) { dragType = "up" } else if (dragRowOrder > hoverRowOrder) { dragType = "down" } else { throw new Error("您没有进行拖拽操作") } // 3、取出 dragRow 与 hoverRow 之间的行列表(注意要按照 order 的顺序取出),下面待用 const resultList = await Designer.findAll({ where: { order: dragType === "up" ? { [Op.gte]: dragRowOrder, [Op.lte]: hoverRowOrder, } : { [Op.gte]: hoverRowOrder, [Op.lte]: dragRowOrder, } }, order: [["order", "DESC"]], transaction }) // 4、对上面取出的结果:① 赋值:drag 行的 order = hover 行 order ② 剩余的行,order 全部 -1(up) / +1(down) for (let i = 0; i < resultList.length; i++) { if (dragType === "up") { if (i === resultList.length - 1) { await resultList[i].update({order: hoverRowOrder}, {transaction}) } else { await resultList[i].decrement('order', {transaction}) } } else { if (i === 0) { await resultList[i].update({order: hoverRowOrder}, {transaction}) } else { await resultList[i].increment('order', {transaction}) } } } // commit await transaction.commit(); res.json({message: 'ok'}); } catch (error) { // 只要出错就回滚 if (transaction) await transaction.rollback(); next(error) } }

2、order 字段值用浮点数#

(1)增#

取出原表中最大的 order 值,order 向上取整并 +1 即为新行的 order。

(2)删#

跟上面用整数的一致。

(3)查#

跟上面用整数的一致。

(4)改(拖拽排序)#

这里比用整数简单,不用更新 [dragRow, hoverRow] 之间的行的 order,只需要更新一行,即把 dragRow 的 order 改成 (dragRow 上一行 order 值 + dragRow 下一行 order 值)/ 2

这种方法又称 取中值法。

缺点:因为一个数除以2,可能会让小数位+1(如 (1+2)/2=1.5 ),所以如果达到了数据库关于浮点数的最大精度,则会有问题

解决方案:

  • 1、如果用户的拖拽不是很频繁,可以忽略这种错误的可能性,例如 Postgres 的 demical 数据类型,小数的最大精度是 16383,绰绰有余。
  • 2、如果用户的拖拽很频繁,建议创建一个定时任务,把所有行的 order 值重置(用整数值)。

参考资料#


一个基本的用户排序功能为什么这么难?—— 知乎

posted @   小蒋不素小蒋  阅读(12095)  评论(3编辑  收藏  举报
编辑推荐:
· AI与.NET技术实操系列(二):开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识(上)
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
阅读排行:
· 全程不用写代码,我用AI程序员写了一个飞机大战
· DeepSeek 开源周回顾「GitHub 热点速览」
· 记一次.NET内存居高不下排查解决与启示
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· .NET10 - 预览版1新功能体验(一)
历史上的今天:
2017-04-21 用编程解决 公务员考试 中的逻辑推理题

ICP证:沪ICP备20014317号

点击右上角即可分享
微信分享提示
CONTENTS