member 等のエンドポイントでは全データを返す手段を用意する

[kokoichi206]

やりたいこと

• gn のクエリパラメータを必須じゃなくする
• パラメータが渡ってきた時は今まで通り
• パラメーターがない場合は、全グループのデータを返す

db query

現状 sql を発行している sqlboiler の部分は以下。

var jMember []MemberInfoBind
err := models.Members(
    qm.Select("member_infos.*", "members.*"),
    qm.InnerJoin("member_infos on members.member_id = member_infos.member_id"),
    qm.InnerJoin("locales on locales.locale_id = member_infos.locale_id AND locales.locale_id = ?", locale),
    qm.InnerJoin("groups on groups.group_id = members.group_id"),
    qm.Where("groups.group_name = ?", groupName),
).Bind(ctx, q.DB, &jMember)

[kokoichi206]

変更方法1: = -> LIKE

qm.Where("groups.group_name = ?", groupName) の部分を以下のようにしたらなんとかなるけど、これ以外に方法があるかな

そして、全グループ検索の時（gn が渡ってきてない時）は groupName = "%" として渡す

var jMember []MemberInfoBind
err := models.Members(
    qm.Select("member_infos.*", "members.*"),
    qm.InnerJoin("member_infos on members.member_id = member_infos.member_id"),
    qm.InnerJoin("locales on locales.locale_id = member_infos.locale_id AND locales.locale_id = ?", locale),
    qm.InnerJoin("groups on groups.group_id = members.group_id"),
    qm.Where("groups.group_name LIKE ?", groupName),
).Bind(ctx, q.DB, &jMember)

変更方法2: CASE を使う

var jMember []MemberInfoBind
err := models.Members(
    qm.Select("member_infos.*", "members.*"),
    qm.InnerJoin("member_infos on members.member_id = member_infos.member_id"),
    qm.InnerJoin("locales on locales.locale_id = member_infos.locale_id AND locales.locale_id = ?", locale),
    qm.InnerJoin("groups on groups.group_id = members.group_id"),
    qm.Where("CASE WHEN ? != '' THEN groups.group_name = ? ELSE '1' END", groupName, groupName),
).Bind(ctx, q.DB, &jMember)

その他に探したいこと

• where 句を特定の条件のみ発行させる方法はあるか
• LIKE 句で % 等を使わないときは完全一致と同じ（= 指定）と思ってるが本当に同じか
  • 発行されるクエリを調べておく

[kokoichi206]

CASE

groupName = 'nogizaka'

EXPLAIN   SELECT member_infos.*, members.* FROM "members" INNER JOIN member_infos on members.member_id = member_infos.member_id INNER JOIN locales on locales.locale_id = member_infos.locale_id AND locales.locale_id = 1 INNER JOIN groups on groups.group_id = members.group_id WHERE (CASE WHEN 'nogizaka' != '' THEN groups.group_name = 'nogizaka' ELSE '1' END);"

                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=8.47..20.74 rows=1 width=205)
   ->  Nested Loop  (cost=8.32..12.56 rows=1 width=205)
         ->  Hash Join  (cost=8.18..12.16 rows=1 width=38)
               Hash Cond: (members.group_id = groups.group_id)
               ->  Seq Scan on members  (cost=0.00..3.57 rows=157 width=38)
               ->  Hash  (cost=8.17..8.17 rows=1 width=4)
                     ->  Index Scan using groups_group_name_key on groups  (cost=0.15..8.17 rows=1 width=4)
                           Index Cond: ((group_name)::text = 'nogizaka'::text)
         ->  Index Scan using member_info_id_locale_unique on member_infos  (cost=0.14..0.39 rows=1 width=167)
               Index Cond: ((member_id = members.member_id) AND (locale_id = 1))
   ->  Index Only Scan using locales_pkey on locales  (cost=0.15..8.17 rows=1 width=4)
         Index Cond: (locale_id = 1)
(12 rows)

groupName = ''

EXPLAIN   SELECT member_infos.*, members.* FROM "members" INNER JOIN member_infos on members.member_id = member_infos.member_id INNER JOIN locales on locales.locale_id = member_infos.locale_id AND locales.locale_id = 1 INNER JOIN groups on groups.group_id = members.group_id WHERE (CASE WHEN '' != '' THEN groups.group_name = 'nogizaka' ELSE '1' END);

                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Hash Join  (cost=41.48..54.57 rows=86 width=205)
   Hash Cond: (members.group_id = groups.group_id)
   ->  Nested Loop  (cost=11.22..24.10 rows=86 width=205)
         ->  Index Only Scan using locales_pkey on locales  (cost=0.15..8.17 rows=1 width=4)
               Index Cond: (locale_id = 1)
         ->  Hash Join  (cost=11.07..15.07 rows=86 width=205)
               Hash Cond: (members.member_id = member_infos.member_id)
               ->  Seq Scan on members  (cost=0.00..3.57 rows=157 width=38)
               ->  Hash  (cost=10.00..10.00 rows=86 width=167)
                     ->  Seq Scan on member_infos  (cost=0.00..10.00 rows=86 width=167)
                           Filter: (locale_id = 1)
   ->  Hash  (cost=19.00..19.00 rows=900 width=4)
         ->  Seq Scan on groups  (cost=0.00..19.00 rows=900 width=4)
(13 rows)