forked from doctrine/orm-documentation
-
Notifications
You must be signed in to change notification settings - Fork 1
/
query-builder.txt
408 lines (280 loc) · 18.1 KB
/
query-builder.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
++ The QueryBuilder
A `QueryBuilder` provides an API that is designed for conditionally constructing a DQL query in several steps.
It provides a set of classes and methods that is able to programatically build you queries, and also provides a fluent API.
This means that you can change between one methodology to the other as you want, and also pick one if you prefer.
+++ Constructing a new QueryBuilder object
The same way you build a normal Query, you build a `QueryBuilder` object, just providing the correct method name.
Here is an example how to build a `QueryBuilder` object:
[php]
// $em instanceof EntityManager
// example1: creating a QueryBuilder instance
$qb = $em->createQueryBuilder();
Once you created an instance of QueryBuilder, it provides a set of useful informative functions that you can use.
One good example is to inspect what type of object the `QueryBuilder` is.
[php]
// $qb instanceof QueryBuilder
// example2: retrieving type of QueryBuilder
echo $qb->getType(); // Prints: 0
There're currently 3 possible return values for `getType()`:
* `QueryBuilder::SELECT`, which returns value 0
* `QueryBuilder::DELETE`, returning value 1
* `QueryBuilder::UPDATE`, which returns value 2
It is possible to retrieve the associated `EntityManager` of the current `QueryBuilder`, its DQL and also a `Query` object when you finish building your DQL.
[php]
// $qb instanceof QueryBuilder
// example3: retrieve the associated EntityManager
$em = $qb->getEntityManager();
// example4: retrieve the DQL string of what was defined in QueryBuilder
$dql = $qb->getDql();
// example5: retrieve the associated Query object with the processed DQL
$q = $qb->getQuery();
Internally, `QueryBuilder` works with a DQL cache, which prevents multiple processment if called multiple times. Any changes that may affect the generated DQL actually modifies the state of `QueryBuilder` to a stage we call as STATE_DIRTY.
One `QueryBuilder`can be in two different state:
* `QueryBuilder::STATE_CLEAN`, which means DQL haven't been altered since last retrieval or nothing were added since its instantiation
* `QueryBuilder::STATE_DIRTY`, means DQL query must (and will) be processed on next retrieval
+++ Working with QueryBuilder
All helper methods in `QueryBuilder` relies actually on a single one: `add()`.
This method is the responsable to build every piece of DQL. It takes 3 parameters: `$dqlPartName`, `$dqlPart` and `$append` (default=false)
* `$dqlPartName`: Where the `$dqlPart` should be placed. Possible values: select, from, where, groupBy, having, orderBy
* `$dqlPart`: What should be placed in `$dqlPartName`. Accepts a string or any instance of `Doctrine\ORM\Query\Expr\*`
* `$append`: Optional flag (default=false) if the `$dqlPart` should override all previously defined items in `$dqlPartName` or not
-
[php]
// $qb instanceof QueryBuilder
// example6: how to define: "SELECT u FROM User u WHERE u.id = ? ORDER BY u.name ASC" using QueryBuilder string support
$qb->add('select', 'u')
->add('from', 'User u')
->add('where', 'u.id = ?1')
->add('orderBy', 'u.name ASC');
++++ Binding parameters to your query
Doctrine supports dynamic binding of parameters to your query, similar to preparing queries. You can use both strings and numbers as placeholders, although both have a slightly different syntax. Binding parameters can simply be achieved as follows:
[php]
// $qb instanceof QueryBuilder
// example6: how to define: "SELECT u FROM User u WHERE u.id = ? ORDER BY u.name ASC" using QueryBuilder string support
$qb->add('select', 'u')
->add('from', 'User u')
->add('where', 'u.id = ?1')
->add('orderBy', 'u.name ASC');
->setParameter(1, 100); // Sets ?1 to 100, and thus we will fetch a user with u.id = 100
You are not forced to enumerate your placeholders as the alternative syntax is available:
[php]
// $qb instanceof QueryBuilder
// example6: how to define: "SELECT u FROM User u WHERE u.id = ? ORDER BY u.name ASC" using QueryBuilder string support
$qb->add('select', 'u')
->add('from', 'User u')
->add('where', 'u.id = :identifier')
->add('orderBy', 'u.name ASC');
->setParameter('identifier', 100); // Sets :identifier to 100, and thus we will fetch a user with u.id = 100
Note that numeric placeholders start with a ? followed by a number while the named placeholders start with a : followed by a string.
If you've got several parameters to bind to your query, you can also use setParameters() instead of setParameter() with the following syntax:
[php]
// $qb instanceof QueryBuilder
// Query here...
$qb->setParameters(array(1 => 'value for ?1', 2 => 'value for ?2', 'whatever' => 'your value for :whatever'));
Getting already bound parameters is easy - simply use the abovementioned syntax with "getParameter()" or "getParameters()":
[php]
// $qb instanceof QueryBuilder
// See example above
$params = qb->getParameters(array(1, 2, 'whatever'));
// Equivalent to
$param = array($qb->getParameter(1), $qb->getParameter(2), $qb->getParameter('whatever'));
Note: If you try to get a parameter that was not bound yet, getParameter() simply returns NULL.
++++ Expr\* classes
When you call `add()` with string, it internally evaluates to an instance of `Doctrine\ORM\Query\Expr\Expr\*` class.
Here is the same query of example 6 written using `Doctrine\ORM\Query\Expr\Expr\*` classes:
[php]
// $qb instanceof QueryBuilder
// example7: how to define: "SELECT u FROM User u WHERE u.id = ? ORDER BY u.name ASC" using QueryBuilder using Expr\* instances
$qb->add('select', new Expr\Select(array('u')))
->add('from', new Expr\From('User', 'u'))
->add('where', new Expr\Comparison('u.id', '=', '?1'))
->add('orderBy', new Expr\OrderBy('u.name', 'ASC'));
Of course this is the hardest way to build a DQL query in Doctrine. To simplify some of these efforts, we introduce what we call as `Expr` helper class.
++++ The Expr class
To workaround most of the issues that `add()` method may cause, Doctrine created a class that can be considered as a helper for building queries.
This class is called `Expr`, which provides a set of useful static methods to help building queries:
[php]
// $qb instanceof QueryBuilder
// example8: QueryBuilder port of: "SELECT u FROM User u WHERE u.id = ? OR u.nickname LIKE ? ORDER BY u.surname DESC" using Expr class
$qb->add('select', $qb->expr()->select('u'))
->add('from', $qb->expr()->from('User', 'u'))
->add('where', $qb->expr()->orx(
$qb->expr()->eq('u.id', '?1'),
$qb->expr()->like('u.nickname', '?2')
))
->add('orderBy', $qb->expr()->orderBy('u.surname', 'ASC'));
Although it still sounds complex, the ability to programatically create conditions are the main feature of `Expr`.
Here it is a complete list of supported helper methods available:
[php]
class Expr
{
/** Base objects **/
// Example usage - $qb->expr()->select('u')
public function select($select = null); // Returns Expr\Select instance
// Example - $qb->expr()->from('User', 'u')
public function from($from, $alias); // Returns Expr\From instance
// Example - $qb->expr()->leftJoin('u.Phonenumbers', 'p', Expr\Join::ON, 'p.user_id = u.id AND p.country_code = 55');
// Example - $qb->expr()->leftJoin('u. Phonenumbers', 'p', 'ON', $qb->expr()->andx($qb->expr()->eq('p.user_id', 'u.id'), $qb->expr()->eq('p.country_code', '55'));
public function leftJoin($join, $alias, $conditionType = null, $condition = null); // Returns Expr\Join instance
// Example - $qb->expr()->innerJoin('u.Group', 'g', Expr\Join::WITH, 'g.manager_level = 100');
// Example - $qb->expr()->innerJoin('u.Group', 'g', 'WITH', $qb->expr()->eq('g.manager_level', '100'));
public function innerJoin($join, $alias, $conditionType = null, $condition = null); // Returns Expr\Join instance
// Example - $qb->expr()->orderBy('u.surname', 'ASC')->add('u.firstname', 'ASC')->...
public function orderBy($sort = null, $order = null); // Returns Expr\OrderBy instance
// Example - $qb->expr()->groupBy()->add('u.id')->...
public function groupBy($groupBy = null); // Returns Expr\GroupBy instance
/** Conditional objects **/
// Example - $qb->expr()->andx($cond1 [, $condN])->add(...)->...
public function andx($x = null); // Returns Expr\Andx instance
// Example - $qb->expr()->orx($cond1 [, $condN])->add(...)->...
public function orx($x = null); // Returns Expr\Orx instance
/** Comparison objects **/
// Example - $qb->expr()->eq('u.id', '?1') => u.id = ?1
public function eq($x, $y); // Returns Expr\Comparison instance
// Example - $qb->expr()->neq('u.id', '?1') => u.id <> ?1
public function neq($x, $y); // Returns Expr\Comparison instance
// Example - $qb->expr()->lt('u.id', '?1') => u.id < ?1
public function lt($x, $y); // Returns Expr\Comparison instance
// Example - $qb->expr()->lte('u.id', '?1') => u.id <= ?1
public function lte($x, $y); // Returns Expr\Comparison instance
// Example - $qb->expr()->gt('u.id', '?1') => u.id > ?1
public function gt($x, $y); // Returns Expr\Comparison instance
// Example - $qb->expr()->gte('u.id', '?1') => u.id >= ?1
public function gte($x, $y); // Returns Expr\Comparison instance
/** Arithmetic objects **/
// Example - $qb->expr()->prod('u.id', '2') => u.id * 2
public function prod($x, $y); // Returns Expr\Math instance
// Example - $qb->expr()->diff('u.id', '2') => u.id - 2
public function diff($x, $y); // Returns Expr\Math instance
// Example - $qb->expr()->sum('u.id', '2') => u.id + 2
public function sum($x, $y); // Returns Expr\Math instance
// Example - $qb->expr()->quot('u.id', '2') => u.id / 2
public function quot($x, $y); // Returns Expr\Math instance
/** Pseudo-function objects **/
// Example - $qb->expr()->exists($qb2->getDql())
public function exists($subquery); // Returns Expr\Func instance
// Example - $qb->expr()->all($qb2->getDql())
public function all($subquery); // Returns Expr\Func instance
// Example - $qb->expr()->some($qb2->getDql())
public function some($subquery); // Returns Expr\Func instance
// Example - $qb->expr()->any($qb2->getDql())
public function any($subquery); // Returns Expr\Func instance
// Example - $qb->expr()->not($qb->expr()->eq('u.id', '?1'))
public function not($restriction); // Returns Expr\Func instance
// Example - $qb->expr()->in('u.id', array(1, 2, 3))
// Make sure that you do NOT use something similar to $qb->expr()->in('value', array('stringvalue')) as this will cause Doctrine to throw an Exception.
// Instead, use $qb->expr()->in('value', array('?1')) and bind your parameter to ?1 (see section above)
public function in($x, $y); // Returns Expr\Func instance
// Example - $qb->expr()->notIn('u.id', '2')
public function notIn($x, $y); // Returns Expr\Func instance
// Example - $qb->expr()->like('u.firstname', $qb->expr()->literal('Gui%'))
public function like($x, $y); // Returns Expr\Comparison instance
// Example - $qb->expr()->between('u.id', '1', '10')
public function between($val, $x, $y); // Returns Expr\Func
/** Function objects **/
// Example - $qb->expr()->trim('u.firstname')
public function trim($x); // Returns Expr\Func
// Example - $qb->expr()->concat('u.firstname', $qb->expr()->concat(' ', 'u.lastname'))
public function concat($x, $y); // Returns Expr\Func
// Example - $qb->expr()->substr('u.firstname', 0, 1)
public function substr($x, $from, $len); // Returns Expr\Func
// Example - $qb->expr()->lower('u.firstname')
public function lower($x); // Returns Expr\Func
// Example - $qb->expr()->upper('u.firstname')
public function upper($x); // Returns Expr\Func
// Example - $qb->expr()->length('u.firstname')
public function length($x); // Returns Expr\Func
// Example - $qb->expr()->avg('u.age')
public function avg($x); // Returns Expr\Func
// Example - $qb->expr()->max('u.age')
public function max($x); // Returns Expr\Func
// Example - $qb->expr()->min('u.age')
public function min($x); // Returns Expr\Func
// Example - $qb->expr()->abs('u.currentBalance')
public function abs($x); // Returns Expr\Func
// Example - $qb->expr()->sqrt('u.currentBalance')
public function sqrt($x); // Returns Expr\Func
// Example - $qb->expr()->count('u.firstname')
public function count($x); // Returns Expr\Func
// Example - $qb->expr()->countDistinct('u.surname')
public function countDistinct($x); // Returns Expr\Func
}
++++ Helper methods
Until now it was described the hardcore level of creating queries. It may be useful to work that way for optimization purposes, but most of the time it is preferred to work higher level.
To simplify even more the way you build a query in Doctrine, we can take advantage of what we call as helper methods. For all base code, it has a set of useful methods to simplify programmer's life.
Illustrating how to work with it, here is the same example 6 written now using `QueryBuilder` helper methods:
[php]
// $qb instanceof QueryBuilder
// example9: how to define: "SELECT u FROM User u WHERE u.id = ?1 ORDER BY u.name ASC" using QueryBuilder helper methods
$qb->select('u')
->from('User', 'u')
->where('u.id = ?1')
->orderBy('u.name ASC');
`QueryBuilder` helper methods are considered the standard way to build DQL queries. Although it is supported, it should be avoided to use string based queries and greatly encouraged to use `$qb->expr()->*` methods.
Here is a converted example 8 to suggested standard way to build queries:
[php]
// $qb instanceof QueryBuilder
// example8: QueryBuilder port of: "SELECT u FROM User u WHERE u.id = ?1 OR u.nickname LIKE ?2 ORDER BY u.surname DESC" using QueryBuilder helper methods
$qb->select(array('u')) // string 'u' is converted to array internally
->from('User', 'u')
->where($qb->expr()->orx(
$qb->expr()->eq('u.id', '?1'),
$qb->expr()->like('u.nickname', '?2')
))
->orderBy('u.surname', 'ASC'));
Here is a complete list of helper methods in `QueryBuilder`:
[php]
class QueryBuilder
{
// Example - $qb->select('u')
// Example - $qb->select(array('u', 'p'))
// Example - $qb->select($qb->expr()->select('u', 'p'))
public function select($select = null);
// Example - $qb->delete('User', 'u')
public function delete($delete = null, $alias = null);
// Example - $qb->update('Group', 'g')
public function update($update = null, $alias = null);
// Example - $qb->set('u.firstName', $qb->expr()->literal('Arnold'))
// Example - $qb->set('u.numChilds', 'u.numChilds + ?1')
// Example - $qb->set('u.numChilds', $qb->expr()->sum('u.numChilds', '?1'))
public function set($key, $value);
// Example - $qb->from('Phonenumber', 'p')
public function from($from, $alias = null);
// Example - $qb->innerJoin('u.Group', 'g', Expr\Join::ON, $qb->expr()->and($qb->expr()->eq('u.group_id', 'g.id'), 'g.name = ?1'))
// Example - $qb->innerJoin('u.Group', 'g', 'ON', 'u.group_id = g.id AND g.name = ?1')
public function innerJoin($join, $alias = null, $conditionType = null, $condition = null);
// Example - $qb->leftJoin('u.Phonenumbers', 'p', Expr\Join::WITH, $qb->expr()->eq('p.area_code', 55))
// Example - $qb->leftJoin('u.Phonenumbers', 'p', 'WITH', 'p.area_code = 55')
public function leftJoin($join, $alias = null, $conditionType = null, $condition = null);
// NOTE: ->where() overrides all previously set conditions
//
// Example - $qb->where('u.firstName = ?1', $qb->expr()->eq('u.surname', '?2'))
// Example - $qb->where($qb->expr()->andx($qb->expr()->eq('u.firstName', '?1'), $qb->expr()->eq('u.surname', '?2')))
// Example - $qb->where('u.firstName = ?1 AND u.surname = ?2')
public function where($where);
// Example - $qb->andWhere($qb->expr()->orx($qb->expr()->lte('u.age', 40), 'u.numChild = 0'))
public function andWhere($where);
// Example - $qb->orWhere($qb->expr()->between('u.id', 1, 10));
public function orWhere($where);
// NOTE: -> groupBy() overrides all previously set grouping items
//
// Example - $qb->groupBy('u.id')
public function groupBy($groupBy);
// Example - $qb->addGroupBy('g.name')
public function addGroupBy($groupBy);
// NOTE: -> having() overrides all previously set having conditions
//
// Example - $qb->having('u.salary >= ?1')
// Example - $qb->having($qb->expr()->gte('u.salary', '?1'))
public function having($having);
// Example - $qb->andHaving($qb->expr()->gt($qb->expr()->count('u.numChild'), 0))
public function andHaving($having);
// Example - $qb->orHaving($qb->expr()->lte('g.managerLevel', '100'))
public function orHaving($having);
// NOTE: -> orderBy() overrides all previously set ordering items
//
// Example - $qb->orderBy('u.surname', 'DESC')
public function orderBy($sort, $order = null);
// Example - $qb->addOrderBy('u.firstName')
public function addOrderBy($sort, $order = null); // Default $order = 'ASC'
}