言語別レコードに備えたテーブル設計とPHP取得コード

Question

質問事項

投稿に言語別タイトルを実装しています。

以下PHPで、言語別タイトルが取得できるかどうか知りたいです。

$his_lang_title = $titles[$key]; // ← ここでズレないかを特に懸念しています

とあるように、このPHPでは$titlesと$lang_idsが言語ごとに同じ位置に来ないと適切に取得できないと思うのですが、以下のSQLのSELECTは、それを同じ位置に取得できるでしょうか？

実際に何度試してもできましたが、例えばORDER BYを書かないと取得の順番が変化するという稀なケースもございますから、そういった稀なケースがもしこのSQLでも起こるのであれば、PHPでの取得もズレてしまうことになりますので懸念しています。

・同じ順番で取得できるから大丈夫
・稀にズレるからこうした方がいい
といったアドバイスを頂戴したく思っています。

SQL

テーブル設計およびレコード取得のSQLはこうなっています。

# 言語マスタ
CREATE TABLE IF NOT EXISTS m_langs (
  `ID` INT(2) UNSIGNED,
  `lang` VARCHAR(10) NOT NULL,
  PRIMARY KEY (`ID`)
);
INSERT INTO m_langs (`ID`,`lang`)
VALUES (1,'ja'),(2,'en'),(3,'zh'),(99,'xx'); # 投稿時に言語指定がなければ99が入る

# 投稿
CREATE TABLE IF NOT EXISTS d_posts (
  `ID` INT(10) UNSIGNED AUTO_INCREMENT,
  `user_ID` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
);
INSERT INTO d_posts (`user_ID`)
VALUES (100),(200),(300);

# 言語別タイトル
CREATE TABLE IF NOT EXISTS d_post_titles (
  `post_ID` INT(10) UNSIGNED,
  `lang_ID` INT(2) UNSIGNED NOT NULL,
  `title` VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, 
  CONSTRAINT foreign_key_post_ID FOREIGN KEY (`post_ID`) REFERENCES d_posts(`ID`),
  CONSTRAINT foreign_key_lang_ID FOREIGN KEY (`lang_ID`) REFERENCES m_langs(`ID`)
);
INSERT INTO d_post_titles (`post_ID`,`lang_ID`,`title`)
VALUES
(1,1,'りんご'), (1,2,'apple'), (1,3,'苹果'),
(2,1,'ばなな'), (2,2,'banana'),
(3,99,'mandarine'); 
    

SELECT
  posts.ID AS post_id,
  GROUP_CONCAT(post_titles.title separator ',') as titles, 
  GROUP_CONCAT(post_titles.lang_id separator ',') as lang_ids 
		
FROM
  d_posts posts
  LEFT JOIN d_post_titles post_titles ON post_titles.post_ID=posts.ID

WHERE 
  posts.ID = 1

GROUP BY
  posts.ID

PHP

言語別タイトルを取得するPHPはこちらです。

/*
上記SQLの結果です
このように「りんご」の位置に「1」が来て「apple」の位置に「2」が来れば
$his_lang_title = $titles[$key];
で取得できるのですが…
この順番は上記SQLでズレることはないでしょうか？
*/
$rows = array( array(
  "post_id"  => "1",
  "titles"   => "りんご,apple,苹果"
  "lang_ids" => "1,2,3"
));
$row = $rows[0];

/*
当該ユーザの言語($his_lang_id)に応じたタイトルを取得します
*/
// 当該ユーザの言語
$his_lang_id = 1;
// 言語別情報
$titles   = explode( ',', $row['titles'] );
$lang_ids = explode( ',', $row['lang_ids'] );
// 表示したい言語ID
$print_lang_id = null;
if( in_array( (string)$his_lang_id, $lang_ids, true ) ){
	$print_lang_id = $his_lang_id; // 当該ユーザの言語があればそれを出力
}else{
	if( in_array( '2', $lang_ids, true ) ){
		$print_lang_id = 2; // 当該ユーザの言語がなく、2(en) があればそれを出力
	}
}
// 表示したいタイトル
$key = array_search( $print_lang_id, $lang_ids ) ?? 0; // 当該ユーザの言語がなく、2(en) もなければ最初のもの(0個目)を出力
$his_lang_title = $titles[$key]; // ← ここでズレないかを特に懸念しています
$result['当該ユーザの言語($his_lang_id)に応じたタイトル'] = $his_lang_title;

補足情報（FW/ツールのバージョンなど）

環境は
Z.comサーバー Zeroプラン
MySQL 5.7.2
PHP 7.3
です。

長文で素人コードにつき見にくい点など多々あるかと思いますが、宜しくお願い致します。

Answer 1

ご提示いただいたSQLでは、ご懸念通り、順序が保証されません。

GROUP_CONCAT()では、ソートが指定できますので、順序が保証されるように、以下のようにされると良いかと思います。

SELECT
  posts.ID AS post_id,
  GROUP_CONCAT(post_titles.title
    ORDER BY post_titles.lang_id
    separator ',') as titles, 
  GROUP_CONCAT(post_titles.lang_id
    ORDER BY post_titles.lang_id
    separator ',') as lang_ids 
(以下略)

GROUP_CONCAT

Comments

[ningen]

やはり保証されませんでしたか、気がついてよかったです。適切な方法をどうもありがとうございました。