WITH params AS (
SELECT 'account.move'::text AS model_name
),
-- モデル基本
mdl AS (
SELECT im.id, im.model, im.name AS display_name, im.transient, im.write_date
FROM ir_model im
JOIN params p ON im.model = p.model_name
),
-- 所有モジュールとアプリ名
owner AS (
SELECT m.model, imd.module AS module
FROM mdl m
JOIN ir_model_data imd ON imd.model='ir.model' AND imd.res_id=m.id
),
app AS (
SELECT
mod.name AS module,
COALESCE(
-- jsonb なら翻訳値（例: en_US）を取り出す
(CASE WHEN pg_typeof(mod.shortdesc) = 'jsonb'::regtype
THEN mod.shortdesc->>'en_US' END),
-- それでも無ければ jsonb/text を文字列化
(CASE WHEN pg_typeof(mod.shortdesc) = 'jsonb'::regtype
THEN mod.shortdesc::text END),
mod.shortdesc::text, -- text の場合はこちら
mod.name -- 最後のフォールバック
) AS app_name,
mod.application
FROM ir_module_module mod
),
-- フィールド種別カウント
f AS (
SELECT
m.model,
COUNT(*) AS field_count,
COUNT(*) FILTER (WHERE f.ttype='many2one') AS m2o_count,
COUNT(*) FILTER (WHERE f.ttype='one2many') AS o2m_count,
COUNT(*) FILTER (WHERE f.ttype='many2many') AS m2m_count
FROM mdl m
JOIN ir_model_fields f ON f.model_id = m.id
GROUP BY m.model
),
-- 参照される数（IN）／参照する数（OUT）
refs_in AS (
SELECT f.relation AS model, COUNT(*) AS refs_in
FROM ir_model_fields f
JOIN params p ON f.relation = p.model_name
GROUP BY f.relation
),
refs_out AS (
SELECT m.model, COUNT(*) AS refs_out
FROM mdl m
JOIN ir_model_fields f ON f.model_id = m.id
WHERE f.relation IS NOT NULL
GROUP BY m.model
),
-- ビュー情報
views AS (
SELECT
iv.model,
COUNT(*) FILTER (WHERE iv.type='form') AS form_views,
COUNT(*) FILTER (WHERE iv.type='tree') AS tree_views,
COUNT(*) FILTER (WHERE iv.type='kanban') AS kanban_views,
COUNT(*) FILTER (WHERE iv.type='search') AS search_views,
COUNT(*) AS total_views,
MAX(iv.write_date) AS views_write_date
FROM ir_ui_view iv
JOIN params p ON iv.model = p.model_name
GROUP BY iv.model
),
-- act_window数（画面アクション数）
aw_cnt AS (
SELECT aw.res_model AS model, COUNT(*) AS act_window_cnt
FROM ir_act_window aw
JOIN params p ON aw.res_model = p.model_name
GROUP BY aw.res_model
),
-- メニュー露出（最初の3件のメニュー名をサンプル表示） ※ROW_NUMBER+FILTER 版
menus_ranked AS (
SELECT
aw.res_model,
m.id,
(m.name::text) AS menu_name,
ROW_NUMBER() OVER (PARTITION BY aw.res_model ORDER BY m.id) AS rn
FROM ir_ui_menu m
JOIN ir_act_window aw
ON m.action LIKE 'ir.actions.act_window,%'
AND aw.id = split_part(m.action, ',', 2)::int
JOIN params p ON aw.res_model = p.model_name
),
menus AS (
SELECT
mr.res_model AS model,
COUNT(*) AS menu_cnt,
ARRAY_AGG(mr.menu_name ORDER BY mr.id) FILTER (WHERE mr.rn <= 3) AS sample_menus
FROM menus_ranked mr
GROUP BY mr.res_model
),
-- 概算件数（pg_class.reltuples）
size AS (
SELECT m.model, c.reltuples::bigint AS approx_rows
FROM mdl m
LEFT JOIN pg_class c ON c.relname = replace(m.model, '.', '_')
),
-- 最終更新のざっくり（モデル/フィールド/ビューのMAX）
latest AS (
SELECT
p.model_name AS model,
GREATEST(
COALESCE(mdl.write_date, '1970-01-01'::timestamp),
COALESCE((SELECT MAX(f2.write_date) FROM ir_model_fields f2 JOIN mdl m2 ON f2.model_id=m2.id), '1970-01-01'::timestamp),
COALESCE((SELECT MAX(iv2.write_date) FROM ir_ui_view iv2 JOIN params pp ON iv2.model=pp.model_name), '1970-01-01'::timestamp)
) AS last_changed_at
FROM params p
LEFT JOIN mdl ON mdl.model = p.model_name
)
SELECT
m.model,
m.display_name,
o.module,
a.app_name,
a.application AS is_application,
COALESCE(sz.approx_rows,0) AS approx_rows,
COALESCE(fi.field_count,0) AS field_count,
COALESCE(fi.m2o_count,0) AS m2o_count,
COALESCE(fi.o2m_count,0) AS o2m_count,
COALESCE(fi.m2m_count,0) AS m2m_count,
COALESCE(ri.refs_in,0) AS refs_in,
COALESCE(ro.refs_out,0) AS refs_out,
COALESCE(v.form_views,0) AS form_views,
COALESCE(v.tree_views,0) AS tree_views,
COALESCE(v.kanban_views,0) AS kanban_views,
COALESCE(v.search_views,0) AS search_views,
COALESCE(v.total_views,0) AS total_views,
COALESCE(aw.act_window_cnt,0) AS act_window_cnt,
COALESCE(mx.menu_cnt,0) AS menu_cnt,
mx.sample_menus,
lt.last_changed_at
FROM mdl m
LEFT JOIN owner o ON o.model = m.model
LEFT JOIN app a ON a.module = o.module
LEFT JOIN size sz ON sz.model = m.model
LEFT JOIN f fi ON fi.model = m.model
LEFT JOIN refs_in ri ON ri.model = m.model
LEFT JOIN refs_out ro ON ro.model = m.model
LEFT JOIN views v ON v.model = m.model
LEFT JOIN aw_cnt aw ON aw.model = m.model
LEFT JOIN menus mx ON mx.model = m.model
LEFT JOIN latest lt ON lt.model = m.model;
モデル単位で“使われやすさ/カスタマイズ候補度”をスコア化する、pgAdminでそのまま動くSQLを用意しました。
モジュールやapp_nameは一切見ません。各モデルだけを評価します。
評価指標（列に全部出ます）
form_views（フォーム画面の有無/数）
menu_cnt（メニューから開ける回数）
act_window_cnt（アクション数）
has_state（state selection フィールド）
has_chatter（Chatter: message_ids）
refs_in / refs_out（参照される/参照するフィールド数）
approx_rows（概算レコード数）
last_changed_at（最終更新のざっくり）
score（下の重みで合計）
デフォルト重み：FORM=+2, MENU=+2, STATE=+1, CHATTER=+1, REFS_IN=最大+1, VIEWS=最大+0.5
変更したければ weights CTE を編集してください。
WITH
-- 重み（必要なら調整）
weights AS (
SELECT
2.0 AS w_form,
2.0 AS w_menu,
1.0 AS w_state,
1.0 AS w_chatter,
1.0 AS w_refs_in_max, -- 参照される数で最大この点まで加点
0.5 AS w_views_max, -- 総ビュー数で最大この点まで加点
10.0 AS refs_in_k, -- refs_in / k でスケール（上限 w_refs_in_max）
4.0 AS views_k -- total_views / k でスケール（上限 w_views_max）
),
-- 評価対象のモデル（必要なら WHERE で除外条件を足す）
mdl AS (
SELECT im.id, im.model, im.name AS display_name, im.transient, im.write_date
FROM ir_model im
WHERE im.transient = FALSE
-- 技術系を除きたければ↓を有効化
-- AND im.model NOT LIKE 'ir.%'
),
-- ビュー集計
views AS (
SELECT
iv.model,
COUNT(*) FILTER (WHERE iv.type='form') AS form_views,
COUNT(*) FILTER (WHERE iv.type='tree') AS tree_views,
COUNT(*) FILTER (WHERE iv.type='kanban') AS kanban_views,
COUNT(*) FILTER (WHERE iv.type='search') AS search_views,
COUNT(*) AS total_views,
MAX(iv.write_date) AS views_write_date
FROM ir_ui_view iv
GROUP BY iv.model
),
-- act_window と メニュー露出
aw_cnt AS (
SELECT aw.res_model AS model, COUNT(*) AS act_window_cnt
FROM ir_act_window aw
GROUP BY aw.res_model
),
menu_exposure AS (
SELECT aw.res_model AS model, COUNT(*) AS menu_cnt
FROM ir_ui_menu m
JOIN ir_act_window aw
ON m.action LIKE 'ir.actions.act_window,%'
AND aw.id = split_part(m.action, ',', 2)::int
GROUP BY aw.res_model
),
-- ワークフローっぽさ（state selection の有無）
has_state AS (
SELECT im.model, TRUE AS flag
FROM ir_model im
JOIN ir_model_fields f ON f.model_id = im.id
WHERE f.name='state' AND f.ttype='selection'
GROUP BY im.model
),
-- Chatter（mail.thread系）判定：message_ids → mail.message
has_chatter AS (
SELECT im.model, TRUE AS flag
FROM ir_model im
JOIN ir_model_fields f ON f.model_id = im.id
WHERE f.name='message_ids' AND f.relation='mail.message'
GROUP BY im.model
),
-- 参照入出力
refs_out AS (
SELECT im.model, COUNT(*) AS refs_out
FROM ir_model im
JOIN ir_model_fields f ON f.model_id = im.id
WHERE f.relation IS NOT NULL
GROUP BY im.model
),
refs_in AS (
SELECT f.relation AS model, COUNT(*) AS refs_in
FROM ir_model_fields f
WHERE f.relation IS NOT NULL
GROUP BY f.relation
),
-- 概算件数（pg_class.reltuples）
size AS (
SELECT m.model, c.reltuples::bigint AS approx_rows
FROM mdl m
LEFT JOIN pg_class c ON c.relname = replace(m.model, '.', '_')
),
-- 最終更新（モデル/フィールド/ビューのMAX）
latest AS (
SELECT
m.model,
GREATEST(
COALESCE(m.write_date, '1970-01-01'::timestamp),
COALESCE( (SELECT MAX(f2.write_date) FROM ir_model_fields f2 WHERE f2.model_id = m.id), '1970-01-01'::timestamp),
COALESCE( (SELECT MAX(iv2.write_date) FROM ir_ui_view iv2 WHERE iv2.model = m.model), '1970-01-01'::timestamp)
) AS last_changed_at
FROM mdl m
)
SELECT
m.model,
m.display_name,
-- 指標
COALESCE(v.form_views,0) AS form_views,
COALESCE(v.tree_views,0) AS tree_views,
COALESCE(v.kanban_views,0) AS kanban_views,
COALESCE(v.search_views,0) AS search_views,
COALESCE(v.total_views,0) AS total_views,
COALESCE(aw.act_window_cnt,0) AS act_window_cnt,
COALESCE(mx.menu_cnt,0) AS menu_cnt,
COALESCE(st.flag,FALSE) AS has_state,
COALESCE(ch.flag,FALSE) AS has_chatter,
COALESCE(ri.refs_in,0) AS refs_in,
COALESCE(ro.refs_out,0) AS refs_out,
COALESCE(sz.approx_rows,0) AS approx_rows,
lt.last_changed_at,
-- スコア（モデル単体の“カスタマイズ候補度”）
(
-- ブール系
(CASE WHEN COALESCE(v.form_views,0) > 0 THEN (SELECT w_form FROM weights) ELSE 0 END) +
(CASE WHEN COALESCE(mx.menu_cnt,0) > 0 THEN (SELECT w_menu FROM weights) ELSE 0 END) +
(CASE WHEN COALESCE(st.flag,FALSE) THEN (SELECT w_state FROM weights) ELSE 0 END) +
(CASE WHEN COALESCE(ch.flag,FALSE) THEN (SELECT w_chatter FROM weights) ELSE 0 END) +
-- 連続系（上限あり）
LEAST( COALESCE(ri.refs_in,0) / (SELECT refs_in_k FROM weights), (SELECT w_refs_in_max FROM weights) ) +
LEAST( COALESCE(v.total_views,0)/ (SELECT views_k FROM weights), (SELECT w_views_max FROM weights) )
) AS score
FROM mdl m
LEFT JOIN views v ON v.model = m.model
LEFT JOIN aw_cnt aw ON aw.model = m.model
LEFT JOIN menu_exposure mx ON mx.model = m.model
LEFT JOIN has_state st ON st.model = m.model
LEFT JOIN has_chatter ch ON ch.model = m.model
LEFT JOIN refs_in ri ON ri.model = m.model
LEFT JOIN refs_out ro ON ro.model = m.model
LEFT JOIN size sz ON sz.model = m.model
LEFT JOIN latest lt ON lt.model = m.model
-- 必要なら“UI寄りだけ”に絞る（コメント解除）
-- WHERE (COALESCE(v.form_views,0) > 0 OR COALESCE(mx.menu_cnt,0) > 0)
ORDER BY score DESC, m.model
LIMIT 200;
コメントを残す