node.js inner joinについて

Question

投稿に対してコメントをしたユーザー名を表示したいです。
現在投稿に対するコメントは表示することはできています。がユーザー名を表示することができていません。
inner joinを使うのだと思いますが、どこにどう使っていいかが分かっていません。。
postが投稿で、commentがその投稿に対すコメントになります。

router.get("/",(req,res) =>{
  console.log(req.query);
  const postId = req.query.id;
  const query = `SELECT * FROM posts where id = ${postId}`
  connection.query(query, (err,post_rows) =>{
    const query = `SELECT * FROM comments where post_id = ${postId} ORDER BY created_at DESC`;
    // const query = "SELECT * FROM comments INNER JOIN users ON comments.user_id = users.id";
    connection.query(query,(err,comment_rows) =>{
      res.render('comment',{postList: post_rows, commentList: comment_rows});
    });
  });
});

router.post("/", (req,res)=>{
  const userId = req.session.user_id? req.session.user_id: 0; 
  const postId = req.body.id;
  const comment = req.body.comment;
  const createdAt = moment().format('YYYY-MM-DD HH:mm:ss'); 
  const query = 'INSERT INTO comments (user_id, post_id, comment, created_at) VALUES ("' + userId + '",'+'"' + postId + '", ' + '"' + comment + '","' + createdAt + '")';
  connection.query(query,(err,rows) =>{
    res.redirect(`/comment?id=${postId}`);
  });
});

Comments

[yuxki]

sqlの質問でしたら、関連するテーブルの定義（今回だとcommentsとusers?）をcreate文で記載すると回答しやすくなると思います。

[aachyee]

Expressですか？

[PARCO]

yuxkiさん、aachyeeさんありがとうございます！
expressです！！
```
CREATE TABLE `comments` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL,
`post_id` int(11) DEFAULT NULL,
`comment` varchar(255) DEFAULT NULL,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
)

```
CREATE TABLE `users` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(255) NOT NULL DEFAULT '',
`email` varchar(255) NOT NULL DEFAULT '',
`password` varchar(255) NOT NULL DEFAULT '',
`prefecture` varchar(255) DEFAULT NULL,
`createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`updatedAt` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
```
```

[aachyee]

あ、すいません。SQLの問題っぽいですね。退散します＞＜

Answer 1

現在投稿に対するコメントは表示することはできています。がユーザー名を表示することができていません。

const query = "SELECT * FROM comments INNER JOIN users ON comments.user_id = users.id";

これはSQLの書き方の質問ですね。

SQL文はSelect句、From句、Where句をパズルのようにくっつけることができます。
Select句が必須項目なだけで、後はほぼ全て任意です。

From句は参照するテーブルを司ります。
基本的には1個を指定しますが、今回はcommentsテーブルを参照しようとしているようですね。
そして結合を定義しています。

ONキーワードを使ってcommentsテーブルとusersテーブルはひも付きました。
結合という名の通り、これは2つのテーブルが合体した巨大なテーブルです。

質問内容を再定義すると、
「この合体した巨大なテーブルから、どうやって求める値を抜き出せば良いんだ？」
ということになります。

Select句に話を戻しましょう。
データベース名.カラム名という指定を使う事ができます。
comments.user_idと指定すれば、結合前のcommentsテーブルのuser_idカラムを引っ張ってくることになります。
それに列別名を関するASキーワードを付与してユニークな名前を振ってあげるとスムーズでしょう。

usersテーブルからユーザー名を引っ張ってくるなら、
質問文にテーブルの定義はありませんので推測ですがusers.nameでアクセス出来るのでは？
それを踏まえてSQL文を作るとこうなります。

    // バッククォートによるテンプレート文字列は改行が許されるので
    // SQLの長文を作るならこんな風にしたほうが読みやすくなる
    const query = `
      SELECT
        comments.*,
        users.name AS user_name
      FROM comments
        INNER JOIN users ON comments.user_id = users.id
      WHERE comments.post_id = ?
    `;
    connection.query(query, [postId], fn);

こんなもんですかね。

質問文にはWhere句が無いので絞り込みができず、
全部のコメントが押し寄せる感じになってたので、勝手に付け足しました。
これをベースに試行錯誤してみてください。

用途に応じて適切なSQL文を書けることは、
それ一本で仕事になるくらい、大変だし貴重な仕事です。
ゆくゆくはSQL文に関して書籍を買う等、勉強していっても損はないと思います。

---

また、ちょっと横道にそれますが、
重篤なセキュリティ脆弱性があるので対応させてくださいね。

connectionというのはmysql系のモジュールの事でしょうか？
もしそうなら下記の流れはまずいです。

const postId = req.query.id;
SELECT * FROM posts where id = ${postId}

これはSQLインジェクション脆弱性と呼ばれる脆弱性です。
想定されるidパラメータは数値を想定しているのだと思いますが、
特に対策されていなければいかなる文字列も埋め込めそうですね。

もしこれを先輩に触らせると、
SQL文を好き勝手にイジられてDELETE FROM postsコマンドを実行されるでしょう。
そうなればすべての投稿が削除、このWebサイトはpostsを溜め込む事を財産にしているでしょうから一発でお陀仏です。

対策は簡単。
このようにするだけです。

  const query = "SELECT * FROM posts where id = ?"
  connection.query(query, [postId], fn);

RMDBSを触る上で、SQLインジェクションは絶対に通る道なので、
値の埋め込みに関してはほぼ全てのライブラリでも対策が用意されています。

POSTの方も同様です。
書き方はちょっと変わりますが、Insertの場合の書き方も紹介されているので探して変更してみてくださいね。
そこ引き出せるように頭の片隅に覚えておいてくださいね。

参考: READMEのEscaping query values

Comments

[PARCO]

うう、とても詳しく説明していただきありがとうございます！！！
やってみます！！

Answer 2

SELECT * FROM comments INNER JOIN users ON comments.user_id = users.id"

これで結合できてるなら、SELECT句にusersテーブルとやらからユーザー名のカラムを並べるだけかと思います。