teratail
回答率
85.47%
質問するログイン新規登録
トップPHPに関する質問CakePHP3の複雑クエリでのfind

編集履歴

回答編集履歴

17

消去

2017/06/09 04:44

投稿

mpyw
mpyw

スコア5223

test CHANGED
@@ -140,40 +140,4 @@
140
140
 
141
141
 
142
142
 
143
- CakePHP3.1以降での例
143
+ CakePHP3での例はdangaoさんの回答を参照してください。
144
-
145
-
146
-
147
- ```php
148
-
149
- $table = TableRegistry::get('Jobs');
150
-
151
- $q = $table->find();
152
-
153
- $q
154
-
155
- ->select($table)
156
-
157
- ->leftJoinWith('Assigns')
158
-
159
- ->group('Jobs.id')
160
-
161
- ->having([
162
-
163
- $q->func()->count('Assigns.user_id')->lt(3),
164
-
165
- $q->func()->coalesce([
166
-
167
- $q->func()->sum($q->newExpr('Assigns.user_id')->notEq(2)),
168
-
169
- 0,
170
-
171
- ])->eq(0)
172
-
173
- ]);
174
-
175
- ```
176
-
177
-
178
-
179
- ↑ノリで書いてみました，動くかどうかは知りません

16

以降

2017/06/09 04:44

投稿

mpyw
mpyw

スコア5223

test CHANGED
@@ -140,7 +140,7 @@
140
140
 
141
141
 
142
142
 
143
- CakePHP3.1 での例
143
+ CakePHP3.1以降での例
144
144
 
145
145
 
146
146
 

15

3\.1

2017/06/09 00:08

投稿

mpyw
mpyw

スコア5223

test CHANGED
@@ -140,7 +140,7 @@
140
140
 
141
141
 
142
142
 
143
- CakePHP3 での例
143
+ CakePHP3.1 での例
144
144
 
145
145
 
146
146
 

14

SELECT

2017/06/09 00:07

投稿

mpyw
mpyw

スコア5223

test CHANGED
@@ -146,11 +146,13 @@
146
146
 
147
147
  ```php
148
148
 
149
- $q = TableRegistry::get('Jobs')->find();
149
+ $table = TableRegistry::get('Jobs');
150
+
151
+ $q = $table->find();
150
152
 
151
153
  $q
152
154
 
153
- ->select($q)
155
+ ->select($table)
154
156
 
155
157
  ->leftJoinWith('Assigns')
156
158
 

13

内側に変数があるのでやっぱり消す

2017/06/09 00:07

投稿

mpyw
mpyw

スコア5223

test CHANGED
@@ -175,31 +175,3 @@
175
175
 
176
176
 
177
177
  ↑ノリで書いてみました，動くかどうかは知りません
178
-
179
-
180
-
181
- ```php
182
-
183
- $q = TableRegistry::get('Jobs')->find();
184
-
185
- $q
186
-
187
- ->select($q)
188
-
189
- ->leftJoinWith('Assigns')
190
-
191
- ->group('Jobs.id')
192
-
193
- ->having([
194
-
195
- 'COUNT(Assigns.user_id) <' => 3,
196
-
197
- 'COALESCE(SUM(Assigns.user_id != 2), 0)' => 0,
198
-
199
- ]);
200
-
201
- ```
202
-
203
-
204
-
205
- ↑これぐらいでも十分かもしれませんね，ORMによる行き過ぎた抽象化はアンチパターンです

12

func\(\)使わない方法

2017/06/08 23:55

投稿

mpyw
mpyw

スコア5223

test CHANGED
@@ -175,3 +175,31 @@
175
175
 
176
176
 
177
177
  ↑ノリで書いてみました，動くかどうかは知りません
178
+
179
+
180
+
181
+ ```php
182
+
183
+ $q = TableRegistry::get('Jobs')->find();
184
+
185
+ $q
186
+
187
+ ->select($q)
188
+
189
+ ->leftJoinWith('Assigns')
190
+
191
+ ->group('Jobs.id')
192
+
193
+ ->having([
194
+
195
+ 'COUNT(Assigns.user_id) <' => 3,
196
+
197
+ 'COALESCE(SUM(Assigns.user_id != 2), 0)' => 0,
198
+
199
+ ]);
200
+
201
+ ```
202
+
203
+
204
+
205
+ ↑これぐらいでも十分かもしれませんね，ORMによる行き過ぎた抽象化はアンチパターンです

11

coalesce args

2017/06/08 23:50

投稿

mpyw
mpyw

スコア5223

test CHANGED
@@ -160,11 +160,13 @@
160
160
 
161
161
  $q->func()->count('Assigns.user_id')->lt(3),
162
162
 
163
- $q->func()->coalesce($q->func()->sum(
163
+ $q->func()->coalesce([
164
164
 
165
- $q->newExpr('Assigns.user_id')->notEq(2)
165
+ $q->func()->sum($q->newExpr('Assigns.user_id')->notEq(2)),
166
166
 
167
+ 0,
168
+
167
- ), 0)->eq(0)
169
+ ])->eq(0)
168
170
 
169
171
  ]);
170
172
 

10

Group

2017/06/08 23:48

投稿

mpyw
mpyw

スコア5223

test CHANGED
@@ -154,7 +154,7 @@
154
154
 
155
155
  ->leftJoinWith('Assigns')
156
156
 
157
- ->group('Assigns.job_id')
157
+ ->group('Jobs.id')
158
158
 
159
159
  ->having([
160
160
 

9

CakePHP3

2017/06/08 23:44

投稿

mpyw
mpyw

スコア5223

test CHANGED
@@ -146,9 +146,11 @@
146
146
 
147
147
  ```php
148
148
 
149
- $q = TableRegistry::get('Jobs');
149
+ $q = TableRegistry::get('Jobs')->find();
150
150
 
151
+ $q
152
+
151
- $q->find()
153
+ ->select($q)
152
154
 
153
155
  ->leftJoinWith('Assigns')
154
156
 
@@ -158,7 +160,11 @@
158
160
 
159
161
  $q->func()->count('Assigns.user_id')->lt(3),
160
162
 
163
+ $q->func()->coalesce($q->func()->sum(
164
+
161
- $q->func()->coalesce($q->func()->sum($q->newExpr('Assigns.user_id')->notEq(2)), 0)->eq(0)
165
+ $q->newExpr('Assigns.user_id')->notEq(2)
166
+
167
+ ), 0)->eq(0)
162
168
 
163
169
  ]);
164
170
 

8

CakePHP3

2017/06/08 23:42

投稿

mpyw
mpyw

スコア5223

test CHANGED
@@ -126,7 +126,7 @@
126
126
 
127
127
 
128
128
 
129
- HAVING句のみだとフルインデックススキャンになってしまうので，総数が多い場合はジョブを絞り込むためのWHERE句が適宜必要です。また，CakePHP3はちょっとまだ使ったことがほとんど無いので，ORMを使った回答は他の人に任せます。
129
+ HAVING句のみだとフルインデックススキャンになってしまうので，総数が多い場合はジョブを絞り込むためのWHERE句が適宜必要です。
130
130
 
131
131
 
132
132
 
@@ -139,3 +139,31 @@
139
139
  - `COALESCE(SUM(assigns.user_id = 2), 0)` でユーザ2にアサインされている数を取得できる。論理式の結果は1または0になり，これが集計される。但し誰に対するアサインも存在しない場合はNULLになってしまうので，その場合は数値の0に変換している。
140
140
 
141
141
 
142
+
143
+ CakePHP3 での例
144
+
145
+
146
+
147
+ ```php
148
+
149
+ $q = TableRegistry::get('Jobs');
150
+
151
+ $q->find()
152
+
153
+ ->leftJoinWith('Assigns')
154
+
155
+ ->group('Assigns.job_id')
156
+
157
+ ->having([
158
+
159
+ $q->func()->count('Assigns.user_id')->lt(3),
160
+
161
+ $q->func()->coalesce($q->func()->sum($q->newExpr('Assigns.user_id')->notEq(2)), 0)->eq(0)
162
+
163
+ ]);
164
+
165
+ ```
166
+
167
+
168
+
169
+ ↑ノリで書いてみました，動くかどうかは知りません

7

補足

2017/06/08 23:37

投稿

mpyw
mpyw

スコア5223

test CHANGED
@@ -127,3 +127,15 @@
127
127
 
128
128
 
129
129
  HAVING句のみだとフルインデックススキャンになってしまうので，総数が多い場合はジョブを絞り込むためのWHERE句が適宜必要です。また，CakePHP3はちょっとまだ使ったことがほとんど無いので，ORMを使った回答は他の人に任せます。
130
+
131
+
132
+
133
+ 【ポイント】
134
+
135
+
136
+
137
+ - `COUNT(assigns.user_id)` でアサインされている数を取得できる。COUNTの引数にLEFT JOINのターゲットのカラムを指定しているので，存在しない場合は0になる。
138
+
139
+ - `COALESCE(SUM(assigns.user_id = 2), 0)` でユーザ2にアサインされている数を取得できる。論理式の結果は1または0になり，これが集計される。但し誰に対するアサインも存在しない場合はNULLになってしまうので，その場合は数値の0に変換している。
140
+
141
+

6

大幅に修正

2017/06/08 23:04

投稿

mpyw
mpyw

スコア5223

test CHANGED
@@ -56,17 +56,13 @@
56
56
 
57
57
  ```sql
58
58
 
59
- SELECT jobs.id, jobs.name FROM (
59
+ SELECT jobs.* FROM jobs
60
60
 
61
- SELECT jobs.*, assigns.user_id AS assignee_id FROM jobs
61
+ LEFT JOIN assigns ON assigns.job_id = jobs.id
62
62
 
63
- LEFT JOIN assigns ON assigns.job_id = jobs.id
63
+ GROUP BY jobs.id
64
64
 
65
- GROUP BY jobs.id
66
-
67
- HAVING (SUM(1) < 3 AND assignee_id != 2) OR assignee_id IS NULL
65
+ HAVING COUNT(assigns.user_id) < 3 AND COALESCE(SUM(assigns.user_id = 2), 0) = 0;
68
-
69
- ) jobs;
70
66
 
71
67
  ```
72
68
 
@@ -78,17 +74,13 @@
78
74
 
79
75
  ```
80
76
 
81
- mysql> SELECT jobs.id, jobs.name FROM (
77
+ mysql> SELECT jobs.* FROM jobs
82
78
 
83
- -> SELECT jobs.*, assigns.user_id AS assignee_id FROM jobs
79
+ -> LEFT JOIN assigns ON assigns.job_id = jobs.id
84
80
 
85
- -> LEFT JOIN assigns ON assigns.job_id = jobs.id
81
+ -> GROUP BY jobs.id
86
82
 
87
- -> GROUP BY jobs.id
88
-
89
- -> HAVING (SUM(1) < 3 AND assignee_id != 2) OR assignee_id IS NULL
83
+ -> HAVING COUNT(assigns.user_id) < 3 AND COALESCE(SUM(assigns.user_id = 2), 0) = 0;
90
-
91
- -> ) jobs;
92
84
 
93
85
  +----+------+
94
86
 
@@ -106,35 +98,29 @@
106
98
 
107
99
 
108
100
 
109
-
110
-
111
101
  mysql> EXPLAIN
112
102
 
113
- -> SELECT jobs.id, jobs.name FROM (
103
+ -> SELECT jobs.* FROM jobs
114
104
 
115
- -> SELECT jobs.*, assigns.user_id AS assignee_id FROM jobs
105
+ -> LEFT JOIN assigns ON assigns.job_id = jobs.id
116
106
 
117
- -> LEFT JOIN assigns ON assigns.job_id = jobs.id
107
+ -> GROUP BY jobs.id
118
108
 
119
- -> GROUP BY jobs.id
109
+ -> HAVING COUNT(assigns.user_id) < 3 AND COALESCE(SUM(assigns.user_id = 2), 0) = 0;
120
110
 
121
- -> HAVING (SUM(1) < 3 AND assignee_id != 2) OR assignee_id IS NULL
111
+ +----+-------------+---------+-------+---------------+---------+---------+-----------------+------+-------------+
122
112
 
123
- -> ) jobs;
113
+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
124
114
 
125
- +----+-------------+------------+-------+---------------+---------+---------+-----------------+------+-------------+
115
+ +----+-------------+---------+-------+---------------+---------+---------+-----------------+------+-------------+
126
116
 
127
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
117
+ | 1 | SIMPLE | jobs | index | PRIMARY | PRIMARY | 4 | NULL | 4 | NULL |
128
118
 
129
- +----+-------------+------------+-------+---------------+---------+---------+-----------------+------+-------------+
119
+ | 1 | SIMPLE | assigns | ref | job_id | job_id | 4 | example.jobs.id | 1 | Using index |
130
120
 
131
- | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 4 | NULL |
121
+ +----+-------------+---------+-------+---------------+---------+---------+-----------------+------+-------------+
132
122
 
133
- | 2 | DERIVED | jobs | index | PRIMARY | PRIMARY | 4 | NULL | 4 | NULL |
134
-
135
- | 2 | DERIVED | assigns | ref | job_id | job_id | 4 | example.jobs.id | 1 | Using index |
123
+ 2 rows in set (0.00 sec)
136
-
137
- +----+-------------+------------+-------+---------------+---------+---------+-----------------+------+-------------+
138
124
 
139
125
  ```
140
126
 

5

補足

2017/06/08 22:56

投稿

mpyw
mpyw

スコア5223

test CHANGED
@@ -140,4 +140,4 @@
140
140
 
141
141
 
142
142
 
143
- CakePHP3はちょっとまだ使ったことがほとんど無いので他の人に回答任せます
143
+ HAVING句のみだとフルインデックススキャンになってしまうので，総数が多い場合はジョブを絞り込むためのWHERE句が適宜必要です。また，CakePHP3はちょっとまだ使ったことがほとんど無いので，ORMを使った回答は他の人に任せます。

4

assignee

2017/06/07 04:33

投稿

mpyw
mpyw

スコア5223

test CHANGED
@@ -58,13 +58,13 @@
58
58
 
59
59
  SELECT jobs.id, jobs.name FROM (
60
60
 
61
- SELECT jobs.*, assigns.user_id AS assigned_user_id FROM jobs
61
+ SELECT jobs.*, assigns.user_id AS assignee_id FROM jobs
62
62
 
63
63
  LEFT JOIN assigns ON assigns.job_id = jobs.id
64
64
 
65
65
  GROUP BY jobs.id
66
66
 
67
- HAVING (SUM(1) < 3 AND assigned_user_id != 2) OR assigned_user_id IS NULL
67
+ HAVING (SUM(1) < 3 AND assignee_id != 2) OR assignee_id IS NULL
68
68
 
69
69
  ) jobs;
70
70
 
@@ -80,13 +80,13 @@
80
80
 
81
81
  mysql> SELECT jobs.id, jobs.name FROM (
82
82
 
83
- -> SELECT jobs.*, assigns.user_id AS assigned_user_id FROM jobs
83
+ -> SELECT jobs.*, assigns.user_id AS assignee_id FROM jobs
84
84
 
85
85
  -> LEFT JOIN assigns ON assigns.job_id = jobs.id
86
86
 
87
87
  -> GROUP BY jobs.id
88
88
 
89
- -> HAVING (SUM(1) < 3 AND assigned_user_id != 2) OR assigned_user_id IS NULL
89
+ -> HAVING (SUM(1) < 3 AND assignee_id != 2) OR assignee_id IS NULL
90
90
 
91
91
  -> ) jobs;
92
92
 
@@ -112,13 +112,13 @@
112
112
 
113
113
  -> SELECT jobs.id, jobs.name FROM (
114
114
 
115
- -> SELECT jobs.*, assigns.user_id AS assigned_user_id FROM jobs
115
+ -> SELECT jobs.*, assigns.user_id AS assignee_id FROM jobs
116
116
 
117
117
  -> LEFT JOIN assigns ON assigns.job_id = jobs.id
118
118
 
119
119
  -> GROUP BY jobs.id
120
120
 
121
- -> HAVING (SUM(1) < 3 AND assigned_user_id != 2) OR assigned_user_id IS NULL
121
+ -> HAVING (SUM(1) < 3 AND assignee_id != 2) OR assignee_id IS NULL
122
122
 
123
123
  -> ) jobs;
124
124
 

3

補足

2017/06/07 04:30

投稿

mpyw
mpyw

スコア5223

test CHANGED
@@ -1,4 +1,4 @@
1
- SQLでの例
1
+ テーブル生成
2
2
 
3
3
 
4
4
 
@@ -137,3 +137,7 @@
137
137
  +----+-------------+------------+-------+---------------+---------+---------+-----------------+------+-------------+
138
138
 
139
139
  ```
140
+
141
+
142
+
143
+ CakePHP3はちょっとまだ使ったことがほとんど無いので他の人に回答任せます

2

補足

2017/06/07 04:23

投稿

mpyw
mpyw

スコア5223

test CHANGED
@@ -69,3 +69,71 @@
69
69
  ) jobs;
70
70
 
71
71
  ```
72
+
73
+
74
+
75
+ 結果と実行計画の確認
76
+
77
+
78
+
79
+ ```
80
+
81
+ mysql> SELECT jobs.id, jobs.name FROM (
82
+
83
+ -> SELECT jobs.*, assigns.user_id AS assigned_user_id FROM jobs
84
+
85
+ -> LEFT JOIN assigns ON assigns.job_id = jobs.id
86
+
87
+ -> GROUP BY jobs.id
88
+
89
+ -> HAVING (SUM(1) < 3 AND assigned_user_id != 2) OR assigned_user_id IS NULL
90
+
91
+ -> ) jobs;
92
+
93
+ +----+------+
94
+
95
+ | id | name |
96
+
97
+ +----+------+
98
+
99
+ | 2 | Job2 |
100
+
101
+ | 4 | Job4 |
102
+
103
+ +----+------+
104
+
105
+ 2 rows in set (0.00 sec)
106
+
107
+
108
+
109
+
110
+
111
+ mysql> EXPLAIN
112
+
113
+ -> SELECT jobs.id, jobs.name FROM (
114
+
115
+ -> SELECT jobs.*, assigns.user_id AS assigned_user_id FROM jobs
116
+
117
+ -> LEFT JOIN assigns ON assigns.job_id = jobs.id
118
+
119
+ -> GROUP BY jobs.id
120
+
121
+ -> HAVING (SUM(1) < 3 AND assigned_user_id != 2) OR assigned_user_id IS NULL
122
+
123
+ -> ) jobs;
124
+
125
+ +----+-------------+------------+-------+---------------+---------+---------+-----------------+------+-------------+
126
+
127
+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
128
+
129
+ +----+-------------+------------+-------+---------------+---------+---------+-----------------+------+-------------+
130
+
131
+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 4 | NULL |
132
+
133
+ | 2 | DERIVED | jobs | index | PRIMARY | PRIMARY | 4 | NULL | 4 | NULL |
134
+
135
+ | 2 | DERIVED | assigns | ref | job_id | job_id | 4 | example.jobs.id | 1 | Using index |
136
+
137
+ +----+-------------+------------+-------+---------------+---------+---------+-----------------+------+-------------+
138
+
139
+ ```

1

補足

2017/06/07 04:19

投稿

mpyw
mpyw

スコア5223

test CHANGED
@@ -50,6 +50,10 @@
50
50
 
51
51
 
52
52
 
53
+ アサイン数3未満でユーザ2に対するアサインがまだ付いていないジョブを選択
54
+
55
+
56
+
53
57
  ```sql
54
58
 
55
59
  SELECT jobs.id, jobs.name FROM (
@@ -60,7 +64,7 @@
60
64
 
61
65
  GROUP BY jobs.id
62
66
 
63
- HAVING (SUM(1) < 3 AND assigned_user_id != 2) OR assigned_user_id IS NULL
67
+ HAVING (SUM(1) < 3 AND assigned_user_id != 2) OR assigned_user_id IS NULL
64
68
 
65
69
  ) jobs;
66
70