資料庫取資料時,預設欄位依照客製化規則排序

Nov 03, 2023

取大筆資料或是分頁資料時,通常會有排序這個功能,除了依照時間、點閱數量,這種跟數學有關的都是好處理的,如果遇到名單或是文字,被需求單位開出一些客製化規則,特別又碰到中文筆畫排序.....,就需要一點設計了

使用技術

程式語言與框架大同小異,設計與原理能掌握就好

  1. TypeOrm
  2. Postgres

客製化排序規則

此時排序的需求為:

  1. 符號
  2. 數字
  3. 英文
  4. 中文筆畫

設計思路

如果是SQL高手,當然可以用

CASE WHEN post.title ~ \'^[!@#$%^&*()_+{}\\[\\];:<>,.?|`~-]\' THEN 1 WHEN post.title ~ \'^[0-9]\' THEN 2 WHEN post.title ~ \'^[a-zA-Z]\' THEN 3 ELSE 4 END'

然後我就卡在 符號 regex '" 跳脫都試不出來.....(寫typeorm時),後續還要把欄位轉成big5(big5排序依照筆畫,但有些難字會加在後面)

所以後來就想一個在資料寫入資料庫時可以做的前處理

開一個欄位 order

當我在把資料存入時,把需要排序的字串,丟入一個 function回傳給我排序的數字,存入 order欄位

export function checkStrTypeReturnOrder(str: string): number { // 如果為空直,排在最後 if (!str) return 5; const firstWord = str[0]; // 中文utf的範圍 if (/[\u4e00-\u9fa5]/.test(firstWord)) return 4; // 英文的範圍 if (/[a-zA-Z]/.test(firstWord)) return 3; // 數字 if (/[0-9]/.test(firstWord)) return 2; // 都不是,就是符號 return 1; }

在query的地方加上排序條件

此處用 TypeOrm的 query builder作為範例

const query = this.postRepo.createQueryBuilder('post') .....(略) query.orderBy('post.order', SORT_TYPE.ASC) .addOrderBy('post.titleTw COLLATE "zh-Hant-TW-x-icu"')

他會先以 order 排序為優先,title 為次要,剛好符合需求,指定 COLLATE用繁體中文 zh-Hant-TW-x-icu筆畫方式排序。

結論

雖然直接用SQL解決很帥,但我只考慮到容易維護,還有如果在寫入時能夠快取(先處理),用到 Postgres的 collation,似乎比硬幹優雅一點。

Ekman Hsieh
Ekman Hsieh

文字工作者,寫作時間常常在人類與電腦之間拉鋸,相信閱讀,相信文字與思想所構築的美麗境界