お世話になります。
数日前に質問をして、追記等してみたのですが、回答が無いため、別の形で質問させていただきます。
テーブル
Jobテーブル(assignのmaxを指定)
Assignテーブル(job_idとuser_idを持つ)
Userテーブル
抽出したいもの
Job
条件
JobにヒモヅクAssignの数がmax以下のもの
JobにヒモヅクAssignのuser_idに自分がいない
上記のような条件でcakephp3系のクエリを駆使して取得したいです。
また、SQLではこう書くとか、別の方法で提案があると嬉しいです。
PHP
1$query = $this->Jobs->find('search', [ 2 'search' => $this->request->query, 3 'contain' => ['Assigns'], 4 'conditions' => [ 5// この部分 6 ] 7]);
よろしくお願いします。
気になる質問をクリップする
クリップした質問は、後からいつでもMYページで確認できます。
またクリップした質問に回答があった際、通知やメールを受け取ることができます。
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
回答2件
0
テーブル生成
sql
1CREATE TABLE jobs ( 2 id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT, 3 name VARCHAR(255) NOT NULL 4); 5 6CREATE TABLE users ( 7 id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT, 8 name VARCHAR(255) NOT NULL 9); 10 11CREATE TABLE assigns ( 12 job_id INTEGER UNSIGNED NOT NULL, 13 user_id INTEGER UNSIGNED NOT NULL, 14 FOREIGN KEY (job_id) REFERENCES jobs(id), 15 FOREIGN KEY (user_id) REFERENCES users(id), 16 UNIQUE (job_id, user_id) 17); 18 19INSERT INTO jobs(name) VALUES ('Job1'), ('Job2'), ('Job3'), ('Job4'); 20INSERT INTO users(name) VALUES ('User1'), ('User2'), ('User3'); 21INSERT INTO assigns(job_id, user_id) VALUES (1, 1), (1, 2), (1, 3), (2, 1), (2, 3), (3, 2);
アサイン数3未満でユーザ2に対するアサインがまだ付いていないジョブを選択
sql
1SELECT jobs.* FROM jobs 2LEFT JOIN assigns ON assigns.job_id = jobs.id 3GROUP BY jobs.id 4HAVING 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さんの回答を参照してください。
投稿2017/06/07 04:08
編集2017/06/09 04:44総合スコア5223
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2017/06/07 14:54
2017/06/08 15:55
2017/06/08 22:57 編集
2017/06/09 03:26
2017/06/09 07:07
0
ベストアンサー
mpywさん、詳しい回答ありがとうございます。
私は今回の質問者ではありませんが、とてもためになりました。
以下はjobsテーブルをメインに取得するCakePHP3のコードです。
最後にページネーションを利用しています。
SQL・テーブル・データはmpywさんのものを拝借しています。
PHP
1$user_id = 2; 2$max = 3; 3 4$query = $this->Jobs->find(); 5$query 6->leftJoinWith('Assigns') 7->group( 8 'Jobs.id' 9) 10->having([ 11 $query->newExpr()->lte($query->func()->count('Assigns.user_id'), $max, 'integer'), 12 $query->newExpr()->eq( 13 $query->func()->coalesce([ 14 $query->func()->sum( 15 $query->newExpr()->eq( 16 'Assigns.user_id', 17 $user_id 18 ) 19 ), 20 0 21 ], 22 ['integer', 'integer'] 23 ), 24 0, 25 'integer' 26 ) 27]); 28 29// 確認 30debug($query->all()); 31 32// ページネーション 33$this->set('jobs', $this->paginate($query)); 34
having句は長くなるので、以下のように書いてもいいと思います。
PHP
1->having([ 2 'COUNT(Assigns.user_id) <=' => $max, 3 "COALESCE(SUM(assigns.user_id = {$user_id}), 0) =" => 0 4]);
CakePHP3のDebug Kitに表示されるSQL
SQL
1SELECT 2 Jobs.id AS `Jobs__id`, 3 Jobs.name AS `Jobs__name` 4FROM 5 jobs Jobs 6 LEFT JOIN assigns Assigns ON Jobs.id = (Assigns.job_id) 7GROUP BY 8 Jobs.id 9HAVING 10 ( 11 ( 12 COUNT(Assigns.user_id) 13 ) <= 3 14 AND ( 15 COALESCE( 16 ( 17 SUM( 18 (Assigns.user_id = 2) 19 ) 20 ), 21 0 22 ) 23 ) = 0 24 )
以下のコードでは誤った値が取得されます。
(履歴として残しておきますが間違いです。)
mpywさんの作ったテーブルとデータを使って、ORMを使う方法を考えてみました。
正攻法ではやり方が分からないので、jobsテーブルではなく、assignsテーブルをメインに使う方法です。
PHP
1use Cake\ORM\TableRegistry; 2 3// 異なるモデルを使った場合でもソートできるようにする 4public $paginate = [ 5 'sortWhitelist' => [ 6 'Assigns.job_id','Jobs.name' 7 ], 8]; 9 10// ページネーションを使用する方法 11$user_id = 2; 12$max = 3; 13 14$query = TableRegistry::get('Assigns') 15 ->find(); 16// ->find('search', ['search' => $this->request->query]); 17 18$query 19 ->select([ 20 'Assigns.job_id', 21 'Assigns.user_id', 22 'count' => $query->func()->count('Assigns.job_id') 23 ]) 24 ->contain([ 25 'Jobs' => function ($q) { 26 return $q 27 ->select(['Jobs.id', 'Jobs.name']); 28 } 29 ]) 30 ->group( 31 'Assigns.job_id' 32 ) 33 ->having([ 34 'count <=' => $max, 35 'Assigns.user_id !=' => $user_id 36 ]); 37 38// 確認 39debug($query->all()); 40 41// ページネーション 42$this->set('jobs', $this->paginate($query));
投稿2017/06/08 16:25
編集2017/06/09 06:12総合スコア37
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2017/06/08 17:49
2017/06/09 03:28
2017/06/09 04:56
2017/06/09 06:14
2017/06/09 07:06
あなたの回答
tips
太字
斜体
打ち消し線
見出し
引用テキストの挿入
コードの挿入
リンクの挿入
リストの挿入
番号リストの挿入
表の挿入
水平線の挿入
プレビュー
質問の解決につながる回答をしましょう。 サンプルコードなど、より具体的な説明があると質問者の理解の助けになります。 また、読む側のことを考えた、分かりやすい文章を心がけましょう。