こういう groups 1-* users な関係のテーブル定義があるときに、
drop table if exists users;
drop table if exists groups;
create table groups (
id int not null primary key auto_increment,
name varchar (100)
);
create table users (
id int not null primary key auto_increment,
group_id int not null,
name varchar (100),
foreign KEY (group_id) references groups (id)
);
insert into groups values
(100, 'A'),
(200, 'B'),
(300, 'C');
insert into users (group_id, name) values
(100, 'ore'),
(100, 'are'),
(300, 'sore'),
(300, 'dore'),
(300, 'kore');
Eloquent で groups の一覧を得つつ hasMany な関係の users が１行でもあるかどうかを得たいです。
SQL 的には次のような感じです。
select G.*, (exists (select * from users U where U.group_id = G.id)) as has_users from groups G;
/*
+-----+------+-----------+
| id | name | has_users |
+-----+------+-----------+
| 100 | A | 1 |
| 200 | B | 0 |
| 300 | C | 1 |
+-----+------+-----------+
*/
相関サブクエリが親の仇の人なら次のような感じでしょうか。
select G.*, group_id IS NOT NULL as has_users from groups G left join (
select group_id from users group by group_id
) U on G.id = U.group_id;
/*
+-----+------+-----------+
| id | name | has_users |
+-----+------+-----------+
| 100 | A | 1 |
| 200 | B | 0 |
| 300 | C | 1 |
+-----+------+-----------+
*/
Eloquent のクラス定義
Model クラスと User クラスの定義です。
use Illuminate\Database\Eloquent\Model;
class Group extends Model
{
public $timestamps = false;
public function users()
{
return $this->hasMany(User::class);
}
}
class User extends Model
{
public $timestamps = false;
public function group()
{
return $this->belongsTo(Group::class);
}
}
試行錯誤の履歴
愚直にやると、
$groups = Group::all();
foreach ($groups as $group) {
printf("id:%d name:%s has_users:%d\n", $group->id, $group->name, $group->users->isNotEmpty());
}
いわゆる N+1 なクエリになってます。
select * from `groups`;
select * from `users` where `users`.`group_id` = 100 and `users`.`group_id` is not null;
select * from `users` where `users`.`group_id` = 200 and `users`.`group_id` is not null;
select * from `users` where `users`.`group_id` = 300 and `users`.`group_id` is not null;
with('users') で Eager ロードすると、
$groups = Group::with('users')->get();
foreach ($groups as $group) {
printf("id:%d name:%s has_users:%d\n", $group->id, $group->name, $group->users->isNotEmpty());
}
2番目のクエリで users の全レコードがフェッチされます。存在しているかどうか知りたいだけなので全レコードフェッチするのは過剰です。
select * from `groups`;
select * from `users` where `users`.`group_id` in (100, 200, 300);
$group->users()->exists() ならレコードはフェッチされない、という話をどこかで聞いた気がしますが（どこで？）、
$groups = Group::all();
foreach ($groups as $group) {
printf("id:%d name:%s has_users:%d\n", $group->id, $group->name, $group->users()->exists());
}
レコードはフェッチされませんが N+1 です。
select * from `groups`;
select exists(select * from `users` where `users`.`group_id` = 100 and `users`.`group_id` is not null) as `exists`;
select exists(select * from `users` where `users`.`group_id` = 200 and `users`.`group_id` is not null) as `exists`;
select exists(select * from `users` where `users`.`group_id` = 300 and `users`.`group_id` is not null) as `exists`;
Eager ロードを組み合わせてみると、
$groups = Group::with('users')->get();
foreach ($groups as $group) {
printf("id:%d name:%s has_users:%d\n", $group->id, $group->name, $group->users()->exists());
}
意味ありませんでした。
select * from `groups`;
select * from `users` where `users`.`group_id` in (100, 200, 300);
select exists(select * from `users` where `users`.`group_id` = 100 and `users`.`group_id` is not null) as `exists`;
select exists(select * from `users` where `users`.`group_id` = 200 and `users`.`group_id` is not null) as `exists`;
select exists(select * from `users` where `users`.`group_id` = 300 and `users`.`group_id` is not null) as `exists`;
全グループとユーザーが存在するグループのみの２回のクエリに分ける
has('users') でユーザーが存在するグループの一覧は取れるので、２回に分けてみます。
$groups = Group::all();
$hasUsersGroupIds = Group::query()->has('users')->get(['id'])->keyBy('id');
foreach ($groups as $group) {
printf("id:%d name:%s has_users:%d\n", $group->id, $group->name, isset($hasUsersGroupIds[$group->id]));
}
若干の無駄はあるもののユーザーが全部フェッチされたり N+1 なことにはなりません。
select * from `groups`;
select `id` from `groups` where exists (select * from `users` where `groups`.`id` = `users`.`group_id`);
相関サブクエリや SQL を直で書く
モデルで定義した hasMany の関係なんて使わずに直で相関サブクエリを書いてみます。
$groups = Group::query()->select('*')
->selectSub('exists (select * from users where users.group_id = groups.id)', 'has_users')
->get();
foreach ($groups as $group) {
printf("id:%d name:%s has_users:%d\n", $group->id, $group->name, $group->has_users);
}
期待した感じの結果になりました。
select *, (exists (select * from users where users.group_id = groups.id)) as `has_users` from `groups`;
ただこれなら fromQuery() で直で SQL を書いても良いかも。
$groups = Group::fromQuery('select G.*, exists(select * from users U where U.group_id = G.id) as has_users from groups G');
foreach ($groups as $group) {
printf("id:%d name:%s has_users:%d\n", $group->id, $group->name, $group->has_users);
}
select G.*, exists(select * from users U where U.group_id = G.id) as has_users from groups G;
ただ $groups は Group のコレクションのはずで、その Group のインスタンスに謎の has_users という属性が生えていることに違和感が・・それなら find() とかで取得した Group のインスタンスにも has_users のような属性が生えているべきな気がするので、
/**
* @property int $id
* @property string $name
* @property bool $has_users
*/
class Group extends Model
{
public $timestamps = false;
public function users()
{
return $this->hasMany(User::class);
}
public function getHasUsersAttribute($value): bool
{
return (bool)($value ?? $this->users()->exists());
}
}
相関サブクエリで has_users が取れていればそれがそのまま返るし、find() などで取ってきたインスタンスでも has_users 属性の参照時に select exists(...) なクエリが実行されてその結果が返ります。
$groups = Group::fromQuery('select G.*, exists(select * from users U where U.group_id = G.id) as has_users from groups G');
foreach ($groups as $group) {
// no sql ... eager loaded
printf("id:%d name:%s has_users:%d\n", $group->id, $group->name, $group->has_users);
}
$group = Group::find(100);
// select exists(select * from `users` where `users`.`group_id` = 100 and `users`.`group_id` is not null) as `exists`
var_dump($group->has_users);
さいごに
「相関サブクエリや SQL を直で書く」の方法が一番無駄がなくて綺麗な気はするのだけど、「全グループとユーザーが存在するグループのみの２回のクエリに分ける」の方がベタで Eloquent 使うならこういうものなのかなという気もするし、いっそ富豪的に全ユーザーがフェッチされてもいいじゃんとわりきるか、あるいはもっと良い方法がありそうな気がしないでもない？