使用 Higher Order Function 動態組合 SQL | 點燈坊

在使用 Knex 產生 SQL 時，實務上會遇到 select 與 where 重複出現，或者 select 與 where 動態產生，對於這種 Method Chaining 風格的 Query Builder 可透過 Higher Order Function 動態組合。

Version

Knex 0.20.9

GraphQL

books query 可不提供任何 argument，預設以 pageNum 為 1 與 pageSize 為 3 查詢。

也可提供 no 查詢。

也可提供 price 查詢。

可發現 where 條件為動態的

Knex

import { ApolloServer, gql } from 'apollo-server'
import knex from 'knex'

let mySQL = knex ({
  client: 'mysql',
  connection: {
    host: process.env.MYSQL_HOST || 'localhost',
    port: process.env.MYSQL_PORT || 3306,
    user: 'root',
    password: process.env.MYSQL_ROOT_PASSWORD || 'demo',
    database: 'Bookshelf'
  }
})

let typeDefs = gql`
  type Query {
    books(no: Int, title: String, price: Int, pageNum: Int = 1, pageSize: Int = 3): Books
  }
  
  type Book {
    no: Int
    title: String
    price: Int
  }
  
  type Books {
    totalPage: Int
    data: [Book]
  }
`

let fetchBooksCount = no => title => price => mySQL ('books')
  .count ({ count: '*'})
  .where ('no', no)
  .andWhere ('title', title)
  .andWhere ('price', price)
  .then (x => x[0].count)

let fetchBooks = no => title => price => pageNum => pageSize => mySQL ('books')
  .select ('no', 'title', 'price')
  .where ('no', no)
  .andWhere ('title', title)
  .andWhere ('price', price)
  .limit (pageSize)
  .offset ((pageNum - 1) * pageSize)

let books = async (_, { no, title, price, pageNum, pageSize }) => {
  let totalPage = Math.ceil (await fetchBooksCount (no) (title)(price) / pageSize)
  let result = await fetchBooks (no) (title) (price) (pageNum)(pageSize)

  return {
    totalPage,
    data: result
  }
}

let resolvers = {
  Query: {
    books
  }
}

let apolloServer = new ApolloServer ({ typeDefs, resolvers })

apolloServer.listen ()
  .then (({ url }) => `GraphQL Server ready at ${url}`)
  .then (console.log)

16 行

type Query {
  books(no: Int, title: String, price: Int, pageNum: Int = 1, pageSize: Int = 3): Books
}

no、title 與 price 並沒有規定一定要提供，pageNum 與 pageSize 則有提供預設值。

32 行

let fetchBooksCount = no => title => price => mySQL ('books')
  .count ({ count: '*'})
  .where ('no', no)
  .andWhere ('title', title)
  .andWhere ('price', price)
  .then (x => x[0].count)

根據 where 條件獲得筆數，為了計算 totalPage。

39 行

let fetchBooks = no => title => price => pageNum => pageSize => mySQL ('books')
  .select ('no', 'title', 'price')
  .where ('no', no)
  .andWhere ('title', title)
  .andWhere ('price', price)
  .limit (pageSize)
  .offset ((pageNum - 1) * pageSize)

根據 where 條件獲得結果。

我們可發現這兩個 Knex query 有以下問題：

• 兩個 query 非常類似，出現大量重複部分
• 目前 where 部分都寫死為全部條件，而非動態 query

目前主流 query builder 與 ORM 都是以 OOP 的 method chaining 呈現，但這對於 重複部分 與 動態產生 處理卻非常頭痛。

Higher Order Function

import { ApolloServer, gql } from 'apollo-server'
import knex from 'knex'
import { isNil, identity, pipe } from 'ramda'

let mySQL = knex ({
  client: 'mysql',
  connection: {
    host: process.env.MYSQL_HOST || 'localhost',
    port: process.env.MYSQL_PORT || 3306,
    user: 'root',
    password: process.env.MYSQL_ROOT_PASSWORD || 'demo',
    database: 'Bookshelf'
  }
})

let typeDefs = gql`
  type Query {
    books(no: Int, title: String, price: Int, pageNum: Int = 1, pageSize: Int = 3): Books
  }
  
  type Book {
    no: Int
    title: String
    price: Int
  }
  
  type Books {
    totalPage: Int
    data: [Book]
  }
`

let genSelect = x => x.select ('no', 'title', 'price')

let genCount = x => x.count ({ count: '*' })

let genWhereNo = no => isNil (no) ?
  identity : x => x.where ('no', no)

let genWhereTitle = title => isNil (title) ?
  identity: x => x.where ('title', title)

let genWherePrice = price => isNil (price) ?
  identity: x => x.where ('price', price)

let genPagination = pageNum => pageSize => x => x
  .limit (pageSize)
  .offset ((pageNum - 1) * pageSize)

let fetchBooksCount = no => title => price => pipe (
  genCount,
  genWhereNo (no),
  genWhereTitle (title),
  genWherePrice (price)
) (mySQL ('books')).then(x => x[0].count)

let fetchBooks = no => title => price => pageNum => pageSize => pipe (
  genSelect,
  genWhereNo (no),
  genWhereTitle (title),
  genWherePrice (price),
  genPagination (pageNum) (pageSize)
) (mySQL ('books'))

let books = async (_, { no, title, price, pageNum, pageSize }) => {
  let totalPage = Math.ceil (await fetchBooksCount(no)(title)(price) / pageSize)
  let result = await fetchBooks (no) (title) (price) (pageNum)(pageSize)

  return {
    totalPage,
    data: result
  }
}

let resolvers = {
  Query: {
    books
  }
}

let apolloServer = new ApolloServer ({ typeDefs, resolvers })

apolloServer.listen ()
  .then (({ url }) => `GraphQL Server ready at ${url}`)
  .then (console.log)

35 行

let genCount = x => x.count ({ count: '*' })

產生 count({ count: '*' }) 部分。

33 行

let genSelect = x => x.select ('no', 'title', 'price')

產生 select 部分。

37 行

let genWhereNo = no => isNil (no) ?
  identity : x => x.where ('no', no)

產生 where ('no') 部分，若沒提供 no argument，則 no 為 undefined，因此特別使用 isNil 判斷 no 是否為 undefined。

• 若 no 為 undefined，表示不用提供 where，因此回傳 identity，也就是 x => x
• 若有提供 no，則回傳 where ('no')

40 行

let genWhereTitle = title => isNil (title) ?
  identity: x => x.where ('title', title)

產生 where ('title') 部分，若沒提供 title argument，則 title 為 undefined，因此特別使用 isNil 判斷 title 是否為 undefined。

• 若 title 為 undefined，表示不用提供 where，因此回傳 identity，也就是 x => x
• 若有提供 title，則回傳 where ('title')

43 行

let genWherePrice = price => isNil (price) ?
  identity: x => x.where ('price', price)

產生 where ('price') 部分，若沒提供 price argument，則 price 為 undefined，因此特別使用 isNil 判斷 price 是否為 undefined。

• 若 price 為 undefined，表示不用提供 where，因此回傳 identity，也就是 x => x
• 若有提供 price，則回傳 where ('price')

可發現每個 higher order function 都會判斷 argument 是否存在，因此可達成動態組合 query

46 行

let genPagination = pageNum => pageSize => x => x
  .limit (pageSize)
  .offset ((pageNum - 1) * pageSize)

產生 分頁 部分。

50 行

let fetchBooksCount = no => title => price => pipe (
  genCount,
  genWhereNo (no),
  genWhereTitle (title),
  genWherePrice (price)
) (mySQL('books')).then(x => x[0].count)

根據 where 條件獲得筆數，可根據需要由 pipe 動態組合 query。

57 行

let fetchBooks = no => title => price => pageNum => pageSize => pipe (
  genSelect,
  genWhereNo (no),
  genWhereTitle (title),
  genWherePrice (price),
  genPagination (pageNum) (pageSize)
) (mySQL ('books'))

根據 where 條件獲得結果，可根據需要由 pipe 動態組合 query。

可發現 fetchBooksCount 與 fetchBooks 重複部分不見了

Conclusion

• OOP 很難解決 method chaining 的 重複部分 與 動態組合 兩問題，但藉由 FP 將 query 各部分以 higher order function 產生，最後在將所有小 function 組合成完整 query，則可完美解決此問題，算 higher order function 經典應用

Reference

Andy Peterson, Using Higher-Order Functions to Build Queries in Knex.js