前提
自分
DBよわよわフロントエンジにゃー。
mysql
$ mysql --version Wed Jan 30 16:50:13 2019
mysql Ver 14.14 Distrib 5.7.21, for osx10.12 (x86_64) using EditLine wrapper
テーブル
mysql> desc user_logs;
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| user_id | int(11) | YES | | NULL | |
| action_id | int(11) | YES | | NULL | |
| created_at | datetime | YES | | NULL | |
+------------+------------------+------+-----+---------+----------------+
mysql> select * from user_logs;
+----+---------+-----------+---------------------+
| id | user_id | action_id | created_at |
+----+---------+-----------+---------------------+
| 1 | 1 | 1 | 2019-01-30 16:00:00 |
| 2 | 1 | 2 | 2019-01-30 17:00:00 |
| 3 | 2 | 1 | 2019-01-30 18:00:00 |
| 4 | 2 | 3 | 2019-01-30 19:00:00 |
+----+---------+-----------+---------------------+
やりたいこと
ユーザーごとに最後になにをしていたか知りたい。
SQL 的に言うと、 user_id が重複しないように created_at が最も大きい時の action_id が取得したい。
上のテーブルだと二つ目と4つ目のレコードがほしい。
+----+---------+-----------+---------------------+
| id | user_id | action_id | created_at |
+----+---------+-----------+---------------------+
| 1 | 1 | 1 | 2019-01-30 16:00:00 |
| 2 | 1 | 2 | 2019-01-30 17:00:00 | <- このレコードと
| 3 | 2 | 1 | 2019-01-30 18:00:00 |
| 4 | 2 | 3 | 2019-01-30 19:00:00 | <- このレコード
+----+---------+-----------+---------------------+
やったこと
select * from user_logs
where (user_id, created_at) in (
select user_id, max(created_at) from user_logs group by user_id
)
解説
where in 句を使うと、 in 句のいずれかに合致するレコードのみに絞れる。
つまり user_id と created_at がわかればよいので
mysql> select * from user_logs
-> where (user_id, created_at) in (
-> (1, '2019-01-30 17:00:00'),
-> (2, '2019-01-30 19:00:00')
-> );
+----+---------+-----------+---------------------+
| id | user_id | action_id | created_at |
+----+---------+-----------+---------------------+
| 2 | 1 | 2 | 2019-01-30 17:00:00 |
| 4 | 2 | 3 | 2019-01-30 19:00:00 |
+----+---------+-----------+---------------------+
次に user_id と created_at を取得したい。
group by 句を使うと指定したカラムの内容でグループ化出来る。
mysql> select user_id, max(created_at) from user_logs group by user_id;
+---------+---------------------+
| user_id | max(created_at) |
+---------+---------------------+
| 1 | 2019-01-30 17:00:00 |
| 2 | 2019-01-30 19:00:00 |
+---------+---------------------+
はまったこと
distinct を使おうとした
mysql> select distinct user_id from user_logs;
+---------+
| user_id |
+---------+
| 1 |
| 2 |
+---------+
これで created_at も取ろうとすると
mysql> select distinct user_id, created_at from user_logs;
+---------+---------------------+
| user_id | created_at |
+---------+---------------------+
| 1 | 2019-01-30 16:00:00 |
| 1 | 2019-01-30 17:00:00 |
| 2 | 2019-01-30 18:00:00 |
| 2 | 2019-01-30 19:00:00 |
+---------+---------------------+
全部出てくる。
max() 使ってみる
mysql> select distinct user_id, max(created_at) from user_logs;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'test.user_logs.user_id'; this is incompatible with sql_mode=only_full_group_by
だめ。
group by で 非集約カラム以外も取得しようとした
mysql> select * from user_logs group by user_id;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.user_logs.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
だめ。
エラーの原因は ここ で詳しく解説されていたので割愛。決して意味がわからなかったわけではない。
課題
これ Eloquent でどうやるの。