CakePHP3の複雑クエリでのfind

Question

お世話になります。
数日前に質問をして、追記等してみたのですが、回答が無いため、別の形で質問させていただきます。

テーブル
Jobテーブル(assignのmaxを指定)
Assignテーブル(job_idとuser_idを持つ)
Userテーブル

抽出したいもの
Job

条件
JobにヒモヅクAssignの数がmax以下のもの
JobにヒモヅクAssignのuser_idに自分がいない

上記のような条件でcakephp3系のクエリを駆使して取得したいです。
また、SQLではこう書くとか、別の方法で提案があると嬉しいです。

$query = $this->Jobs->find('search', [
    'search' => $this->request->query,
    'contain' => ['Assigns'],
    'conditions' => [
// この部分
    ]
]);

よろしくお願いします。

Comments

[mpyw]

書き方的に完全にCakePHP1/2のコードを見ているようですが，お使いのバージョンは本当に3でしょうか？ (備考) CakePHP 3のQueryBuilderの使い方をまとめてみた！ - Qiita
http://qiita.com/kozo/items/87dc9f725e71dd742468

[kenkbou]

はい、３系です。3系ではメソッドチェーンも使用可能ですが、今まで通りの方法も使用できます。

[mpyw]

なるほど，初耳でした。ありがとうございます

Answer 1

テーブル生成

CREATE TABLE jobs (
  id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE users (
  id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE assigns (
  job_id INTEGER UNSIGNED NOT NULL,
  user_id INTEGER UNSIGNED NOT NULL,
  FOREIGN KEY (job_id) REFERENCES jobs(id),
  FOREIGN KEY (user_id) REFERENCES users(id), 
  UNIQUE (job_id, user_id)
);

INSERT INTO jobs(name) VALUES ('Job1'), ('Job2'), ('Job3'), ('Job4');
INSERT INTO users(name) VALUES ('User1'), ('User2'), ('User3');
INSERT INTO assigns(job_id, user_id) VALUES (1, 1), (1, 2), (1, 3), (2, 1), (2, 3), (3, 2);

アサイン数3未満でユーザ2に対するアサインがまだ付いていないジョブを選択

SELECT jobs.* FROM jobs
LEFT JOIN assigns ON assigns.job_id = jobs.id
GROUP BY jobs.id
HAVING COUNT(assigns.user_id) < 3 AND COALESCE(SUM(assigns.user_id = 2), 0) = 0;

結果と実行計画の確認

mysql> SELECT jobs.* FROM jobs
    -> LEFT JOIN assigns ON assigns.job_id = jobs.id
    -> GROUP BY jobs.id
    -> HAVING COUNT(assigns.user_id) < 3 AND COALESCE(SUM(assigns.user_id = 2), 0) = 0;
+----+------+
| id | name |
+----+------+
|  2 | Job2 |
|  4 | Job4 |
+----+------+
2 rows in set (0.00 sec)

mysql> EXPLAIN
    -> SELECT jobs.* FROM jobs
    -> LEFT JOIN assigns ON assigns.job_id = jobs.id
    -> GROUP BY jobs.id
    -> HAVING COUNT(assigns.user_id) < 3 AND COALESCE(SUM(assigns.user_id = 2), 0) = 0;
+----+-------------+---------+-------+---------------+---------+---------+-----------------+------+-------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref             | rows | Extra       |
+----+-------------+---------+-------+---------------+---------+---------+-----------------+------+-------------+
|  1 | SIMPLE      | jobs    | index | PRIMARY       | PRIMARY | 4       | NULL            |    4 | NULL        |
|  1 | SIMPLE      | assigns | ref   | job_id        | job_id  | 4       | example.jobs.id |    1 | Using index |
+----+-------------+---------+-------+---------------+---------+---------+-----------------+------+-------------+
2 rows in set (0.00 sec)

HAVING句のみだとフルインデックススキャンになってしまうので，総数が多い場合はジョブを絞り込むためのWHERE句が適宜必要です。

【ポイント】

• COUNT(assigns.user_id) でアサインされている数を取得できる。COUNTの引数にLEFT JOINのターゲットのカラムを指定しているので，存在しない場合は0になる。
• COALESCE(SUM(assigns.user_id = 2), 0) でユーザ2にアサインされている数を取得できる。論理式の結果は1または0になり，これが集計される。但し誰に対するアサインも存在しない場合はNULLになってしまうので，その場合は数値の0に変換している。

CakePHP3での例はdangaoさんの回答を参照してください。

Comments

[kenkbou]

ありがとうございます！SQL的にはこちらの通りです。ただ、Cakeでの書き方が分かりません、、、もう少し勉強してみます

[dangao]

mpywさんの回答で気になることがあるのでちょっと伺いたいのですが……、もしよろしければ教えてください。

> アサイン数3未満でユーザ2に対するアサインがまだ付いていないジョブを選択
で書いているSQLの5行目
> HAVING (SUM(1) < 3 AND assignee_id != 2) OR assignee_id IS NULL
についてです。

SUM(1) と書いてありますが、アソシエーション先のテーブル（assignsテーブル）から、アサインされているカラムを数えて合計する場合は、どのようにすればよいでしょうか。
ここが最も難しいと思うので、教えていただけると幸いです。

kenkbouさんの以前の関連質問（質問文中のリンク先）でも、
> ・アソシエーション先のstatusが１の要素が３個以下という条件になります。
と書いてありますので……。

[mpyw]

回答を大幅に修正しました。

もとの回答は，そもそもHAVINGの中に非集約なカラムを突っ込んでいる時点で結果が保証されておらず，例えば

INSERT INTO assigns(job_id, user_id) VALUES (3, 1);

を実行すると誤った結果を取得するようになっていました。

[dangao]

mpywさん
ご回答いただきありがとうございます。学ぶ良い機会になりました。

kenkbouさん
横から失礼いたしました。

[kenkbou]

mpywさん
この度はありがとうございます。SQLに関してはCake以上に知識が乏しいので、勉強しようと思います

Answer 2

mpywさん、詳しい回答ありがとうございます。
私は今回の質問者ではありませんが、とてもためになりました。

以下はjobsテーブルをメインに取得するCakePHP3のコードです。
最後にページネーションを利用しています。
SQL・テーブル・データはmpywさんのものを拝借しています。

$user_id = 2;
$max = 3;

$query = $this->Jobs->find();
$query
->leftJoinWith('Assigns')
->group(
	'Jobs.id'
)
->having([
	$query->newExpr()->lte($query->func()->count('Assigns.user_id'), $max, 'integer'),
	$query->newExpr()->eq(
		$query->func()->coalesce([
			$query->func()->sum(
				$query->newExpr()->eq(
					'Assigns.user_id',
					$user_id
				)
			),
			0
		],
		['integer', 'integer']
		),
		0,
		'integer'
	)
]);

// 確認
debug($query->all());

// ページネーション
$this->set('jobs', $this->paginate($query));

having句は長くなるので、以下のように書いてもいいと思います。

->having([
	'COUNT(Assigns.user_id) <=' => $max,
	"COALESCE(SUM(assigns.user_id = {$user_id}), 0) =" => 0
]);

CakePHP3のDebug Kitに表示されるSQL

SELECT 
  Jobs.id AS `Jobs__id`, 
  Jobs.name AS `Jobs__name` 
FROM 
  jobs Jobs 
  LEFT JOIN assigns Assigns ON Jobs.id = (Assigns.job_id) 
GROUP BY 
  Jobs.id 
HAVING 
  (
    (
      COUNT(Assigns.user_id)
    ) <= 3 
    AND (
      COALESCE(
        (
          SUM(
            (Assigns.user_id = 2)
          )
        ), 
        0
      )
    ) = 0
  )

以下のコードでは誤った値が取得されます。

（履歴として残しておきますが間違いです。）

mpywさんの作ったテーブルとデータを使って、ORMを使う方法を考えてみました。
正攻法ではやり方が分からないので、jobsテーブルではなく、assignsテーブルをメインに使う方法です。

use Cake\ORM\TableRegistry;

// 異なるモデルを使った場合でもソートできるようにする
public $paginate = [
    'sortWhitelist' => [
        'Assigns.job_id','Jobs.name'
    ],
];

// ページネーションを使用する方法
$user_id = 2;
$max = 3;

$query = TableRegistry::get('Assigns')
	->find();
//	->find('search', ['search' => $this->request->query]);

$query
	->select([
		'Assigns.job_id',
		'Assigns.user_id',
		'count' => $query->func()->count('Assigns.job_id')
	])
	->contain([
		'Jobs' => function ($q) {
			return $q
				->select(['Jobs.id', 'Jobs.name']);
		}
	])
	->group(
		'Assigns.job_id'
	)
	->having([
		'count <=' => $max,
		'Assigns.user_id !=' => $user_id
	]);

// 確認
debug($query->all());

// ページネーション
$this->set('jobs', $this->paginate($query));

Comments

[kenkbou]

ありがとうございます。非常に勉強になります。ただ、最終的に取得したいのはJobなんですよね、、、

[dangao]

Jobも同時に取得していますよ。
でも取得できている値に誤りがあるので、mpywさんのSQLをもとに再度書き直しました。

[kenkbou]

ありがとうございます！こちらで求めている情報が取得出来ました。
ただ、maxがJobsテーブルのmaxカラムのものを使用したいと思っているのですが、それって可能でしょうか。程度の低い質問で申し訳ございません。

[dangao]

> maxがJobsテーブルのmaxカラムのものを使用したいと思っている
$maxを$query->newExpr('Jobs.max')にかえたらどうなりますか？

あと、lt()「<」とlte()「<=」の違いがあるのですが、上記コードで修正し忘れていたので、修正しました。

[kenkbou]

ありがとうございます！理想的な動きになりました。この度は本当に勉強になりましたmm