はじめに 今回は、自作している学習用チャットアプリで中間テーブルを実装したところ、かなり詰まったので、中間テーブルへの登録方法を備忘録として残そうと思います。 同じように悩んでいる方がいたら参考にしてみてください。 アプリ概要 ログインユーザーが他の登録ユーザーを選択してチャットを開始するという、DMのようなものが主機能のアプリです。 リレーション usersテーブルとroomsテーブルが多対多の関係性のため、N+1問題が懸念されるので、中間テーブルとしてroom_usersテーブルを作成しました。 環境 Spring2.5.5 gradle MyBatis2.2.0 MySQL 中間テーブルへのinsertを実装 今回の実装では、 ①チャットルーム作成時に、roomsテーブルにチャットルーム情報を登録する ②作成者のユーザーIDと作成者が選択したユーザーのユーザーID、作成したチャットルームのIDを中間テーブルに登録する 上記2つの処理が必要です。 ということで実装内容を解説します。 entity MUser.java @Data public class MUser { private int id; private String name; private String email; private String password; private String passwordConfirmation; private String role; @DateTimeFormat(pattern = "yyyy_MM_dd HH:mm:ss") private LocalDateTime createdAt; @DateTimeFormat(pattern = "yyyy_MM_dd HH:mm:ss") private LocalDateTime updatedAt; } MRoom.java @Data public class MRoom { private int id; private String roomName; @DateTimeFormat(pattern = "yyyy_MM_dd HH:mm:ss") private LocalDateTime createdAt; } TRoomUser.java @Data public class TRoomUser { private int id; private int roomId; private int currentUserId; private int userId; @DateTimeFormat(pattern = "yyyy_MM_dd HH:mm:ss") private LocalDateTime createdAt; } entityクラスのポイントは中間テーブルのTRoomUserクラスです。 roomsテーブルのIDにroomId、チャットルーム作成者のIDをcurrentUserIdに、選択されたユーザーのIDをuserIdとして定義しています。 RoomMapper RoomMapper.java @Mapper public interface RoomMapper { /**チャットルーム登録*/ public int insertOneRoom(MRoom room); } RoomMapper.xml <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <!-- Mapperとxmlのマッピング --> <mapper namespace="com.example.demo.repository.RoomMapper"> <!-- マッピング定義(rooms) --> <resultMap type="com.example.demo.entity.MRoom" id="room"> <id column="id" property="id"></id> <result column="room_name" property="roomName"></result> <result column="created_at" property="createdAt"></result> <collection property="roomUserList" resultMap="roomUser"></collection> </resultMap> <!-- チャットルーム登録 --> <insert id="insertOneRoom"> insert into rooms ( id, room_name, created_at ) values ( #{id,jdbcType=INTEGER}, #{roomName,jdbcType=VARCHAR}, #{createdAt,jdbcType=TIMESTAMP} ) <selectKey resultType="int" keyProperty="id" order="AFTER"> select @@IDENTITY </selectKey> </insert> </mapper> まず①の処理ですが、roomsテーブルへの登録なので単純にinsert文を作成するだけで大丈夫です。 また、selectKeyタグで主キーであるidを取得していますが、後ほど使用するために取得しています。 RoomUserMapper RoomUserMapper.java @Mapper public interface RoomUserMapper { public int insertRoomUser(TRoomUser roomUser); } RoomUserMapper.xml <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <!-- Mapperとxmlのマッピング --> <mapper namespace="com.example.demo.repository.RoomUserMapper"> <!-- マッピング定義(room_user) --> <resultMap type="com.example.demo.entity.TRoomUser" id="roomUser"> <id column="id" property="id"></id> <result column="room_id" property="roomId"></result> <result column="current_user_id" property="currentUserId"></result> <result column="created_at" property="createdAt"></result> <result column="user_id" property="userId"></result> </resultMap> <!-- room_user登録 --> <insert id="insertRoomUser"> insert into room_users ( id, room_id, current_user_id, created_at, user_id ) values ( #{id,jdbcType=INTEGER}, #{roomId,jdbcType=INTEGER}, #{currentUserId,jdbcType=INTEGER}, #{createdAt,jdbcType=TIMESTAMP}, #{userId,jdbcType=INTEGER} ) </insert> </mapper> 中間テーブルもMapperに関してはroomsテーブルと同様、登録の処理を記述するだけでOKです。 RoomService RoomService.java public interface RoomService { /**チャットルーム登録*/ public void insertRoom(MRoom room, RoomForm form); } RoomServiceImpl.java @Service public class RoomServiceImpl implements RoomService { @Autowired private RoomMapper mapper; /** *チャットルーム登録 */ @Transactional @Override public void insertRoom(MRoom room, RoomForm form) { //チャットルーム名取得 room.setRoomName(form.getRoomName()); //現在時刻の取得 LocalDateTime now = LocalDateTime.now(); room.setCreatedAt(now); //チャットルーム登録 mapper.insertOneRoom(room); } } Serviceクラスは一つにまとめることもできますが、Mapperと同じメソッド名になるとわかりにくくなるため、自分の場合は、インターフェースと実装クラスで分けています。 ロジックですが、チャットルーム名はフォームに入力された値を取得して、それをセットするだけです。 作成日時も現在時刻を取得し、それをセットするだけで簡単に実装できます。 最後にroomsテーブルに登録するためRoomMapperインターフェースの登録メソッドを呼び出して登録処理を行います。 Form RoomForm.java @Data public class RoomForm { @NotBlank private String roomName; private int userId; } チャットルーム登録画面のフォームクラスです。 自分の実装はプルダウンから、選択したユーザーとチャットができる仕様のため、プルダウンに入力されるユーザーの情報を取得するためuserIdを定義しています。 RoomUserService RoomUserService public interface RoomUserService { /**room_user登録*/ public void registRoomUser(RoomForm form, TRoomUser roomUser, @AuthenticationPrincipal UserDetailServiceImpll loginUser); } RoomUserServiceImpl.java @Service public class RoomUserServiceImpl implements RoomUserService { @Autowired private RoomUserMapper mapper; @Autowired private RoomService service; @Transactional @Override public void registRoomUser(RoomForm form, TRoomUser roomUser, @AuthenticationPrincipal UserDetailServiceImpll loginUser) { //formをMRoomクラスに変換 MRoom room = new MRoom(); //チャットルーム登録 service.insertRoom(room, form); //ログインユーザーのユーザーID取得 int currentUserId = loginUser.getUser().getId(); //roomsテーブルのIDを設定(FK) roomUser.setRoomId(room.getId()); //ログインユーザーのIDを設定 roomUser.setCurrentUserId(currentUserId); //プルダウン選択されたユーザーIDを設定 roomUser.setUserId(form.getUserId()); //現在時刻の取得 LocalDateTime now = LocalDateTime.now(); roomUser.setCreatedAt(now); //roomUserTBL登録 mapper.insertRoomUser(roomUser); } } 少し処理が多めですが、中間テーブルへの登録ロジックです。 ポイントは、このロジックの中でRoomServiceインターフェースのinsertRoom()メソッドを呼び出している点です。 これにより、roomsテーブルの登録と同時にroom_usersテーブルの登録も行うことができ、roomsテーブルのidを取得して、room_usersテーブルのroomIdカラムに値を設定できます。 また、roomsテーブルに登録するメソッド(insertRoom())の前にroomsテーブルのエンティティのインスタンス(MRoom room = new MRoom();)を作成しておくことも重要です。 これがないと、チャットルーム登録処理時に引数としてMRoomのエンティティを渡せないため登録処理自体が行えないため、最初にインスタンスを作成することが必要です。 その後、UserDetailServiceImpllクラスで取得したログインユーザーのIDを取得、フォームから送られるユーザーIDを取得して、各自セッターで中間テーブルへ登録する値を設定します。 最後に、中間テーブルのMapperに定義している登録メソッドを呼び出せば、roomsテーブルの登録と同時に、中間テーブルへも値を登録できるというロジックが完成します。 なお、ログインユーザーの取得については、以下の記事で詳しく解説しているので、参考にしてみてください。 SpringSecurityとSpringBootでログイン認証と投稿機能を実装する RoomController RoomController @Controller @RequestMapping("/") @Slf4j public class RoomController { @Autowired private UserService userService; @Autowired private RoomService roomService; @Autowired private RoomUserService roomUserService; @GetMapping("/rooms/new") public String getRoomsNew(Model model, @ModelAttribute("form") RoomForm form, @AuthenticationPrincipal UserDetailServiceImpll loginUser) { //ログインユーザーのユーザーID取得 int currentUserId = loginUser.getUser().getId(); //ユーザー取得(複数件) List<MUser> users = userService.getUsers(currentUserId); model.addAttribute("users", users); return "rooms/new"; } @PostMapping("/rooms/new") public String postRoomsNew(Model model, @Validated @ModelAttribute("form") RoomForm form, BindingResult result, TRoomUser roomUser, @AuthenticationPrincipal UserDetailServiceImpll loginUser) { //入力チェック if(result.hasErrors()) { /* NG:チャットルーム作成画面に戻る*/ return "redirect:/rooms/new"; } log.info(form.toString()); //チャットルーム・roomUserTBL登録 roomUserService.registRoomUser(form, roomUser, loginUser); return "redirect:/"; } } コントローラーの処理ですが、postRoomsNew()メソッドから解説します。 ここでは単純にバリデーションのチェックと、ロジックの呼び出しを行うだけです。 次に、getRoomsNew()メソッドの解説ですが、UserServiceのgetUsers()メソッド`がプルダウンを実装する上で重要になってくるので、解説します。 先に結論から言うと、このメソッドの処理はログインユーザー以外のユーザーを取得するメソッドです。 UserMapper UserMapper.java @Mapper public interface UserMapper { /**ログインユーザー以外のユーザー取得(複数件)*/ public List<MUser> findMany(int id); } UserMapper.xml <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <!-- Mapperとxmlのマッピング --> <mapper namespace="com.example.demo.repository.UserMapper"> <!-- マッピング定義(ユーザー) --> <resultMap type="com.example.demo.entity.MUser" id="user"> <id column="id" property="id"></id> <result column="email" property="email"></result> <result column="password" property="password"></result> <result column="password_confirmation" property="passwordConfirmation"></result> <result column="name" property="name"></result> <result column="role" property="role"></result> <result column="created_at" property="createdAt"></result> <result column="updated_at" property="updatedAt"></result> </resultMap> <!-- ログインユーザー以外のユーザー取得(複数件) --> <select id="findMany" resultType="MUser"> select * from users where not id = #{id} </select> </mapper> ポイントはxmlファイルのSQLです。 WHERE NOT句で条件を指定することで、引数に指定されるID以外の値を取得することができます。 UserService UserService.java public interface UserService { /**ログインユーザー以外のユーザー取得(複数件)*/ public List<MUser> getUsers(int id); } UserServiceImpl.java @Service public class UserServiceImpl implements UserService { @Autowired private UserMapper mapper; /**ログインユーザー以外のユーザー取得(複数件)*/ @Override public List<MUser> getUsers(int id) { return mapper.findMany(id); } } View new.html <!DOCTYPE html> <html xmlns:th="http://www.thymeleaf.org"> <head> <meta charset="UTF-8"> <title>ChatApp</title> <link rel="stylesheet" th:href="@{/css/rooms/room.css}"> </head> <body> <div class='chat-room-form'> <h1>新規チャットルーム</h1> <form th:action="@{/rooms/new}" method="post" th:object="${form}"> <div class='chat-room-form__field'> <div class='chat-room-form__field--left'> <label for="roomName" th:text="#{roomName}" class="chat-room-form__label"></label> </div> <div class='chat-room-form__field--right'> <input type="text" th:field="*{roomName}" th:errorclass="is-invalid" class="chat__room_name chat-room-form__input" placeholder="チャットルーム名を入力してください"> </div> <div class="invalid-feedback" th:errors="*{roomName}"></div> </div> <div class='chat-room-form__field'></div> <div class='chat-room-form__field'> <div class='chat-room-form__field--left'> <label class='chat-room-form__label' for='chat_room_チャットメンバー'>チャットメンバー</label> </div> <div class='chat-room-form__field--right'> <select id="userId" name="userId"> <option value="">チャットするユーザーを選択してください</option> <option th:each="user: ${users}" th:value="${user.id}" th:text="${user.name}"></option> </select> </div> </div> <div class='chat-room-form__field'> <div class='chat-room-form__field--left'></div> <div class='chat-room-form__field--right'> <input type="submit" name="commit" class="chat-room-form__action-btn"> </div> </div> </form> </div> </body> </html> ポイントはプルダウンのselectタグの部分です。 通常のフォームタグ内ならth:field="*{userId}"としますが、それだとエラーになるため、selectタグの場合はid属性とname属性に入力させたい(DBに送りたい)値を設定します。 プルダウンの初期値を設定する方法は色々ありますが、自分の場合は、シンプルにoptionタグを二つ作り、一つ目のvalue属性の値を空にして設定しています。 二つ目のoptionタグでは、送信したい値をth:value属性に指定し、表示させたい内容をth:name属性に指定します。 先ほど、コントローラーで呼び出したgetUsers()メソッドはList型のため、th:each属性で値を一つずつ表示・取得できるようにしています。 これで、中間テーブル+αの実装は完了です。 自分の場合はこのロジックを考え出すのに半日くらいかかり、実装するのに3時間くらいかかりました。 まだまだJavaであったりSpringBootの文献は非常に少ないので、同志がいたら参考になれば幸いかと思います。

rake aborted! ActiveRecord::ConnectionNotEstablished: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (38) というエラーが発生した。/tmpディレクトリを確認してもmysql.sockはしっかりと存在する。 mysqlを起動してみると下記のエラーが起こっていることが判明した。 $ sudo mysql.server start Password: Starting MySQL .Logging to '/usr/local/var/mysql/fujiwaratakuminoMacBook-Pro.local.err'. ERROR! The server quit without updating PID file (/usr/local/var/mysql/fujiwaratakuminoMacBook-Pro.local.pid). というエラーが起こっていることが判明した。 pidファイルとは？ 「.pid」で終わっているファイルがpidファイルです。このファイルには、該当するプロセスのプロセスIDなどの情報が記述されています。たとえば、/var/run/crond.pidファイルには、crondのプロセスIDが記述されています。 このファイルは、スクリプトやほかのプロセスで利用されます。プロセスIDが記述されているので、このファイルはプロセスの制御（再起動や停止など）、プロセス同士の連携などに利用されます。 参照先　「/var/run」ディレクトリ ググった末にmysql起動時でエラーが起きた時の対処の記事を参考に $ sudo rm /tmp/mysql.sock $ chown -R _mysql:_mysql mysql を実行。再度sudo mysql.server restartを実行するが、 $ sudo mysql.server restart ERROR! MySQL server PID file could not be found! Starting MySQL .Logging to '/usr/local/var/mysql/fujiwaratakuminoMacBook-Pro.local.err'. ERROR! The server quit without updating PID file (/usr/local/var/mysql/fujiwaratakuminoMacBook-Pro.local.pid). と違うエラーが発生した。/tmp/mysql.sockを消したのがまずかったのかと思った。 今度は、mysql 起動時のThe server quit without updating PID file エラーの回避法を参考にした。 $ ls /usr/local/var/mysqlを実行してみると*****.local.pidがなかった。 $ touch /usr/local/var/mysql/*****.local.pid を実行。sudo mysql.server restartを実行してみるが、*****.local.pidに権限がなかったので、 sudo chown -R _mysql:_mysql /usr/local/var/mysql/ これで無事動くことができました！ 参考にさせて頂いていたサイト 「/var/run」ディレクトリ mysql起動時でエラーが起きた時の対処 mysql 起動時のThe server quit without updating PID file エラーの回避法

rake aborted! ActiveRecord::ConnectionNotEstablished: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (38) というエラーが発生した。/tmpディレクトリを確認してもmysql.sockはしっかりと存在する。 mysqlを起動してみると下記のエラーが起こっていることが判明した。 $ sudo mysql.server start Password: Starting MySQL .Logging to '/usr/local/var/mysql/fujiwaratakuminoMacBook-Pro.local.err'. ERROR! The server quit without updating PID file (/usr/local/var/mysql/fujiwaratakuminoMacBook-Pro.local.pid). というエラーが起こっていることが判明した。 pidファイルとは？ 「.pid」で終わっているファイルがpidファイルです。このファイルには、該当するプロセスのプロセスIDなどの情報が記述されています。たとえば、/var/run/crond.pidファイルには、crondのプロセスIDが記述されています。 このファイルは、スクリプトやほかのプロセスで利用されます。プロセスIDが記述されているので、このファイルはプロセスの制御（再起動や停止など）、プロセス同士の連携などに利用されます。 参照先　「/var/run」ディレクトリ ググった末にmysql起動時でエラーが起きた時の対処の記事を参考に $ sudo rm /tmp/mysql.sock $ chown -R _mysql:_mysql mysql を実行。再度sudo mysql.server restartを実行するが、 $ sudo mysql.server restart ERROR! MySQL server PID file could not be found! Starting MySQL .Logging to '/usr/local/var/mysql/fujiwaratakuminoMacBook-Pro.local.err'. ERROR! The server quit without updating PID file (/usr/local/var/mysql/fujiwaratakuminoMacBook-Pro.local.pid). と違うエラーが発生した。/tmp/mysql.sockを消したのがまずかったのかと思った。 今度は、mysql 起動時のThe server quit without updating PID file エラーの回避法を参考にした。 $ ls /usr/local/var/mysqlを実行してみると*****.local.pidがなかった。 $ touch /usr/local/var/mysql/*****.local.pid を実行。sudo mysql.server restartを実行してみるが、*****.local.pidに権限がなかったので、 sudo chown -R _mysql:_mysql /usr/local/var/mysql/ これで無事動くことができました！ 参考にさせて頂いていたサイト 「/var/run」ディレクトリ mysql起動時でエラーが起きた時の対処 mysql 起動時のThe server quit without updating PID file エラーの回避法

$ mysqladmin password これだけです。 https://dev.mysql.com/doc/refman/8.0/ja/mysqladmin.html 公式サイトに書いてあります。 ・・・どこに？ 新しいパスワードは、password コマンドの後に省略できます。 この場合、mysqladmin はパスワード値を要求し、パスワードをコマンド行で指定するのを避けることができます。 パスワード値は、password が mysqladmin コマンド行の最後のコマンドである場合にかぎって省略できます。 そうでない場合、次の引数がパスワードとみなされます。 上記です。分かりにくい。日本語的に微妙だし、同じことを違う表現で丁寧に二度繰り返して書いてかえって分かりにくくなっていて、まるでコントだ。 けど、この方法が広まってほしい。 $ mysqladmin -u root password "NewPassword" ネットでよく紹介されてるのは上記ですが、これだとコマンド入力の履歴としてパスワード文字列が記録されてしまうため、本番環境ではhistoryを編集するなどの配慮が必要です。 mysqlにログインしてからmysql.userのpasswordカラムの値を編集してprivilegesを更新する方法もよく見かけますが、世の中どんどん便利になっていくので新しい方法をおすすめします。

ゾネスさん（@takahiro-yamada）からバトンを受け取ってPOLのアドベントカレンダーを書きます、業務委託でエンジニアをしている田村です。 二年連続でクリスマスイブにアドベントカレンダーを書いているのですが、決して毎年予定がないわけではないですよ。今年も健全にお仕事頑張るぞい。 今回の題材 はい、そんな田村も今年で社会人3年目なのですが、今年は任せてもらえる仕事が増えました。小さい案件の要求定義書作やリリース後の効果測定やサービスの要因分析をしてグラフにまとめる等、仕事の幅が広がりました。 特にDBからデータを抽出し、そのデータをまとめ分析をするということは結構行っていて、一年間でクエリを92個ほど書いてました（けっこうびっくり） ただ最初からスムーズにできたかと言われるとそうではなかったです。 やり始めた当初は、SQL自体の知識はあったのですが、実務で使うことはあまりなく「どういう場面で使う関数なんだろう？」、「実際に使ってみないとイメージ沸かないな」というものが多数あり、身についてない状態になっていました。 実際にクエリを書いてみると慣れないSQLに苦戦し、一個のデータを出すのに何十分何時間と時間を浪費してました（ほんとその説はすいませんでした） SQLを勉強して、いざ実務でクエリを書いてみようとなるとこういったことに陥る人が結構いるかなと思います。 そんな実務でSQLを使いたての人向けに、この一年でよく使った関数を紹介したいと思います。 紹介する中には「これ勉強した時に見たことある！」みたいなものが多数あると思います。（というかそれしかない気がする） 自分が学んできたことと紐付けて見てもらえると、「あ、これ進研ゼミでやったやつだ」みたいな感覚になれると思います。 データ抽出をする前の田村の知識量 実務経験はほとんどないけど、関数に関してはうろ覚えで頭の片隅にあるレベルでした。 ORACLE MASTER Bronze DBAを取得 →基礎的な知識は資格取得の際に学んでいます PythonのDjangoを使った、DB操作 →SQLとしては書いてませんが、ORMを使いデータベース操作は行っておりました。 環境 MySQL Redash or SQLクライアント（Sequel pro） 前提 今回はデータ抽出をする際に使う関数のみの紹介になるため、テーブル操作系のコマンドについては紹介しません。 また、実行計画やチューニングについても触れないです。 関数紹介 それでは、関数について紹介していきます！ 今回は、関数の概要、使用頻度、例文、補足（使用時の注意点・実際にデータ分析に使う時はこういう場面で使うよ等）でそれぞれ紹介していきます。 LEFT JOIN, INNER JOIN 概要：テーブル間の結合をする時に使う関数 使用頻度：★★★★★ 基本的な関数なので書くか迷ったのですが、データ抽出の際に必ず書くといっても過言ではないので紹介します。 基本的にデータを抽出する時は、テーブルを跨いで抽出するのでまずは結合してデータを取れるようにしていきましょう。 また結合する時にも使える関数が複数あるのですが、今回は自分がよく使ったLEFT JOIN, INNER JOINについて紹介します。 LEFT JOINは外部結合、INNER JOINは内部結合と言われる結合関数になってます。 外部結合？内部結合？と言われてもピンとこないと思うので図にするとこんな感じです。 このように LEFT JOINは条件に合わくても、結合する側のテーブル（実際に書くと上のテーブル）の情報はレコードに抽出し、結合される側のテーブルの情報はNULLとしてレコードに抽出される INNER JOINは条件に合わない場合はレコードそのものが抽出されなくなります。 特性がわかったらどんな時に使うんだって話に移ります。自分はこんな感じで2つを棲み分けてます。 テーブルの中身がユーザに依って任意の時はLEFT JOIN(外部結合)、テーブルの中にユーザに対して必須でデータが入ってる時はINNER JOIN(内部結合) データがない時でも必要なレコードの時はLEFT JOIN(外部結合)、データがない時に必要ないレコードの時はINNER JOIN(内部結合) という感じにやってます。基本的にはINNER JOINの方がレコードを絞れる・レコードが絞れたことで後の処理速度が上がるといったメリットがあります。INNER JOINでかけるところは基本書いていくようにしましょう。 GROUP_CONCAT 概要：指定したカラムに含まれている値をGROUP BYで指定したグループごとに連結させた文字列を表示する関数 使用頻度：★★★★☆ 1対多のデータを扱うときには大抵使います。そしてだいたいのクエリは、1対多のデータを扱うのでほとんどで使うことになります。 例えば、ユーザに紐づく好きな食べ物を管理するテーブルがあったとして id account_id food 1 1 ラーメン 2 1 うどん 3 1 焼き肉 4 1 ハンバーガー 5 2 寿司 6 2 カレーライス 7 2 ケバブ ユーザごとでまとめようとするとGROUP BYを使うことになり‥ SELECT account_id ,food FROM test_table GROUP BY account_id account_id food 1 ラーメン 2 寿司 これだけだと全部の値をデータとして取ることができません。1番の人がただラーメンが好きな人になってしまいます。(最初やりがち） ここでGROUP_CONCATを使うと一つのカラムに文字列を連結させることができます。 SELECT account_id ,GROUP_CONCAT(food) FROM test_table GROUP BY account_id account_id food 1 ラーメン,うどん,焼き肉,ハンバーガー 2 寿司,カレーライス,ケバブ こうして1番の人がうどんも好きなことがわかるようになりましたね。 また区切り文字に関しては第２パラメータで指定できるので用途に合わせて変更しましょう（タブ文字なんかは結構使います） CONCAT 概要：引数に指定した複数の文字列を連結して文字列にする関数 使用頻度：★★★☆☆ プログラムを書いてても文字列結合って結構使うと思うのですが、SQLでも同様なことが言えます。 名字と名前の結合等で使われることがあります。 特にデータをそのまま見る際に、クエリを作る段階で数値に単位を付けたり、見やすいように補足文を入れたりと加工した方が何かと良い場面が多いです。その際にCONCATはよく使いますね。 DATEDIFF 概要：2つの日付の差分をカウントしてくれる関数 使用頻度：★★★★☆ データ抽出の際に、日付を扱うことが結構多いので日付系の関数は覚えたほうがよいです。 この関数でいうと、「登録から何日経った」や「施策を出してから何日経った」や「ユーザが再ログインするまで何日かかった」等、例を上げると切りがないくらい色んなところで使えます。 一緒によく使うNOW()やLAST_DAY()だったり時刻の差分をカウントするTIMEDIFFなども覚えておくと幅が広がります。 DATE_FORMAT 概要：指定した日付の値を指定のフォーマットで整形した文字列を返す関数 使用頻度：★★★★☆ DATEDIFFに続いて日付を扱う関数になっているのでこちらも使用頻度は高めになってます。 データを抽出する上でデータ型をそのまま出してもよいのですが、実際にみたい・欲しいとなった時に「曜日も入れてほしい」・「時刻以降は見ない」など要望に沿った形式で出す方が親切ですし見やすいです。 例えば田村の場合、スプレッドシートにデータを貼り付けることがたまにあるのですが、その際にスプレッドシートのデフォルトの日付のフォーマットに合わせて日付を出したりします。こういった細かいところを気を遣えると良いですね。 また、DATE_FORMATはGROUP BYと合わせて使うことで日付のデータをまとめることができます。 月次： SELECT DATE_FORMAT(date, '%Y-%m') ,COUNT(*) FROM test_table GROUP BY DATE_FORMAT(date, '%Y-%m') 週次： SELECT DATE_FORMAT(date, '%X-%V') ,COUNT(*) FROM test_table GROUP BY DATE_FORMAT(date, '%X-%V') 日時： SELECT DATE_FORMAT(date, '%Y-%m-%d') ,COUNT(*) FROM test_table GROUP BY DATE_FORMAT(date, '%Y-%m-%d') こういった形で使ってあげることでDAU、WAU、MAUのような日付ごとの統計データを取ることができます。 CASE式 概要：SQLで条件分岐させたい時に使う関数 使用頻度：★★★☆☆ 使用用途としては、クエリを作る段階で抽出する目的に沿った形でデータを抽出するために使われることが多いです（CONCATと同じですね） 例えば、ユーザごとのテストの合否が知りたいってなった際に、DB上ではユーザごとの点数を持っているけど合否の情報は持たないということが多いと思うんですよね（持ってる場合もあるけど） そんな時、点数だけを引っ張ってきても合格してるかどうかはわからないですよね。そういう時はCASE式を使い条件分岐させ、合否のカラムを作ってあげるみたいなことをします。 CHAR_LENGTH 概要：指定した文字列の文字数を抽出できる関数 使用頻度：★★☆☆☆ ユーザが自由記述で入力したデータをDBで持つケースが結構あります。 そういったデータの記入率の分布を出したり、編集履歴も残ってる場合は前回更新の差分を出したりとデータを見る上で結構使われます。 自由記述はデータを出す際に様々な観点で出す必要があるのでCHAR_LENGTHは大事です。 DISTINCT 概要：重複したデータを除外してデータを取得することができる関数 使用頻度：★★★☆☆ よく使う場面でいうと重複を省いてその人が持っている属性を数える時に使います。 なのでCOUNTと一緒に使うことが多いですね そしてこのDISTINCTは挙動がちょっとややこしいので例を出しながら説明すると 例えば以下のようなテーブルがあったとします（決して田村の一週間の献立とかじゃないですよ） shop food バーガー屋 ハンバーガー 焼き肉 牛タン ラーメン 醤油ラーメン ラーメン 牛タン バーガー屋 ハンバーガー バーガー屋 チキンバーガー うどん ぶっかけうどん こいつを以下の形で取ろうとすると SELECT DISTINCT shop ,food FROM test_table 重複除外されるのはshopとfoodが一致している「バーガー屋　ハンバーガー」のみになります。 実際にまとめてみると「そりゃそうじゃ」ってなるのですが、実際使ってみると「なんで数値おかしいんだろう・・」みたいなことになるので要注意です。 この場合ですと 何店舗で食べたかを抽出するなら COUNT(DISTINCT shop) 店舗の商品を何種類食べたかを抽出するなら COUNT(DISTINCT shop, food) という感じで使い分けます。 まとめ MySQL初心者が1年間でデータ抽出をする時によく使った関数について紹介しました。いかがでしたか？ 「この関数知ってたけどこういう用途で使われるんだな」って言うのが少しでも伝わったら幸いです。 この記事が少しでも良かったと思ったらLGTMと他の日のアドベントカレンダーの記事を見ていただたいです！ さて、明日は我らがリーダーのミズノさん（@mizno）にバトンを渡します！ 最終日どんないい記事を書いてくれるか期待大ですね＞ω＜/

前提条件 環境 ・ローカル 検証端末：MacOS Monterey (12.1) docker：Docker version 20.10.11, build dea9396 docker-compose：v2.2.1 ・Docker環境 PHP：8.0.13 MySQL：8.0.27 Elasticsearch：7.12.0 読むべき人 Elasticsearch導入したことない人。 家で検証してみたい人。 Elasticという響きがかっこいいなーと思ってる人。 コンテナの構成 ・nginx リクエストを最初に受けるサーバー ・php Laravelが動作するコンテナ。 ・mysql メインのデータストレージとして使用するDB。 ・Elasticsearch 全文検索エンジン。Scout経由で使用する想定。 ・node 画面実装にはないと困るので。 Laravelのモジュールをクローンする git clone https://github.com/laravel/laravel.git 今回作成する環境のディレクトリ構成 elasticsearch_test/ ←ディレクトリ名は任意のものに変更 ├── CHANGELOG.md ├── README.md ├── app ├── artisan ├── bootstrap ├── composer.json ├── composer.lock ├── config ├── database ├── docker ←[追加] 設定ファイルを追加 ├── docker-compose.yml ←[追加] 設定ファイルを追加 ├── package.json ├── phpunit.xml ├── public ├── resources ├── routes ├── server.php ├── src ├── storage ├── tests ├── vendor └── webpack.mix.js 任意のプロジェクト名に変更 今回は「elasticsearch_test」とする。 mv laravel elasticsearch_test Dockerの設定ファイルを追加していく nginxの設定ファイル nginxコンテナ。 Laravelのrootディレクトリはpublic配下なので設定しておく。 listenするポートはコンテナの内側なのでwebサーバーのデフォルトの80でおk。 docker/nginx/default.conf server { listen 80; index index.php index.html; root /var/www/elasticsearch_test/public; location / { try_files $uri $uri/ /index.php?$query_string; } location ~ \.php$ { fastcgi_split_path_info ^(.+\.php)(/.+)$; fastcgi_pass php:9000; fastcgi_index index.php; include fastcgi_params; fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name; fastcgi_param PATH_INFO $fastcgi_path_info; } } phpコンテナのDockerfile phpコンテナはimageではなくDockerfileで設定を指定する。 理由はコンテナ起動時にRUNで叩きたいコマンドがあるからだと思うけど、今回はその辺から拝借してきた記述をそのままにしてある。 docker/php/Dockerfile FROM php:8.0-fpm COPY php.ini /usr/local/etc/php/ RUN apt-get update \ && apt-get install -y zlib1g-dev mariadb-client vim libzip-dev \ && docker-php-ext-install zip pdo_mysql #Composer install RUN php -r "copy('https://getcomposer.org/installer', 'composer-setup.php');" RUN php composer-setup.php RUN php -r "unlink('composer-setup.php');" RUN mv composer.phar /usr/local/bin/composer ENV COMPOSER_ALLOW_SUPERUSER 1 ENV COMPOSER_HOME /composer ENV PATH $PATH:/composer/vendor/bin WORKDIR /var/www COPY . /var/www/ RUN composer global require "laravel/installer" php.ini docker/php/php.ini [Date] date.timezone = "Asia/Tokyo" [mbstring] mbstring.internal_encoding = "UTF-8" mbstring.language = "Japanese" docker-compose.yml コンテナ達を1セットに扱うためのdocker-compose。 それの設定ファイルを定義しておきます。 version: '3' services: php: container_name: php_container build: ./docker/php volumes: - ./:/var/www/elasticsearch_test nginx: container_name: nginx_container image: nginx ports: - 81:80 volumes: - ./:/var/www/elasticsearch_test - ./docker/nginx/default.conf:/etc/nginx/conf.d/default.conf depends_on: - php db: container_name: mysql_container image: mysql:8.0 environment: MYSQL_ROOT_PASSWORD: root MYSQL_DATABASE: database MYSQL_PASSWORD: root TZ: 'Asia/Tokyo' command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci volumes: - ./docker/db/data:/var/lib/mysql - ./docker/db/my.cnf:/etc/mysql/conf.d/my.cnf - ./docker/db/sql:/docker-entrypoint-initdb.d ports: - 3306:3306 es: container_name: elasticsearch_container image: elasticsearch:7.12.0 ports: - "9200:9200" environment: - discovery.type=single-node - cluster.name=docker-cluster - bootstrap.memory_lock=true - "ES_JAVA_OPTS=-Xms512m -Xmx512m" ulimits: memlock: soft: -1 hard: -1 node: image: node:12.13-alpine tty: true volumes: - ./src:/var/www working_dir: /var/www コンテナ起動 起動！ docker-compose up -d コンテナで実行 vendor周りを作成。 composerの意味がわからずvendor配下を他プロジェクトからコピーしてきていた日が僕にもありました。 依存ライブラリはちゃんとcomposerでインストールしよう！ composer install envファイルを作成 cp .env.example .env keyを生成 これあんまり意味分かってない。謎の儀式。 php artisan key:generate 疎通確認 phpコンテナ ブラウザでアクセス localhost:81 mysqlコンテナ コンテナにログイン docker exec -it mysql_container /bin/bash Mysqlにログイン mysql -uroot -proot Databaseを作成する create database elasticsearch_test_db; Mysql8系はデフォルトの接続方法が異なるため変更する ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root'; .envファイルのDBホスト情報をdocker-composeで定義したサービス名に変更 DB_CONNECTION=mysql DB_HOST=db // ←ここ DB_PORT=3306 DB_DATABASE=elasticsearch_test_db DB_USERNAME=root DB_PASSWORD=root Elasticsearchコンテナ とりあえずコンテナに入る docker exec -it elasticsearch_container /bin/bash Elasticserachコンテナの中からなのでhostは「localhost」 [root@8970f3395096 elasticsearch]# curl -X GET localhost:9200 { "name" : "8970f3395096", "cluster_name" : "docker-cluster", "cluster_uuid" : "JKMTJ9JYRDuTfq0o4phKIQ", "version" : { "number" : "7.12.0", "build_flavor" : "default", "build_type" : "docker", "build_hash" : "78722783c38caa25a70982b5b042074cde5d3b3a", "build_date" : "2021-03-18T06:17:15.410153305Z", "build_snapshot" : false, "lucene_version" : "8.8.0", "minimum_wire_compatibility_version" : "6.8.0", "minimum_index_compatibility_version" : "6.0.0-beta1" }, "tagline" : "You Know, for Search" } .envにElasticsearchの設定を追加。 識別はdocker-compose.ymlのserviceに書いたservice名 SCOUT_DRIVER=elasticsearch ELASTICSEARCH_HOST=es:9200 サンプルとなるDBを用意する migration migrationファイルを作成 php artisan make:migration create_scout_test_records_table --create=scout_test_records database/migrations/2021_12_17_031813_create_scout_test_records_table.php のサンプル <?php use Illuminate\Database\Migrations\Migration; use Illuminate\Database\Schema\Blueprint; use Illuminate\Support\Facades\Schema; class CreateScoutTestRecordsTable extends Migration { /** * Run the migrations. * * @return void */ public function up() { Schema::create('scout_test_records', function (Blueprint $table) { $table->increments('id'); $table->string('name', 255)->comment('名前'); $table->string('hash_code', 255)->comment('ハッシュコード'); $table->text('text')->nullable()->comment('テキスト'); $table->double('latitude')->nullable()->comment('緯度'); $table->double('longitude')->nullable()->comment('経度'); $table->integer('type')->comment('タイプ'); $table->tinyInteger('status')->comment('ステータス'); $table->timestamps(); $table->softDeletes(); }); } /** * Reverse the migrations. * * @return void */ public function down() { Schema::dropIfExists('scout_test_records'); } } Model modelを作成 php artisan make:model ScoutTestRecord Models/ScoutTestRecord.php <?php namespace App\Models; use Illuminate\Database\Eloquent\Factories\HasFactory; use Illuminate\Database\Eloquent\Model; use Illuminate\Database\Eloquent\SoftDeletes; class ScoutTestRecord extends Model { use HasFactory; use SoftDeletes; public $table = 'scout_test_records'; public $dates = ['deleted_at']; public $fillable = [ 'id', 'name', 'hash_code', 'text', 'latitude', 'longitude', 'type', 'status', ]; protected $casts = [ 'id' => 'integer', 'name' => 'string', 'hash_code' => 'string', 'text' => 'string', 'latitude' => 'double', 'longitude' => 'double', 'type' => 'integer', 'status' => 'integer', ]; public static $rules = [ 'name' => 'required|string', 'hash_code' => 'required|string', 'text' => 'string', 'type' => 'integer', 'status' => 'integer', ]; } Seeder seederも作成しておく php artisan make:seeder ScoutTestRecordsTableSeeder <?php namespace Database\Seeders; use Illuminate\Database\Seeder; use App\Models\ScoutTestRecord; use Faker\Factory as Faker; class ScoutTestRecordsTableSeeder extends Seeder { /** * Run the database seeds. * * @return void */ public function run() { ScoutTestRecord::factory()->count(20)->create(); } } DatabaseSeeder.php <?php namespace Database\Seeders; use Illuminate\Database\Seeder; use Database\Seeders\ScoutTestRecordsTableSeeder; class DatabaseSeeder extends Seeder { /** * Seed the application's database. * * @return void */ public function run() { $this->call([ ScoutTestRecordsTableSeeder::class, ]); } } Factory config/app.php　factoryの設定をするため一部変更する 'fallback_locale' => 'en', factoryを作成 php artisan make:factory ScoutTestRecordFactory --model=ScoutTestRecord database/factories/ScoutTestRecordFactory.php <?php namespace Database\Factories; use App\Models\ScoutTestRecord; use Illuminate\Database\Eloquent\Factories\Factory; use Illuminate\Support\Carbon; class ScoutTestRecordFactory extends Factory { protected $model = ScoutTestRecord::class; /** * Define the model's default state. * * @return array */ public function definition() { return [ 'name' => $this->faker->name, 'hash_code' => $this->faker->password(), 'text' => $this->faker->realText($maxNbChars = 50, $indexSize = 2), 'latitude' => $this->faker->latitude(-90, 90), 'longitude' => $this->faker->longitude(0, 180), 'type' => $this->faker->randomNumber($nbDigits = 1), 'status' => $this->faker->randomNumber($nbDigits = 1), 'created_at' => Carbon::now(), 'updated_at' => Carbon::now(), 'deleted_at' => null, ]; } } DBにデータを登録する php artisan migrate --seed Elasticsearchにデータを連携する 各モジュールの役割は下記の記事で解説してます。 Scoutの設定 まずはScoutをインストールする。 このあとインストールするエンジンがscoutの8.x系に依存しているため、バージョンを指定する。 バージョン指定のセマンティックバージョンの概念はこれがわかりやすかったです。 composer require laravel/scout:^8.0 設定ファイルをバージョン管理配下に配置する php artisan vendor:publish --provider="Laravel\Scout\ScoutServiceProvider" 連携したいデータソースのModelにモデルオブザーバを登録する <?php namespace App\Models; use Illuminate\Database\Eloquent\Factories\HasFactory; use Illuminate\Database\Eloquent\Model; use Illuminate\Database\Eloquent\SoftDeletes; use Laravel\Scout\Searchable; // 追加 class ScoutTestRecord extends Model { use HasFactory; use SoftDeletes; use Searchable; // 追加 public $table = 'scout_test_records'; ・ ・ ・ エンジンを設定 Elasticsearchのクライアントモジュールをインストール composer require elasticsearch/elasticsearch エンジンは先人達がけっこういっぱい用意してくれてるけど、今回はtamayoさんのやつをチョイス。 composer require tamayo/laravel-scout-elastic エンジンの処理の挙動はカスタマイズしたい場合があるので、app配下にエンジンを継承したファイルを用意する。 mkdir app/Scout/ touch app/Scout/ElasticsearchEngine.php app/Scout/ElasticsearchEngine.php 若干挙動がおかしいところがあったので継承して修正。 <?php namespace App\Scout; use Laravel\Scout\Builder; use Elasticsearch\Client; use Tamayo\LaravelScoutElastic\Engines\ElasticsearchEngine as ScoutElasticsearchEngine; class ElasticsearchEngine extends ScoutElasticsearchEngine { protected $elastic; protected $index; private $query; public function __construct(Client $elastic, $index) { $this->elastic = $elastic; $this->index = $index; } protected function performSearch(Builder $builder, array $options = []) { $params = [ 'index' => $builder->model->searchableAs(), 'type' => get_class($builder->model), 'body' => [ 'query' => [ 'bool' => [ 'must' => [['query_string' => ['query' => "*{$builder->query}*"]]] ] ] ] ]; if ($sort = $this->sort($builder)) { $params['body']['sort'] = $sort; } if (isset($options['from'])) { $params['body']['from'] = $options['from']; } if (isset($options['size'])) { $params['body']['size'] = $options['size']; } if (isset($options['z']) && count($options['numericFilters'])) { $params['body']['query']['bool']['must'] = array_merge( $params['body']['query']['bool']['must'], $options['numericFilters'] ); } if ($builder->callback) { return call_user_func( $builder->callback, $this->elastic, $builder->query, $params ); } return $this->elastic->search($params); } /** * Perform the given search on the engine. * @inheritDoc * @see ScoutElasticsearchEngine::paginate * @param Builder $builder * @param int $perPage　(limit) * @param int $page (offset) * @return mixed */ public function paginate(Builder $builder, $perPage, $page) { $result = $this->performSearch($builder, [ 'numericFilters' => $this->filters($builder), 'from' => (($page * $perPage) - $perPage), 'size' => $perPage, ]); $result['nbPages'] = $result['hits']['total']['value'] / $perPage; return $result; } /** * Map the given results to instances of the given model. * * @inheritDoc * @see ScoutElasticsearchEngine::map * @param \Laravel\Scout\Builder $builder * @param mixed $results * @param \Illuminate\Database\Eloquent\Model $model * @return Collection */ public function map(Builder $builder, $results, $model) { if ($results['hits']['total']['value'] === 0) { return $model->newCollection(); } $keys = collect($results['hits']['hits'])->pluck('_id')->values()->all(); $modelIdPositions = array_flip($keys); return $model->getScoutModelsByIds( $builder, $keys )->filter(function ($model) use ($keys) { return in_array($model->getScoutKey(), $keys); })->sortBy(function ($model) use ($modelIdPositions) { return $modelIdPositions[$model->getScoutKey()]; })->values(); } /** * Get the total count from a raw result returned by the engine. * * @inheritDoc * @see ScoutElasticsearchEngine::getTotalCount * @param mixed $results * @return int */ public function getTotalCount($results) { return $results['hits']['total']['value']; } } Providerを読み込む config/app.php 'providers' => [ /* * Laravel Framework Service Providers... */ Illuminate\Auth\AuthServiceProvider::class, Illuminate\Broadcasting\BroadcastServiceProvider::class, ・ ・ ・ // Elasticsearch Tamayo\LaravelScoutElastic\LaravelScoutElasticProvider::class, // ←これを追加 ], 軽く動作確認 ここまで書いたらphpのコンテナにログインしてScoutが提供しているデータの取り込みスクリプトを実行する。 php artisan scout:import "App\Models\ScoutTestRecord" Imported [App\Models\ScoutTestRecord] models up to ID: 20 All [App\Models\ScoutTestRecord] records have been imported. // こうなれば成功 次はElasticsearchに検索リクエストを投げてみる。 コンテナ間の通信はdocker-composeのおかげでservice名で識別できる。 curl -X GET es:9200/scout_test_records/_search {"took":9,"timed_out":false,"_shards":{"total":1,"successful":1,"skipped":0,"failed":0},"hits":{"total":{"value":20,"relation":"eq"},"max_score":1.0,"hits":[{"_index":"scout_test_records","_type":"App\\Models\\ScoutTestRecord","_id":"1","_score":1.0,"_source":{"id":1,"name":"山田 涼平","has... // 結果が返却されればOK! まとめ MySQL8.0とかPHP8を使ったせいで実プロジェクトで完コピして作業するには難しいかも。 でもおおまかな流れは全て一緒で、変わるとしたらcompsoerを使って依存ライブラリを導入した時のバージョンが変わるくらいかなー。 あと使ったライブラリがあきらか個人名みたいな名前だけどMITライセンス、みたいな感じ、 これってどれくらい信用度あるんだろ。 フレームワーク本体とかよりは信頼度低い気がする。 そのまま使ったら普通にエラーになったし。 クエリの使い方とかちょいちょい上げていきます。 記載漏れとかあったら是非ご連絡ください！