以下の3つのテーブルがあるとします。
users
-------------
id(pk) | name
-------------
1 | bob
2 | mary
user_defined_columns
------------------------
id(pk) | name | enabled
------------------------
1 | Mail | 1
2 | Tel | 1
3 | | 0
column_values
----------------------------------------------------------
id(pk) | user_id(fk) | user_defined_column_id(fk) | value
----------------------------------------------------------
1 | 1 | 1 | bob@example.com
2 | 1 | 2 | 000-111-222
3 | 2 | 1 | mary@example.com
このときにユーザのリストを作成しようと思っています。
しかし、以下のようにuser_defined_columnsに定義された値を
ユーザリストのカラムとして表示したいです。
results
-----------------------------------------------
id(pk) | name | Mail | Tel
-----------------------------------------------
1 | bob | bob@example.com | 000-111-222
2 | mary | mary@example.com |
この場合はどのようなSQLを書くとよいのでしょうか?
このような場合はやはりアプリケーション側で対処すべきでしょうか?
SQLを工夫してお望みの結果は得られると思いますが、おそらく電話番号を複数持ったりFAX番号を管理したりということも考えているようなDB設計であると見受けられます。
このような項目拡張があった場合でもDBの変更が必要ないような工夫をされたのだと思います。
このようなテーブル構成の場合は、無理してSQL一本で望み通りの結果を得るよりも、column_valuesテーブルとusersとuser_defined_columnsを素直にjoinして得られた結果をアプリケーション側で整形した方が良いと思います。
u.*,
(SELECT
value
FROM
column_values v LEFT JOIN user_defined_columns c
ON v.user_defined_column_id = c.id
WHERE
c.id = 1
and u.id = v.user_id
) as Mail,
(SELECT
value
FROM
column_values v LEFT JOIN user_defined_columns c
ON v.user_defined_column_id = c.id
WHERE
c.id = 2
and u.id = v.user_id
) as Tel
FROM
users u
このようなものは考えてみましたが、しっくりきていません。