MySQLにストアドファンクションという便利な機能があることを教えてもらった

NO IMAGE

商品の値段を決めるのに、セールス期間があって、セールス価格があって、さらに別の条件を満たしてるとそっちの価格を・・・
なんていうSQLを書いていたらとんでもない行数に\(^-^)/
これってどうにかならないの?と聞いたらストアドファンクションってものを教えて貰ったのでまとめておきます

とあるEC-CUBEのデフォルトの価格計算のSQL部分(抜粋)

~~~
SELECT
product_id,
MIN(product_code) AS product_code_min,
MAX(product_code) AS product_code_max,
MIN(price01) AS price01_min,
MAX(price01) AS price01_max,
MIN(price02) AS price02_min,
MAX(price02) AS price02_max,
MIN(stock) AS stock_min,
MAX(stock) AS stock_max,
MIN(stock_unlimited) AS stock_unlimited_min,
MAX(stock_unlimited) AS stock_unlimited_max,
COUNT(*) as class_count
FROM dtb_products_class
GROUP BY product_id

~~~

商品の価格は規格(SサイズとかMサイズとか)が持っています。
その中の最大価格と最小価格を出して販売価格100円~1000円みたいな表記をしています。
ここにセール期間の場合はセール価格にするよ、というSQLを加えます

~~~
SELECT
product_id,
MIN(product_code) AS product_code_min,
MAX(product_code) AS product_code_max,
MIN(price01) AS price01_min,
MAX(price01) AS price01_max,
MIN(CASE
WHEN sale_price IS NOT NULL AND now() BETWEEN sale_bigin_date AND sale_end_date THEN sale_price
ELSE price02
END
) AS price02_min,
MAX(CASE
WHEN sale_price IS NOT NULL AND now() BETWEEN sale_bigin_date AND sale_end_date THEN sale_price
ELSE price02
END
) AS price02_max,
MIN(stock) AS stock_min,
MAX(stock) AS stock_max,
MIN(stock_unlimited) AS stock_unlimited_min,
MAX(stock_unlimited) AS stock_unlimited_max,
COUNT(*) as class_count
FROM dtb_products_class
GROUP BY product_id
~~~

一目でわかるかと思いますが、ものすごい冗長なSQLになりました。
かつこのロジックを必要なところに全部コピペして修正しなくてはいけません・・・
そしてこのロジックやカラム名が変わったらまた全箇所修正・・・
こんな開発うんざりですよね!
そんな時はMySQLに頼ってみましょう

ストアドファンクションを作成します

~~~
delimiter //
CREATE FUNCTION SALES_PRICE(sale_price INT ,sale_bigin_date DATE ,sale_end_date DATE ,price02 INT) RETURNS INT
RETURN
CASE
WHEN sale_price IS NOT NULL AND now() BETWEEN sale_bigin_date AND sale_end_date THEN sale_price
ELSE price02
END
//
delimiter ;
~~~

SQLはこうなります

~~~
SELECT
product_id,
MIN(product_code) AS product_code_min,
MAX(product_code) AS product_code_max,
MIN(price01) AS price01_min,
MAX(price01) AS price01_max,
MIN(SALES_PRICE(sale_price,sale_bigin_date,sale_end_date,price02)) AS price02_min,
MAX(SALES_PRICE(sale_price,sale_bigin_date,sale_end_date,price02)) AS price02_max,
MIN(stock) AS stock_min,
MAX(stock) AS stock_max,
MIN(stock_unlimited) AS stock_unlimited_min,
MAX(stock_unlimited) AS stock_unlimited_max,
COUNT(*) as class_count
FROM dtb_products_class
GROUP BY product_id
~~~

これでOK、関数を追加するイメージなのでそれほど難しくないと思います。
ただし・・・これはMySQLに持ってしまうのでソースコードからはロジックが追えないというデメリットもあります
また、バージョンや権限にもかなり依存するので使える場面は限られるかと思いますが知ってて損はないと思いました

ものすごい参考にさせていただきました!

MySQLのストアドファンクションで星座の計算 – CODE NAME: TUNE34