y4shiro commented 2 years ago

[x] Supabase CLI のセットアップ
[x] 必要なデータを取得できるように Supabase Client のクエリを組む
[x] Supabase 側で必要な View を作成
- [x] effects
- [x] event_skills
- [x] training_skills
- [x] training_status_gain
- [x] 全てを合成した view_cards_json
[x] cards_json の型定義

y4shiro commented 2 years ago

Supabase CLI のセットアップ

公式ドキュメントを参考にする Quickstart: Next.js | Supabase

supabase-js をインストール

yarn add @supabase/supabase-js@rc

Supabase のアクセスに必要な環境変数を用意

.evn.local

NEXT_PUBLIC_SUPABASE_URL=<SUPABASE_URL>
NEXT_PUBLIC_SUPABASE_ANON_KEY=<SUPABASE_ANON_KEY>

任意のコンポーネントから Supabase のテーブルを読み込み

import { supabase } from '@/utils/supabaseClient';
// ...
  const getCards = async () => {
    try {
      const { data, error } = await supabase.from('cards').select('*');
      console.log(data);
    } catch (error) {
      console.log(error);
    }
  };

  getCards();
// ...

y4shiro commented 2 years ago

必要なデータを取得できるように Supabase Client のクエリを組む

最終的に複数テーブルを結合した結果を JSON で取得したいが、複雑なクエリを JS 側で組めるのか調査無理なら Supabase 側で View の作成 or RPC による関数定義を検討する

JS 側でクエリを組むのは現実的ではないので、Supabase 側で結合するクエリ組んで JS 側で完成した JSON を読み込む方向に決定 Supabase Client で JSON を fetch するコード例

const { data, error } = await supabase.from('view_cards_json').select('*');

y4shiro commented 2 years ago

Supabase 側で必要な View を作成

現在の DB の ER図を示す uma_card_er 今回は赤枠で囲った各中間テーブルの結合結果を View として作成し、最終的に cards と結合した View を作成する

最終的に作成したい JSON の構造は次の通り

[
  {
    "card_id": 30001,
    "card_name": "[日本一のステージを]スペシャルウィーク",
    "charactor_name": "スペシャルウィーク",
    "card_rarity": "SSR",
    "card_type": "Guts",
    "card_img_path": null,
    "effects": [],
    "event_skills": [],
    "training_skills": [],
    "training_status_gain": []
  },
// 省略
]

effects / event_skills / training_skills / training_status_gain の View をそれぞれ定義していく

y4shiro commented 2 years ago

`effects` の View

作成したい View の構造を提示する

[
  {
    "card_id": 30001,
    "effects": [
      {
        "id": 1,
        "name": "友情ボーナス",
        "values": [
          { "level": 20 , "value": 14 },
          { "level": 25 , "value": 15 },
          { "level": 30 , "value": 15 },
          { "level": 35 , "value": 16 },
          { "level": 40 , "value": 18 },
          { "level": 45 , "value": 20 },
          { "level": 50 , "value": 20 },
        ],
      },
  // 以降省略
]

今回配列を生成するために PostgreSQL の 'json_agg()' 関数を利用するが、この関数は集計関数であるため直接ネストすることが出来ない View を 2 つ定義し、それぞれで 'json_agg()' を利用する

最初に "effects" 配列を結合する View を定義

create view view_effects_array as
select card_effects.card_id,
       json_build_object(
         'id', card_effects.effect_id,
         'name', effects.name,
         'values',
         json_agg(json_build_object(
           'level', card_effects.level,
           'value', card_effects.value
         ))
       ) as effects
  from card_effects
  join effects
    on card_effects.effect_id = effects.id
 group by card_effects.card_id, card_effects.effect_id, effects.name
 order by card_effects.card_id;

この SQL が生成する Effects のレコード

{
    "card_id": 30001,
    "effects": {
        "id": 1,
        "name": "友情ボーナス",
        "values": [
            { "level": 20, "value": 14 },
            { "level": 25, "value": 15 },
            { "level": 30, "value": 15 },
            { "level": 35, "value": 16 },
            { "level": 40, "value": 18 },
            { "level": 45, "value": 20 },
            { "level": 50, "value": 20 }
        ]
    }
}

上で作成した view_effects_array の結果を結合し、1つの配列にまとめる

create view view_card_effects as
select cards.id as card_id,
       json_agg(
         effects_array.effects
       ) as values
  from cards
  join view_effects_array as effects_array
    on cards.id = effects_array.card_id
 group by cards.id
 order by cards.id;

この SQL の結果

{
    "card_id": 30001,
    "values": [/* view_effects_array の結果を配列要素として格納 */]
}

y4shiro commented 2 years ago

`event_skills`

create view view_card_event_skills as
select cards.id as card_id,
       case
         when count(card_event_skills.card_id) = 0 then '[]'
         else json_agg(
                json_build_object(
                  'id', skills.id,
                  'name', skills.name,
                  'skill_pt', skills.skill_pt,
                  'img_path', skills.img_path
                )
             )
         end as values
  from cards
  left join card_event_skills
    on cards.id = card_event_skills.card_id
  left join skills
    on card_event_skills.skill_id = skills.id
 group by cards.id
 order by cards.id;

イベントで取得できるスキルの中間テーブル card_event_skills を用いて、cards と skills テーブルを結合する実行結果は次の通り

{
    "card_id": 30001,
    "values": [
        {
            "id": 200511,
            "name": "全身全霊",
            "skill_pt": 170,
            "img_path": null
        },
        {
            "id": 200512,
            "name": "末脚",
            "skill_pt": 170,
            "img_path": null
        },
        // 以降省略
    ]
}

y4shiro commented 2 years ago

`training_skills`

event_skills とデータ構造はほぼ同じなので実装関連の説明は省略こちらはサポートカードを編成した際にトレーニングで取得できるスキルの配列を持つ

create view view_card_training_skills as
select cards.id as card_id,
       case
         when count(card_training_skills.card_id) = 0 then '[]'
         else json_agg(
                json_build_object(
                  'id', skills.id,
                  'name', skills.name,
                  'skill_pt', skills.skill_pt,
                  'img_path', skills.img_path
                )
             )
         end as values
  from cards
  left join card_training_skills
    on cards.id = card_training_skills.card_id
  left join skills
    on card_training_skills.skill_id = skills.id
 group by cards.id
 order by cards.id;

実行結果

{
    "card_id": 30001,
    "values": [
        {
            "id": 200162,
            "name": "道悪○",
            "skill_pt": 90,
            "img_path": null
        },
        {
            "id": 200232,
            "name": "雨の日○",
            "skill_pt": 90,
            "img_path": null
        },
        // 以降省略
    ]
}

y4shiro commented 2 years ago

`training_status_gain`

こちらはサポートカードを編成した際にトレーニングで取得できるステータスの配列を持つ

create view view_card_training_status_gain as
select cards.id as card_id,
       case
         when count(ctsg.card_id) = 0 then '[]'
         else json_agg(
           json_build_object(
           'id', ctsg.status_gain_id,
           'name', status_gain.name_ja,
           'value', ctsg.status_value
           )
         )
       end as values
  from cards
  left join card_training_status_gain as ctsg
    on cards.id = ctsg.card_id
  left join status_gain
    on ctsg.status_gain_id = status_gain.id
 group by cards.id
 order by cards.id;

実行結果この例だと、トレーニングによりスピード 1 / パワー 1 / 根性 6 のパラメータを取得することが出来る

{
    "card_id": 30001,
    "values": [
        {
            "id": 1,
            "name": "スピード",
            "value": 1
        },
        {
            "id": 3,
            "name": "パワー",
            "value": 1
        },
        {
            "id": 4,
            "name": "根性",
            "value": 6
        }
    ]
}

y4shiro commented 2 years ago

全てを合成した `view_cards_json`

これまでに定義したテーブルや View を結合し、Web アプリで使用する JSON データを生成する単純に card_id を元に各テーブルや View を JOIN している

create view view_cards_json as
select cards.id as card_id,
       cards.name as card_name,
       cards.charactor_name,
       cards.rarity as card_rarity,
       cards.type as card_type,
       cards.img_path as card_img_path,
       effects.values as effects,
       event_skills.values as event_skills,
       training_skills.values as training_skills,
       status_gain.values as status_gain
  from cards
  join view_card_effects as effects
    on cards.id = effects.card_id
  join view_card_event_skills as event_skills
    on cards.id = event_skills.card_id
  join view_card_training_skills as training_skills
    on cards.id = training_skills.card_id
  join view_card_training_status_gain as status_gain
    on cards.id = status_gain.card_id;

y4shiro commented 2 years ago

cards_json の型定義

今回はそこまで複雑な JSON ではなく、大幅な構造変更も無いので手動で定義しても良い Supabase は GraphQL の API が存在し、そこから自動生成するのもあり

export type CardType = {
  card_id: number;
  card_name: string;
  charactor_name: string;
  card_rarity: 'R' | 'SR' | 'SSR';
  card_type: 'Speed' | 'Stamina' | 'Power' | 'Guts' | 'Wisdom' | 'Friends' | 'Group';
  card_img_path: string | null;
  effects: Effects[];
  event_skills?: EventSkill[];
  training_skills?: TrainingSkill[];
  status_gains?: StatusGain[];
};

export type Effects = {
  id: number;
  name: string;
  values: EffectValue[];
};

export type EffectValue = {
  level: number;
  value: number;
};

export type EventSkill = {
  id: number;
  name: string;
  skill_pt: number | null;
  img_path: string | null;
};

export type TrainingSkill = {
  id: number;
  name: string;
  skill_pt: number | null;
  img_path: string | null;
};

export type StatusGain = {
  id: number;
  name: string;
  value: number;
};

y4shiro commented 2 years ago

Merge したので本 Issue を Close します

y4shiro / uma-card-deck-tools

Next.js から Supabase にアクセスし、データ読み込み #5

Supabase CLI のセットアップ

supabase-js をインストール

Supabase のアクセスに必要な環境変数を用意

任意のコンポーネントから Supabase のテーブルを読み込み

必要なデータを取得できるように Supabase Client のクエリを組む

Supabase 側で必要な View を作成

`effects` の View

`event_skills`

`training_skills`

`training_status_gain`

全てを合成した `view_cards_json`

cards_json の型定義

y4shiro / uma-card-deck-tools

Next.js から Supabase にアクセスし、データ読み込み #5

Supabase CLI のセットアップ

supabase-js をインストール

Supabase のアクセスに必要な環境変数を用意

任意のコンポーネントから Supabase のテーブルを読み込み

必要なデータを取得できるように Supabase Client のクエリを組む

Supabase 側で必要な View を作成

effects の View

event_skills

training_skills

training_status_gain

全てを合成した view_cards_json

cards_json の型定義

`effects` の View

`event_skills`

`training_skills`

`training_status_gain`

全てを合成した `view_cards_json`