フィールド
-- 抽出モデルとベース言語を指定。全モデルは target_model を NULL に
WITH params AS (
SELECT
'res.partner'::text AS target_model,
'en_US'::text AS base_lang
),
-- public スキーマ内の各テーブルの PK 列を取得
pk_by_table AS (
SELECT
c.relname AS model_table, -- 物理テーブル名
array_agg(a.attname ORDER BY a.attnum) AS pk_columns -- 主キー列配列
FROM pg_index i
JOIN pg_class c ON c.oid = i.indrelid
JOIN pg_namespace n ON n.oid = c.relnamespace AND n.nspname = 'public'
JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = ANY(i.indkey)
WHERE i.indisprimary
GROUP BY c.relname
)
SELECT
m.model AS model,
replace(m.model, '.', '_') AS model_table,
f.name AS field_name,
f.ttype AS ttype,
/* ★ label_i18n：既に JSON オブジェクトならそのまま、その他は base_lang で包む */
COALESCE(
CASE
WHEN pg_typeof(f.field_description)::text IN ('json','jsonb')
AND jsonb_typeof(f.field_description::jsonb) = 'object'
THEN jsonb_strip_nulls(f.field_description::jsonb)
END,
CASE
WHEN pg_typeof(f.field_description)::text IN ('json','jsonb')
AND jsonb_typeof(f.field_description::jsonb) = 'string'
THEN jsonb_build_object(p.base_lang, btrim(f.field_description::text, '"'))
ELSE CASE
WHEN f.field_description IS NOT NULL AND f.field_description::text <> ''
THEN jsonb_build_object(p.base_lang, f.field_description::text)
ELSE '{}'::jsonb
END
END
) AS label_i18n,
'generated'::text AS code_status,
/* ★ notes（helpをTEXTで採用）：json文字列→中身、jsonオブジェクト→base_langの値、他はそのまま */
COALESCE(
CASE
WHEN pg_typeof(f.help)::text IN ('json','jsonb')
AND jsonb_typeof(f.help::jsonb) = 'string' THEN btrim(f.help::text, '"')
WHEN pg_typeof(f.help)::text IN ('json','jsonb')
AND jsonb_typeof(f.help::jsonb) = 'object' THEN f.help::jsonb ->> p.base_lang
ELSE f.help::text
END,
NULL
) AS notes,
CASE f.state WHEN 'manual' THEN 'studio' ELSE 'code' END AS origin,
-- ★ デフォルト非表示（ポータルINSERT時にそのまま使える）
'invisible'::text AS show_invisible,
-- ★ ここから PK 情報
COALESCE(pk.pk_columns, ARRAY[]::text[]) AS "テーブルPK列配列",
CASE WHEN pk.pk_columns IS NOT NULL
AND f.name = ANY(pk.pk_columns) THEN true ELSE false
END AS "このフィールドはPKか",
-- 参考：日本語表示用（そのまま INSERT も可能）
m.model AS "モデル技術名",
replace(m.model, '.', '_') AS "モデル物理名",
f.name AS "フィールド技術名",
CASE f.ttype
WHEN 'char' THEN '文字列'
WHEN 'text' THEN 'テキスト'
WHEN 'integer' THEN '整数'
WHEN 'float' THEN '小数'
WHEN 'boolean' THEN '真偽'
WHEN 'date' THEN '日付'
WHEN 'datetime' THEN '日時'
WHEN 'json' THEN 'JSON'
WHEN 'many2one' THEN '参照'
WHEN 'one2many' THEN '複数参照'
WHEN 'many2many' THEN '多対多'
ELSE '文字列'
END AS "データ型"
FROM ir_model m
JOIN ir_model_fields f ON f.model_id = m.id
LEFT JOIN pk_by_table pk ON pk.model_table = replace(m.model, '.', '_')
CROSS JOIN params p
WHERE (p.target_model IS NULL OR m.model = p.target_model)
ORDER BY m.model, f.name;
show_invisibleで表示されるフィールドを制御
連携テーブルの抽出クエリ
-- ★ 抽出対象モデル。全モデルは NULL に
WITH params AS (
SELECT
'res.partner'::text AS target_model,
'ja_JP'::text AS target_lang, -- ← 取りたい言語
'en_US'::text AS base_lang -- ← フォールバック基準
),
-- 対象モデル（技術名→物理名）
models AS (
SELECT m.id AS model_id, m.model AS local_model, replace(m.model,'.','_') AS local_table
FROM ir_model m
CROSS JOIN params p
WHERE p.target_model IS NULL OR m.model = p.target_model
),
-- 全モデル（参照先解決用）
all_models AS (
SELECT m.id AS model_id, m.model AS model_tech, replace(m.model,'.','_') AS model_table
FROM ir_model m
),
-- public スキーマ内テーブルの PK 列
pk_by_table AS (
SELECT
c.relname AS table_name,
array_agg(a.attname ORDER BY a.attnum) AS pk_columns
FROM pg_index i
JOIN pg_class c ON c.oid = i.indrelid
JOIN pg_namespace n ON n.oid = c.relnamespace AND n.nspname = 'public'
JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = ANY(i.indkey)
WHERE i.indisprimary
GROUP BY c.relname
),
/* =========================================================
フィールド名 → ラベル辞書（model_id, field_name 単位）
- field_description が json/jsonb の object: そのまま言語キーで参照
- json/jsonb の string: テキスト化（btrim('"')）
- それ以外: text 化
target_lang → base_lang → 'en_US' → 技術名 の順でフォールバック
========================================================= */
field_labels AS (
SELECT
f.model_id,
f.name AS field_name,
CASE
WHEN pg_typeof(f.field_description)::text IN ('json','jsonb')
AND jsonb_typeof(f.field_description::jsonb) = 'string'
THEN btrim(f.field_description::text, '"')
ELSE f.field_description::text
END AS label_txt,
CASE
WHEN pg_typeof(f.field_description)::text IN ('json','jsonb')
AND jsonb_typeof(f.field_description::jsonb) = 'object'
THEN f.field_description::jsonb
ELSE NULL
END AS label_obj
FROM ir_model_fields f
),
/* ===== many2one ===== local.m2o_col = remote.id */
rels_m2o AS (
SELECT
'many2one' AS rel_kind,
f.ttype AS local_ttype, -- ★ 型 追加
mo.local_model AS local_model,
mo.local_table AS local_table,
f.name AS local_field,
-- 日本語ラベル（local_field）
COALESCE(
(SELECT fl.label_obj ->> p.target_lang FROM field_labels fl WHERE fl.model_id = mo.model_id AND fl.field_name = f.name),
(SELECT fl.label_obj ->> p.base_lang FROM field_labels fl WHERE fl.model_id = mo.model_id AND fl.field_name = f.name),
(SELECT fl.label_obj ->> 'en_US' FROM field_labels fl WHERE fl.model_id = mo.model_id AND fl.field_name = f.name),
(SELECT fl.label_txt FROM field_labels fl WHERE fl.model_id = mo.model_id AND fl.field_name = f.name),
f.name
) AS local_field_label_ja,
cm.model_tech AS remote_model,
cm.model_table AS remote_table,
COALESCE(pk_local.pk_columns, ARRAY['id']) AS local_pk_columns,
COALESCE(pk_remote.pk_columns, ARRAY['id']) AS remote_pk_columns,
ARRAY[f.name] AS join_local_cols,
ARRAY['id'] AS join_remote_cols,
(mo.local_model = cm.model_tech) AS is_self_reference,
-- ★ リモート側の表示用フィールドと日本語ラベル
remdisp.field_name AS remote_display_field,
COALESCE(
(SELECT fl.label_obj ->> p.target_lang FROM field_labels fl WHERE fl.model_id = cm.model_id AND fl.field_name = remdisp.field_name),
(SELECT fl.label_obj ->> p.base_lang FROM field_labels fl WHERE fl.model_id = cm.model_id AND fl.field_name = remdisp.field_name),
(SELECT fl.label_obj ->> 'en_US' FROM field_labels fl WHERE fl.model_id = cm.model_id AND fl.field_name = remdisp.field_name),
(SELECT fl.label_txt FROM field_labels fl WHERE fl.model_id = cm.model_id AND fl.field_name = remdisp.field_name),
CASE WHEN remdisp.field_name = 'id' THEN 'ID' ELSE remdisp.field_name END
) AS remote_display_label_ja,
NULL::text AS inverse_field,
NULL::text AS inverse_field_label_ja,
NULL::text AS junction_table,
NULL::text AS junction_col_local,
NULL::text AS junction_col_remote
FROM models mo
JOIN ir_model_fields f ON f.model_id = mo.model_id AND f.ttype='many2one'
LEFT JOIN all_models cm ON cm.model_tech = f.relation
LEFT JOIN pk_by_table pk_local ON pk_local.table_name = mo.local_table
LEFT JOIN pk_by_table pk_remote ON pk_remote.table_name = cm.model_table
-- ★ 相手モデルの表示用フィールドを決定（name → display_name → PK）
LEFT JOIN LATERAL (
SELECT
CASE
WHEN EXISTS (SELECT 1 FROM ir_model_fields rf WHERE rf.model_id = cm.model_id AND rf.name='name') THEN 'name'
WHEN EXISTS (SELECT 1 FROM ir_model_fields rf WHERE rf.model_id = cm.model_id AND rf.name='display_name') THEN 'display_name'
ELSE COALESCE(pk_remote.pk_columns[1], 'id')
END AS field_name
) remdisp ON TRUE
CROSS JOIN params p
),
/* ===== one2many ===== local.id = remote.inverse_m2o */
rels_o2m AS (
SELECT
'one2many' AS rel_kind,
f.ttype AS local_ttype, -- ★ 型 追加
mo.local_model AS local_model,
mo.local_table AS local_table,
f.name AS local_field,
-- 日本語ラベル（local_field）
COALESCE(
(SELECT fl.label_obj ->> p.target_lang FROM field_labels fl WHERE fl.model_id = mo.model_id AND fl.field_name = f.name),
(SELECT fl.label_obj ->> p.base_lang FROM field_labels fl WHERE fl.model_id = mo.model_id AND fl.field_name = f.name),
(SELECT fl.label_obj ->> 'en_US' FROM field_labels fl WHERE fl.model_id = mo.model_id AND fl.field_name = f.name),
(SELECT fl.label_txt FROM field_labels fl WHERE fl.model_id = mo.model_id AND fl.field_name = f.name),
f.name
) AS local_field_label_ja,
cm.model_tech AS remote_model,
cm.model_table AS remote_table,
COALESCE(pk_local.pk_columns, ARRAY['id']) AS local_pk_columns,
COALESCE(pk_remote.pk_columns, ARRAY['id']) AS remote_pk_columns,
COALESCE(pk_local.pk_columns, ARRAY['id']) AS join_local_cols,
ARRAY[COALESCE(inv.inv_name, 'id')] AS join_remote_cols,
(mo.local_model = cm.model_tech) AS is_self_reference,
-- ★ 相手モデルの表示用フィールドと日本語ラベル
remdisp.field_name AS remote_display_field,
COALESCE(
(SELECT fl.label_obj ->> p.target_lang FROM field_labels fl WHERE fl.model_id = cm.model_id AND fl.field_name = remdisp.field_name),
(SELECT fl.label_obj ->> p.base_lang FROM field_labels fl WHERE fl.model_id = cm.model_id AND fl.field_name = remdisp.field_name),
(SELECT fl.label_obj ->> 'en_US' FROM field_labels fl WHERE fl.model_id = cm.model_id AND fl.field_name = remdisp.field_name),
(SELECT fl.label_txt FROM field_labels fl WHERE fl.model_id = cm.model_id AND fl.field_name = remdisp.field_name),
CASE WHEN remdisp.field_name = 'id' THEN 'ID' ELSE remdisp.field_name END
) AS remote_display_label_ja,
inv.inv_name AS inverse_field,
-- 日本語ラベル（inverse_field）
COALESCE(
(SELECT fl.label_obj ->> p.target_lang FROM field_labels fl WHERE fl.model_id = inv.inv_model_id AND fl.field_name = inv.inv_name),
(SELECT fl.label_obj ->> p.base_lang FROM field_labels fl WHERE fl.model_id = inv.inv_model_id AND fl.field_name = inv.inv_name),
(SELECT fl.label_obj ->> 'en_US' FROM field_labels fl WHERE fl.model_id = inv.inv_model_id AND fl.field_name = inv.inv_name),
(SELECT fl.label_txt FROM field_labels fl WHERE fl.model_id = inv.inv_model_id AND fl.field_name = inv.inv_name),
inv.inv_name
) AS inverse_field_label_ja,
NULL::text AS junction_table,
NULL::text AS junction_col_local,
NULL::text AS junction_col_remote
FROM models mo
JOIN ir_model_fields f ON f.model_id = mo.model_id AND f.ttype='one2many'
LEFT JOIN all_models cm ON cm.model_tech = f.relation
LEFT JOIN pk_by_table pk_local ON pk_local.table_name = mo.local_table
LEFT JOIN pk_by_table pk_remote ON pk_remote.table_name = cm.model_table
-- 逆参照列（子の many2one）＋その model_id
LEFT JOIN LATERAL (
SELECT cf.name AS inv_name, cf.model_id AS inv_model_id
FROM ir_model_fields cf
WHERE cf.model_id = cm.model_id
AND cf.ttype = 'many2one'
AND cf.relation = mo.local_model
ORDER BY
CASE WHEN f.relation_field IS NOT NULL AND cf.name = f.relation_field THEN 0 ELSE 1 END,
CASE WHEN cf.name = replace(mo.local_model,'.','_') || '_id' THEN 0 ELSE 1 END,
cf.id
LIMIT 1
) AS inv ON TRUE
-- 相手モデルの表示用フィールド
LEFT JOIN LATERAL (
SELECT
CASE
WHEN EXISTS (SELECT 1 FROM ir_model_fields rf WHERE rf.model_id = cm.model_id AND rf.name='name') THEN 'name'
WHEN EXISTS (SELECT 1 FROM ir_model_fields rf WHERE rf.model_id = cm.model_id AND rf.name='display_name') THEN 'display_name'
ELSE COALESCE(pk_remote.pk_columns[1], 'id')
END AS field_name
) remdisp ON TRUE
CROSS JOIN params p
),
/* ===== many2many ===== junction.col_local = local.id, junction.col_remote = remote.id */
rels_m2m AS (
SELECT
'many2many' AS rel_kind,
f.ttype AS local_ttype, -- ★ 型 追加
mo.local_model AS local_model,
mo.local_table AS local_table,
f.name AS local_field,
-- 日本語ラベル（local_field）
COALESCE(
(SELECT fl.label_obj ->> p.target_lang FROM field_labels fl WHERE fl.model_id = mo.model_id AND fl.field_name = f.name),
(SELECT fl.label_obj ->> p.base_lang FROM field_labels fl WHERE fl.model_id = mo.model_id AND fl.field_name = f.name),
(SELECT fl.label_obj ->> 'en_US' FROM field_labels fl WHERE fl.model_id = mo.model_id AND fl.field_name = f.name),
(SELECT fl.label_txt FROM field_labels fl WHERE fl.model_id = mo.model_id AND fl.field_name = f.name),
f.name
) AS local_field_label_ja,
cm.model_tech AS remote_model,
cm.model_table AS remote_table,
COALESCE(pk_local.pk_columns, ARRAY['id']) AS local_pk_columns,
COALESCE(pk_remote.pk_columns, ARRAY['id']) AS remote_pk_columns,
COALESCE(pk_local.pk_columns, ARRAY['id']) AS join_local_cols,
COALESCE(pk_remote.pk_columns, ARRAY['id']) AS join_remote_cols,
(mo.local_model = cm.model_tech) AS is_self_reference,
-- ★ 相手モデルの表示用フィールドと日本語ラベル
remdisp.field_name AS remote_display_field,
COALESCE(
(SELECT fl.label_obj ->> p.target_lang FROM field_labels fl WHERE fl.model_id = cm.model_id AND fl.field_name = remdisp.field_name),
(SELECT fl.label_obj ->> p.base_lang FROM field_labels fl WHERE fl.model_id = cm.model_id AND fl.field_name = remdisp.field_name),
(SELECT fl.label_obj ->> 'en_US' FROM field_labels fl WHERE fl.model_id = cm.model_id AND fl.field_name = remdisp.field_name),
(SELECT fl.label_txt FROM field_labels fl WHERE fl.model_id = cm.model_id AND fl.field_name = remdisp.field_name),
CASE WHEN remdisp.field_name = 'id' THEN 'ID' ELSE remdisp.field_name END
) AS remote_display_label_ja,
NULL::text AS inverse_field,
NULL::text AS inverse_field_label_ja,
NULLIF(f.relation_table,'') AS junction_table,
NULLIF(f.column1,'') AS junction_col_local,
NULLIF(f.column2,'') AS junction_col_remote
FROM models mo
JOIN ir_model_fields f ON f.model_id = mo.model_id AND f.ttype='many2many'
LEFT JOIN all_models cm ON cm.model_tech = f.relation
LEFT JOIN pk_by_table pk_local ON pk_local.table_name = mo.local_table
LEFT JOIN pk_by_table pk_remote ON pk_remote.table_name = cm.model_table
-- 相手モデルの表示用フィールド
LEFT JOIN LATERAL (
SELECT
CASE
WHEN EXISTS (SELECT 1 FROM ir_model_fields rf WHERE rf.model_id = cm.model_id AND rf.name='name') THEN 'name'
WHEN EXISTS (SELECT 1 FROM ir_model_fields rf WHERE rf.model_id = cm.model_id AND rf.name='display_name') THEN 'display_name'
ELSE COALESCE(pk_remote.pk_columns[1], 'id')
END AS field_name
) remdisp ON TRUE
CROSS JOIN params p
),
all_rels AS (
SELECT * FROM rels_m2o
UNION ALL
SELECT * FROM rels_o2m
UNION ALL
SELECT * FROM rels_m2m
)
SELECT
-- 分類（自己参照は m2o/o2m のみ）
CASE
WHEN rel_kind IN ('many2one','one2many') AND is_self_reference THEN '自己参照（親子関係）'
WHEN rel_kind = 'many2one' THEN '多対一（参照）'
WHEN rel_kind = 'one2many' THEN '一対多（子テーブル）'
WHEN rel_kind = 'many2many' THEN '多対多（中間テーブル）'
END AS 分類,
rel_kind AS rel_kind_raw,
local_ttype, -- ★ 追加：型
local_model, local_table,
local_field, local_field_label_ja,
remote_model, remote_table,
-- ★ 追加：リモートの表示用フィールドと日本語ラベル
remote_display_field,
remote_display_label_ja,
inverse_field, inverse_field_label_ja,
join_local_cols, join_remote_cols,
junction_table, junction_col_local, junction_col_remote,
is_self_reference
FROM all_rels
ORDER BY 分類, local_model, rel_kind_raw, local_field;
このテーブル情報を基に連携用途をOpenAIに推測させる
コメントを残す