🔍

sqlcで動的なクエリを作る　編集と検索

2025/08/18に公開

この内容は備忘録に近い　似たような課題を抱えている人がいたら見て欲しい。
端的にいうとsqlcの独自の関数であるsqlc.narg()という関数とSQL側の関数であるCOALESCE関数を併用することによって実装している。
sqlc.arg sqlc.nargというのは、
sqlcでは$1, $2
のように引数を指定して、それをsqlcが型と名前を推測するのが一般的だが、sqlc.argというのは一般的なプログラムの引数のように、柔軟に引数を渡すことができる。
sqlc.nargは単にnullが可能になっているというだけである。
しかし問題は、sqlc.nargの内容がnullであった場合にも正常にプログラムが動作する必要があるということである。
この課題を、今回私が引用しているプログラムでは解決している。
（決して美しくないが）

さて、今回のようなプログラムが必要になった経緯について話そう。
私は今身内用の簡単なCRMを作成している。たいした要件じゃない。
CRMというのは、単にお客さんの情報を管理する帳簿だ。それゆえに編集しなきゃいけない項目が多い。
また、使うのは営業スタッフなので、検索する時に利用する情報をメモ欄に書くことも想定されるし、埼玉県の顧客に集中して電話をかけたいという要件も発生しうるので、検索対象もそれなりに多くなってしまう。
具体的には、検索対象は以下のような感じだ。内容を完結にするために一部省略している。
name 顧客名
book 顧客情報が所属する帳簿
corporation 顧客の法人名
address 住所
memo メモ

今回の例ではオーバーヘッドを減らすために帳簿の選択は強制としているが、他４つのそれぞれの検索パターンを組み合わせたクエリを作って回るとしたら、
4!分クエリを作る必要があるので、24個作らないといけない。はっきり言って美しくない。
(この計算式はだいぶ適当。数学が詳しい人は突っ込んで欲しい。)
GetCustomerByName
GetCustomerByNameAndCorporation
GetCustomerByNameAndAddress
...

と言った感じだ。これをどうやって解決するか
下記のプログラムは吾輩が実際に進めているOSSプロジェクトから引用している

customer.sql

-- name: UpdateCustomer :one
UPDATE "Customer"
SET 
  name = COALESCE(sqlc.narg(name), name),
  book_id = COALESCE(sqlc.narg(book_id), book_id),
  corporation = COALESCE(sqlc.narg(corporation), corporation),
  address = COALESCE(sqlc.narg(address), address),
  memo = COALESCE(sqlc.narg(memo), memo)
WHERE
  id = sqlc.arg(id)
RETURNING *;

sqlc.argとnargの使用については、さっき説明した通りだ。
COALESCEというのはsql側の関数で、第一引数の内容がnullであった場合に、右の第二引数の内容が適応されるというものだ。
つまりどうなるか。

name = COALESCE(sqlc.narg(name), name),

この行で起きていることを説明すると、sqlc.nargによって渡されたnameに"bitcat"情報が入っていた場合は、
name = 'bitcat'
となる。値がnullの場合は、name = nameとなる。
これはどういう意味なのかというと、もともと入っていた値で更新するという意味になる。
つまり、何もしない。
これによって、編集したい内容だけをリクエストとして受け取って、クエリを実行するだけなので通信畳にオーバーヘッドが生じないし、汎用性も高めることが可能なのである。

そして、これはその応用　動的な検索だ。

customer.sql

-- name: SearchCustomer :many
SELECT DISTINCT c.* FROM "Customer" c
LEFT JOIN "Contact" ct ON c.id = ct.customer_id
WHERE c.book_id = sqlc.arg(book_id)
AND c.name ILIKE '%' || COALESCE(sqlc.narg(name), c.name) || '%'
AND c.corporation ILIKE '%' || COALESCE(sqlc.narg(corporation), c.corporation) || '%'
AND c.address ILIKE '%' || COALESCE(sqlc.narg(address), c.address) || '%'
AND c.memo ILIKE '%' || COALESCE(sqlc.narg(memo), c.memo) || '%';

これも複雑だが本質的には同じだ。
値がnullの場合無条件でそのカラムの検索はtrueになるため、引数が多くなれば多くなるほど絞り込まれることになる。

ちなみに実際にコードでクエリを叩く時にマッピングする時はこんな感じ。
grpcを使っているため若干コードがきっちゃいないが、copierなどを使ってマッピングを簡略化するのもありだろう。

service/customer.go

	customerArg := db.SearchCustomerParams{
		BookID: uuid.MustParse(customer.GetBookId()),
		Name: pgtype.Text{
			String: customer.GetName(),
			Valid:  customer.GetName() != "",
		},
		Corporation: pgtype.Text{
			String: customer.GetCorporation(),
			Valid:  customer.GetCorporation() != "",
		},
		Address: pgtype.Text{
			String: customer.GetAddress(),
			Valid:  customer.GetAddress() != "",
		},
		Memo: pgtype.Text{
			String: customer.GetMemo(),
			Valid:  customer.GetMemo() != "",
		},
	}

余談だが、この検索に関する部分を部分検索つまりILIKEで実装しているが、
bookのような外部キーなどで検索したい場合は、当然=による検索がパッと思いつくが、
今回使った方式では=を使った条件式はFALSEになるので、要注意だ。
もっと美しい解を見つけている同志があれば、コメント欄に書いて欲しい。

諸君！また会おう！

Discussion