はじめに

Laravel の中間テーブルを使った多対多(belongsToMany)の関係で、中間テーブルのカラムでソートする方法を調べた。

イメージとしては「ユーザー」と「サークル」があって、「ユーザー」は複数の「サークル」に所属可能、「サークル」には複数の「ユーザー」が所属。サークルへの加入時期(created_at)でソートする、など。

TL;DR

  • withPivotorderBy を組み合わせる
  • withPivot('created_at AS joined_at') のように別名を付けると分かりやすい

目次

  1. はじめに
  2. TL;DR
  3. 環境・条件
  4. 詳細
    1. 前置き
      1. プロジェクト作成
      2. DB(SQLite) 設定
      3. マイグレーション
      4. モデルの作成
      5. 試験データの作成
    2. 本題: belongsToMany な関係で中間テーブルのカラムでソートする方法
  5. まとめ
  6. 参考文献

環境・条件

1
2
3
4
5
6
7
8
9
10
11
$ sw_vers
ProductName: Mac OS X
ProductVersion: 10.15
BuildVersion: 19A602

$ php -v
PHP 7.3.8 (cli) (built: Aug 24 2019 19:38:14) ( NTS )

$ composer info laravel/framework
name : laravel/framework
versions : * v6.4.1

詳細

前置き

ここは本題ではないので、読み飛ばしても良い。PHP, Laravel に慣れてないので、自分のメモのためにも記載。

読み飛ばし用リンク

プロジェクト作成

Composer を使って適当にプロジェクト作成、結構時間がかかるので待つ。

1
2
3
4
5
6
$ composer create-project --prefer-dist laravel/laravel laravel-sandbox
Installing laravel/laravel (v6.4.0)
...
Application key set successfully.

$ cd laravel-sandbox

DB(SQLite) 設定

手っ取り早く sqlite を使うので設定していく、database/database.sqlite を作成。

1
$ touch database/database.sqlite

.env を編集、sqlite を使うようにする。

1
2
3
4
5
6
7
8
9
10
11
12
13
-DB_CONNECTION=mysql
-DB_HOST=127.0.0.1
-DB_PORT=3306
-DB_DATABASE=laravel
-DB_USERNAME=root
-DB_PASSWORD=
+# DB_CONNECTION=mysql
+# DB_HOST=127.0.0.1
+# DB_PORT=3306
+# DB_DATABASE=laravel
+# DB_USERNAME=root
+# DB_PASSWORD=
+DB_CONNECTION=sqlite

マイグレーション

初期マイグレーションファイルは不要なので削除。

1
2
3
4
5
6
$ ls database/migrations/201
2014_10_12_000000_create_users_table.php
2014_10_12_100000_create_password_resets_table.php
2019_08_19_000000_create_failed_jobs_table.php

$ rm -f database/migrations/*

php artisan make:migrationusers, circles, circle_user テーブル作成用のマイグレーションファイルを作成。

1
2
3
4
5
6
7
8
$ php artisan make:migration CreateUsersTable
Created Migration: 2019_11_01_121208_create_users_table

$ php artisan make:migration CreateCirclesTable
Created Migration: 2019_11_01_121258_create_circles_table

$ php artisan make:migration CreateCircleUserTable
Created Migration: 2019_11_01_121309_create_circle_user_table

それぞれ以下のように編集、カラムは最小限にしている。
userdown() は省略。

users: YYYY_MM_DD_hhmmss_create_users_table

1
2
3
4
5
6
7
8
9
10
11
12
class CreateUsersTable extends Migration
{
public function up()
{
Schema::create('users', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('name');
$table->timestamp('created_at')->useCurrent();
$table->timestamp('updated_at')->useCurrent()->onUpdate(DB::raw('CURRENT_TIMESTAMP'));
});
}
}

circles: YYYY_MM_DD_hhmmss_create_circles_table

1
2
3
4
5
6
7
8
9
10
11
12
class CreateCirclesTable extends Migration
{
public function up()
{
Schema::create('circles', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('name');
$table->timestamp('created_at')->useCurrent();
$table->timestamp('updated_at')->useCurrent()->onUpdate(DB::raw('CURRENT_TIMESTAMP'));
});
}
}

circle_user: YYYY_MM_DD_hhmmss_create_circle_user_table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
class CreateCircleUserTable extends Migration
{
public function up()
{
Schema::create('circle_user', function (Blueprint $table) {
$table->bigIncrements('id');
$table->unsignedBigInteger('circle_id');
$table->foreign('circle_id')->references('id')->on('circles')->onDelete('cascade');
$table->unsignedBigInteger('user_id');
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
$table->timestamp('created_at')->useCurrent();
$table->timestamp('updated_at')->useCurrent()->onUpdate(DB::raw('CURRENT_TIMESTAMP'));
});
}
}

マイグレーションファイルを編集したら、php artisan migrate を実行。

1
2
3
4
5
6
7
8
$ php artisan migrate
Migration table created successfully.
Migrating: 2019_11_01_121208_create_users_table
Migrated: 2019_11_01_121208_create_users_table (0 seconds)
Migrating: 2019_11_01_121258_create_circles_table
Migrated: 2019_11_01_121258_create_circles_table (0 seconds)
Migrating: 2019_11_01_121309_create_circle_user_table
Migrated: 2019_11_01_121309_create_circle_user_table (0 seconds)

モデルの作成

マイグレーションファイル同様に、最初から用意されている app/User.php は削除する。

1
$ rm -f app/User.php

php artisan make:modelUser, Circle を作成する。

1
2
3
4
5
$ php artisan make:model User
Model created successfully.

$ php artisan make:model Circle
Model created successfully.

モデルの $fillable とリレーションを定義する。

app/User.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class User extends Model
{
protected $fillable = ['name'];

public function circles()
{
return $this->belongsToMany(Circle::class);
}
}

app/Circle.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Circle extends Model
{
protected $fillable = ['name'];

public function users()
{
return $this->belongsToMany(User::class);
}
}

試験データの作成

Seeder 作っても良いけど、とりあえず Tinker で適当にデータを投入する。(ついでにリレーションの確認もする)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
$ php artisan tinker
Psy Shell v0.9.9 (PHP 7.3.8 — cli) by Justin Hileman
>>> use App\User;
>>> use App\Circle;

# === ユーザー作成 ===
>>> User::create(['name' => 'taro']);
=> App\User {#2996
name: "taro",
updated_at: "2019-11-01 12:31:17",
created_at: "2019-11-01 12:31:17",
id: 1,
}
>>> User::create(['name' => 'hanako']);
=> App\User {#3000
name: "hanako",
updated_at: "2019-11-01 12:31:21",
created_at: "2019-11-01 12:31:21",
id: 2,
}
>>> User::create(['name' => 'kenji']);
=> App\User {#2992
name: "kenji",
updated_at: "2019-11-01 12:31:25",
created_at: "2019-11-01 12:31:25",
id: 3,
}
>>> User::create(['name' => 'kumi']);
=> App\User {#2996
name: "kumi",
updated_at: "2019-11-01 12:31:32",
created_at: "2019-11-01 12:31:32",
id: 4,
}

# === サークル作成 ===
>>> Circle::create(['name' => 'soccer']);
=> App\Circle {#3000
name: "soccer",
updated_at: "2019-11-01 12:31:43",
created_at: "2019-11-01 12:31:43",
id: 1,
}
>>> Circle::create(['name' => 'teniss']);
=> App\Circle {#2992
name: "teniss",
updated_at: "2019-11-01 12:31:47",
created_at: "2019-11-01 12:31:47",
id: 2,
}
>>> Circle::create(['name' => 'movie']);
=> App\Circle {#2996
name: "movie",
updated_at: "2019-11-01 12:32:01",
created_at: "2019-11-01 12:32:01",
id: 3,
}
>>> Circle::create(['name' => 'music']);
=> App\Circle {#3000
name: "music",
updated_at: "2019-11-01 12:32:26",
created_at: "2019-11-01 12:32:26",
id: 4,
}

レコードの紐付けを attach で行う。User 側からでも Circle 側からでも、どちらからでもいける。

1
2
3
4
5
6
7
8
>>> User::find(1)->circles()->attach(Circle::find(2))
>>> User::find(1)->circles()->attach(Circle::find(3))
>>> User::find(2)->circles()->attach(Circle::find(3))
>>> User::find(2)->circles()->attach(Circle::find(4))
>>> Circle::find(4)->users()->attach(User::find(3))
>>> Circle::find(1)->users()->attach(User::find(3))
>>> Circle::find(1)->users()->attach(User::find(4))
>>> Circle::find(2)->users()->attach(User::find(4))

紐付けされているか確認。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
>>> User::find(2)->circles
=> Illuminate\Database\Eloquent\Collection {#3024
all: [
App\Circle {#3038
id: "3",
name: "movie",
created_at: "2019-11-01 12:32:01",
updated_at: "2019-11-01 12:32:01",
pivot: Illuminate\Database\Eloquent\Relations\Pivot {#3036
user_id: "2",
circle_id: "3",
},
},
App\Circle {#3030
id: "4",
name: "music",
created_at: "2019-11-01 12:32:26",
updated_at: "2019-11-01 12:32:26",
pivot: Illuminate\Database\Eloquent\Relations\Pivot {#3003
user_id: "2",
circle_id: "4",
},
},
],
}

>>> Circle::find(1)->users
=> Illuminate\Database\Eloquent\Collection {#3037
all: [
App\User {#3041
id: "3",
name: "kenji",
created_at: "2019-11-01 12:31:25",
updated_at: "2019-11-01 12:31:25",
pivot: Illuminate\Database\Eloquent\Relations\Pivot {#3022
circle_id: "1",
user_id: "3",
},
},
App\User {#3031
id: "4",
name: "kumi",
created_at: "2019-11-01 12:31:32",
updated_at: "2019-11-01 12:31:32",
pivot: Illuminate\Database\Eloquent\Relations\Pivot {#3043
circle_id: "1",
user_id: "4",
},
},
],
}

本題: belongsToMany な関係で中間テーブルのカラムでソートする方法

ようやく本題。

中間テーブルの値でソートしたい場合には withPivot を使うと良い。

1
2
3
4
Circle::find(1)->users()
->withPivot('created_at AS joined_at')
->orderBy('joined_at', 'desc')
->get();

普通に ->users で取得した場合はこうなる。(データ増やした)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
>>> Circle::find(1)->users                                                                                                                                                                                                                                                    => Illuminate\Database\Eloquent\Collection {#3060
all: [
App\User {#3027
id: "3",
name: "kenji",
created_at: "2019-11-01 12:31:25",
updated_at: "2019-11-01 12:31:25",
pivot: Illuminate\Database\Eloquent\Relations\Pivot {#3028
circle_id: "1",
user_id: "3",
},
},
App\User {#3050
id: "4",
name: "kumi",
created_at: "2019-11-01 12:31:32",
updated_at: "2019-11-01 12:31:32",
pivot: Illuminate\Database\Eloquent\Relations\Pivot {#3067
circle_id: "1",
user_id: "4",
},
},
App\User {#3079
id: "1",
name: "taro",
created_at: "2019-11-01 12:31:17",
updated_at: "2019-11-01 12:31:17",
pivot: Illuminate\Database\Eloquent\Relations\Pivot {#3081
circle_id: "1",
user_id: "1",
},
},
App\User {#3058
id: "2",
name: "hanako",
created_at: "2019-11-01 12:31:21",
updated_at: "2019-11-01 12:31:21",
pivot: Illuminate\Database\Eloquent\Relations\Pivot {#3035
circle_id: "1",
user_id: "2",
},
},
],
}

withPivot を使う場合はこうなる。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
>>> Circle::find(1)->users()\
... ->withPivot('created_at AS joined_at')\
... ->orderBy('joined_at', 'desc')->get();
=> Illuminate\Database\Eloquent\Collection {#3085
all: [
App\User {#3084
id: "2",
name: "hanako",
created_at: "2019-11-01 12:31:21",
updated_at: "2019-11-01 12:31:21",
joined_at: "2019-11-01 12:48:02",
pivot: Illuminate\Database\Eloquent\Relations\Pivot {#3083
circle_id: "1",
user_id: "2",
},
},
App\User {#3055
id: "1",
name: "taro",
created_at: "2019-11-01 12:31:17",
updated_at: "2019-11-01 12:31:17",
joined_at: "2019-11-01 12:48:00",
pivot: Illuminate\Database\Eloquent\Relations\Pivot {#3090
circle_id: "1",
user_id: "1",
},
},
App\User {#3070
id: "4",
name: "kumi",
created_at: "2019-11-01 12:31:32",
updated_at: "2019-11-01 12:31:32",
joined_at: "2019-11-01 12:40:44",
pivot: Illuminate\Database\Eloquent\Relations\Pivot {#3087
circle_id: "1",
user_id: "4",
},
},
App\User {#3095
id: "3",
name: "kenji",
created_at: "2019-11-01 12:31:25",
updated_at: "2019-11-01 12:31:25",
joined_at: "2019-11-01 12:40:10",
pivot: Illuminate\Database\Eloquent\Relations\Pivot {#3051
circle_id: "1",
user_id: "3",
},
},
],
}

ちなみに、今回のケースだと users.created_atcircle_user.created_at が同名なので、withPivot('created_at' AS joined_at') として別名にしている。

別名にせずにそのまま create_at を用いると users.created_at での並び替えになるので注意。(重複してないならそのまま使えるはず)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
>>> Circle::find(1)->users()
... ->withPivot('created_at')
... ->orderBy('created_at', 'desc')->get();
=> Illuminate\Database\Eloquent\Collection {#3087
all: [
App\User {#3084
id: "4",
name: "kumi",
created_at: "2019-11-01 12:31:32",
updated_at: "2019-11-01 12:31:32",
pivot: Illuminate\Database\Eloquent\Relations\Pivot {#3090
circle_id: "1",
user_id: "4",
created_at: "2019-11-01 12:40:44",
},
},
App\User {#3106
id: "3",
name: "kenji",
created_at: "2019-11-01 12:31:25",
updated_at: "2019-11-01 12:31:25",
pivot: Illuminate\Database\Eloquent\Relations\Pivot {#3082
circle_id: "1",
user_id: "3",
created_at: "2019-11-01 12:40:10",
},
},
App\User {#3104
id: "2",
name: "hanako",
created_at: "2019-11-01 12:31:21",
updated_at: "2019-11-01 12:31:21",
pivot: Illuminate\Database\Eloquent\Relations\Pivot {#3085
circle_id: "1",
user_id: "2",
created_at: "2019-11-01 12:48:02",
},
},
App\User {#3109
id: "1",
name: "taro",
created_at: "2019-11-01 12:31:17",
updated_at: "2019-11-01 12:31:17",
pivot: Illuminate\Database\Eloquent\Relations\Pivot {#3055
circle_id: "1",
user_id: "1",
created_at: "2019-11-01 12:48:00",
},
},
],
}

まとめ

  • withPivotorderBy を組み合わせる
  • withPivot('created_at AS joined_at') のように別名を付けると分かりやすい

参考文献

関連記事