こんにちは、のSGKです。こんにちは。

私、今年で24年ず～とWEBエンジニアをしていて、最近初めて実装しました、JOINの条件にサブクエリを使うとか。

ということで、どんなときに使ったかを簡単な例を踏まえて紹介したいと思います。

テーブル構成

ある施設の「利用者」とその利用者の施設の「利用期間」を管理しているとします。

そしてその「利用者の健康状態」も管理記録しています。
「利用者の健康状態」は変更日時を付けて履歴を残すようになっています。

ER図は以下の通り。

ER図はPostgreSql対応だけど、、SQLite対応のスキーマのSQLを簡単に書くとこんな感じです。

※テストに使いやすかったので以降SQLはSQLite対応で書いていきます。

-- 利用期間
create table periods (
  id integer PRIMARY KEY AUTOINCREMENT
  , user_id integer not null
  , start datetime not null
  , end datetime not null
) ;

-- 利用者の健康状態
create table healths (
  id integer PRIMARY KEY AUTOINCREMENT
  , user_id integer not null
  , val integer not null
  , change datetime not null
) ;

-- 利用者
create table users (
  id integer PRIMARY KEY AUTOINCREMENT
  , name text not null
) ;

テストデータ

テストデータはこんな感じで入れておきます。

INSERT INTO "users" ("id", "name") VALUES
	(1, '浦島太郎'),
	(2, '桃太郎'),
	(3, '金太郎');

INSERT INTO "healths" ("id", "user_id", "val", "change") VALUES
	(1, 1, 8, '2024-08-23 00:00:00'),
	(2, 1, 150, '2024-08-22 00:00:00'),
	(3, 1, 100, '2024-08-21 00:00:00'),
	(4, 2, 10, '2024-08-23 00:00:00'),
	(5, 3, 999, '2024-08-22 00:00:00'),
	(6, 3, 1250, '2024-08-23 00:00:00');

INSERT INTO "periods" ("id", "user_id", "start", "end") VALUES
	(1, 1, '2024-08-23 00:00:00', '2024-08-23 00:30:00'),
	(2, 2, '2024-08-23 00:00:00', '2024-08-23 00:30:00'),
	(3, 3, '2024-08-23 00:00:00', '2024-08-23 00:30:00'),
	(4, 1, '2024-08-21 00:00:00', '2024-08-21 00:30:00'),
	(5, 3, '2024-08-22 00:00:00', '2024-08-23 00:30:00');

履歴が絡むデータを取得

では、このテーブルから利用者の施設利用ごとの利用開始時の健康状態を取得する方法を考えてみましょう。

施設利用ごとに利用者の healths.change が 利用開始時（periods.start）に最新のレコードを1件取得するクエリになるので以下の様なSQLになります。

SELECT 
  users.name
  , healths.val
  , healths.change
  , periods.start

FROM 
  periods
  INNER JOIN users ON periods.user_id = users.id
  LEFT JOIN healths ON healths.user_id = users.id

WHERE
  healths.id IN (
    SELECT 
    healths.id 
    FROM
      healths 
    WHERE 
      healths.change <= periods.start
      AND healths.user_id = users.id 
    ORDER BY 
      healths.change DESC 
    LIMIT 
      1
  )

ORDER BY
  periods.start

サブクエリを JOIN の条件に書く

普通は上記のSQLのように書くと思います。
で、実は WHERE に書いたサブクエリを JOIN の条件に書くこともできます。
それが以下のSQLです。

SELECT 
  users.name
  , healths.val
  , healths.change
  , periods.start

FROM 
  periods
  INNER JOIN users ON periods.user_id = users.id
  LEFT JOIN healths ON (
    SELECT 
      healths.id 
    FROM
      healths 
    WHERE 
      healths.change <= periods.start
      AND healths.user_id = users.id 
    ORDER BY 
      healths.change DESC 
    LIMIT 
      1
  ) = healths.id

ORDER BY
  periods.start

ふーんできるんだ、書いたことないわこんなん。キモチワルイ。

出力結果

ちなみに結果は以下の通り。
浦島はおじいさんになったんでしょう、桃太郎は生まれたてか？金太郎はいつも元気なんでしょうね。

それはCakePKP

なぜJOINの条件にサブクエリを書くような事になったかというと弊社愛用のCakePHPのためです。

CakePKPで書くと以下のようになります。

class PeriodsTable extends Table{
	public function initialize(array $config): void{
		//～略、他の初期化処理

		$this->belongsTo('Users', [
			'foreignKey' => 'user_id',
			'joinType' => 'INNER',
		]);

		$this->hasOne('Healths', [
			'className' => 'Healths',
			'foreignKey' => false,
			'conditions' => [
				"(
					SELECT 
						healths.id 
					FROM
						healths 
					WHERE 
						healths.change <= Periods.start
						AND healths.user_id = Users.id 
					ORDER BY 
						healths.change DESC 
					LIMIT 
						1
				) = Healths.id"
			],
		]);
	}
}

class HealthsTable extends Table{
	public function initialize(array $config): void{
		//～略、他の初期化処理

		$this->belongsTo('Users', [
			'foreignKey' => 'user_id',
			'joinType' => 'INNER',
		]);
	}
}

class UsersTable extends Table{
	public function initialize(array $config): void{
		//～略、他の初期化処理

		$this->hasMany('Healths', [
			'foreignKey' => 'user_id',
		]);

		$this->hasMany('Periods', [
			'foreignKey' => 'user_id',
		]);
	}
}

//テストでSQLと結果をcontrollerで表示する例
class TestController extends Controller{
	public function index(){
		$query = \Cake\ORM\TableRegistry::getTableLocator()->get( 'Periods' )->find()
			->select([
				'Users.name',
				'Healths.val',
				'Healths.change',
				'Periods.start',
			])
			->contain(['Users', 'Healths'])
			->order(['Periods.start'])
		;

		\DebugKit\DebugSql::sql($query, true, true);
		echo '<hr>';
		dd($query->all());
	}
}

PeriodsTable で Joinの条件にサブクエリを置く事で Periods を Healths と関連付けることができるようになりました。
ただしこのサブクエリにはUsersが含まれているため contain するときは Healths の前に Users を置く必要があります。
conditions の定義にサブクエリとかまたまキモチワルイコードなのですがこれをやっておけば
->contain([‘Users’, ‘Healths’])
だけで複雑な条件を指定しなければ取得できないデータを一発で取得できるのでとても便利です。

以上JOINの条件にサブクエリを使った話でございました。