PHP連想配列をSQLの「AS ~」に用いたい

Question

前提・実現したいこと

PHPで連想配列を define して、SQLのマスタテーブルの役割として用いています。

この連想配列をもとに、SQLで select する際に変換したいです。

該当のソースコード

マスタテーブルは次のものです。
投稿タイプとして「ニュース」や「ブログ」があるという意味になります。

define( 'master_types', [ 1=>'news', 2=>'blog' ] );

投稿テーブルは次のものです。

CREATE TABLE posts
    (`ID` int, `type_id` int, `title` varchar(100), `author_id` int)
;
INSERT INTO posts
    (`ID`, `type_id`, `title`, `author_id`)
VALUES
    (1, 2, 'ブログ1つ目', 9),
    (2, 1, 'ニュース1つ目', 4),
    (3, 2, 'ブログ2つ目', 2)
;

そして上記投稿テーブルからPHPで select するとこう書きます。

$query = "
select
ID
,type_id AS type_name

from
posts;";

やりたいのは、「AS type_name」とあるように、「type_id」を「type_name」に変換することです。

つまり次の取得が目的です。

ID	type_name
1	blog
2	news
3	blog

現状

現状はSQLの変換がわからないので、

ID	type_id
1	2
2	1
3	2

という風にtype_idのままで取得し、その後にPHPでtype_nameへと変換している状況です。

試したこと

今回はSQL側で変換したいので以下 case when になるのかと思ったのですが、

$query = "
select
ID
,case when type_id=1 then 'news' when type_id=2 then 'blog' end as type_name

from
posts;";

しかし、define しているものをわざわざ then 'news' とか then 'blog' のように書かないといけません。

つまり define を変更した際に、define だけでなく、この $query をも変更しなければならず、define のメリットが活かしきれない点に不満を覚えました。

そこで何かいい方法がありそうに思って質問させて頂いた次第です。
宜しくお願い致します。

###環境
PHP 7.3
MySQL 5.5

Comments

[退会済みユーザー]

mysqlなのか別なのか明らかにした方が回答が付きやすいし、データベースシステムにちなんだ質問タグをつけると回答が付きやすいよ。

[mokeame]

すみません、ありがとうございます。タグや環境を追加致しました。

[takasima20]

type_name のマスターテーブルを作ればいいんじゃね?

Answer 1

define を変更した際に、define だけでなく、この $query をも変更しなければならず、define のメリットが活かしきれない点に不満を覚えました。

PHPでもSQLでも同一の定義を参照可能なように設計していないのですから当然です。
対応するなら、先ずはdefineをSQLで参照できる形（テーブル化とかストアド化とか）に置き換えて下さい。

Comments

[mokeame]

テーブル化というのは、postsが１億件あったら、１億件のjoinが発生しますよね？それってどうなのでしょうか？普通なんですか？なんとなく遅くなりそうなのでdefineしたのですが、テーブル化の方がいいでしょうか？

ストアド化というのはわからないので、よかったら具体的に教えてくださいませんでしょうか。

[sazi]

テーブルの内容を取得して、グローバル変数にするとかクラスにするなどで、joinする必要は無いですね。
尤も、defineの件数がそんなに多く無ければjoinしても大差は無いと思いますが。

ストアド化というのは、テーブルではなく同等の結果（定数の一覧）を返却するストアドにするという事です。viewでも良いかもしれません。

> なんとなく遅くなりそうなのでdefineした
明確な根拠を持った設計を心掛けて下さい。

[mokeame]

ありがとうございます。ストアド調べましたが使い方がむずめですね…

[退会済みユーザー]

enum が候補から外されてるのは意図的？

[mokeame]

偉い方ばかりですね。

[sazi]

@te2jiさん
ENUMは値をインデックスに変換するのが本来の用途なので、後付けで採用するにはあまり適していないと思います。

[退会済みユーザー]

継続的な更新を考慮したってことですね。
理解できました。ありがとう。