-
-
Notifications
You must be signed in to change notification settings - Fork 277
Expand file tree
/
Copy pathexplorer.texy
More file actions
912 lines (640 loc) Β· 35.3 KB
/
explorer.texy
File metadata and controls
912 lines (640 loc) Β· 35.3 KB
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
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
Database Explorer
*****************
<div class=perex>
Explorer offers an intuitive and efficient way to work with your database. It automatically handles table relationships and optimizes queries, allowing you to focus on your application logic. It works immediately without configuration. If you need full control over SQL queries, you can use the [SQL way |SQL way].
- Working with data is natural and easy to understand
- Generates optimized SQL queries that fetch only the necessary data
- Provides easy access to related data without the need to write JOIN queries
- Works immediately without any configuration or entity generation
</div>
Working with the Explorer begins by calling the `table()` method on the [api:Nette\Database\Explorer] object (see [Connection and Configuration |guide#Connection and Configuration] for details on setting up the database connection):
```php
$books = $explorer->table('book'); // 'book' is the table name
```
The method returns a [Selection |api:Nette\Database\Table\Selection] object, which represents an SQL query. Additional methods can be chained to this object for filtering and sorting results. The query is assembled and executed only when the data is requested, for example, by iterating with `foreach`. Each row is represented by an [ActiveRow |api:Nette\Database\Table\ActiveRow] object:
```php
foreach ($books as $book) {
echo $book->title; // outputs the 'title' column
echo $book->author_id; // outputs the 'author_id' column
}
```
Explorer greatly simplifies working with [table relationships |#Relationships Between Tables]. The following example shows how easily we can output data from related tables (books and their authors). Notice that no JOIN queries need to be written; Nette generates them for us:
```php
$books = $explorer->table('book');
foreach ($books as $book) {
echo 'Book: ' . $book->title;
echo 'Author: ' . $book->author->name; // creates a JOIN to the 'author' table
}
```
Nette Database Explorer optimizes queries for maximum efficiency. The above example performs only two SELECT queries, regardless of whether we process 10 or 10,000 books.
Additionally, Explorer tracks which columns are used in the code and fetches only those from the database, saving further performance. This behavior is fully automatic and adaptive. If you later modify the code to use additional columns, Explorer automatically adjusts the queries. You donβt need to configure anything or think about which columns will be needed β leave that to Nette.
Filtering and Sorting
=====================
The `Selection` class provides methods for filtering and sorting data selections.
.[language-php]
| `where($condition, ...$params)` | Adds a WHERE condition. Multiple conditions are combined using AND |
| `whereOr(array $conditions)` | Adds a group of WHERE conditions combined using OR |
| `wherePrimary($value)` | Adds a WHERE condition based on the primary key |
| `order($columns, ...$params)` | Sets sorting with ORDER BY |
| `select($columns, ...$params)` | Specifies which columns to fetch |
| `limit($limit, $offset = null)` | Limits the number of rows (LIMIT) and optionally sets OFFSET |
| `page($page, $itemsPerPage, &$total = null)` | Sets pagination |
| `group($columns, ...$params)` | Groups rows (GROUP BY) |
| `having($condition, ...$params)`| Adds a HAVING condition for filtering grouped rows |
Methods can be chained (the so-called [fluent interface |nette:introduction-to-object-oriented-programming#Fluent Interfaces]): `$table->where(...)->order(...)->limit(...)`.
In these methods, you can also use special notations for accessing [data from related tables |#Querying Through Related Tables].
Escaping and Identifiers
------------------------
The methods automatically escape parameters and quote identifiers (table and column names), preventing SQL injection. To ensure proper operation, a few rules must be followed:
- Write keywords, function names, procedures, etc., in **uppercase**.
- Write column and table names in **lowercase**.
- Always pass strings using **parameters**.
```php
where('name = ' . $name); // CRITICAL VULNERABILITY: SQL injection
where('name LIKE "%search%"'); // WRONG: complicates automatic quoting
where('name LIKE ?', '%search%'); // CORRECT: value passed as a parameter
where('name like ?', $name); // WRONG: generates: `name` `like` ?
where('name LIKE ?', $name); // CORRECT: generates: `name` LIKE ?
where('LOWER(name) = ?', $value);// CORRECT: LOWER(`name`) = ?
```
where(string|array $condition, ...$parameters): static .[method]
----------------------------------------------------------------
Filters results using WHERE conditions. Its strength lies in intelligently handling various value types and automatically selecting appropriate SQL operators.
Basic usage:
```php
$table->where('id', $value); // WHERE `id` = 123
$table->where('id > ?', $value); // WHERE `id` > 123
$table->where('id = ? OR name = ?', $id, $name); // WHERE `id` = 1 OR `name` = 'Jon Snow'
```
Thanks to automatic detection of suitable operators, you donβt need to handle various special cases β Nette resolves them for you:
```php
$table->where('id', 1); // WHERE `id` = 1
$table->where('id', null); // WHERE `id` IS NULL
$table->where('id', [1, 2, 3]); // WHERE `id` IN (1, 2, 3)
// You can also use the placeholder ? without an operator:
$table->where('id ?', 1); // WHERE `id` = 1
```
The method correctly handles negative conditions and empty arrays:
```php
$table->where('id', []); // WHERE `id` IS NULL AND FALSE -- finds nothing
$table->where('id NOT', []); // WHERE `id` IS NULL OR TRUE -- finds everything
$table->where('NOT (id ?)', []); // WHERE NOT (`id` IS NULL AND FALSE) -- finds everything
// $table->where('NOT id ?', $ids); // WARNING: This syntax is not supported
```
You can also pass the result of another table query as a parameter, creating a subquery:
```php
// WHERE `id` IN (SELECT `id` FROM `tableName`)
$table->where('id', $explorer->table($tableName));
// WHERE `id` IN (SELECT `col` FROM `tableName`)
$table->where('id', $explorer->table($tableName)->select('col'));
```
Conditions can also be passed as an array, whose items are combined using AND:
```php
// WHERE (`price_final` < `price_original`) AND (`stock_count` > `min_stock`)
$table->where([
'price_final < price_original',
'stock_count > min_stock',
]);
```
In the array, you can use key => value pairs, and Nette will again automatically choose the correct operators:
```php
// WHERE (`status` = 'active') AND (`id` IN (1, 2, 3))
$table->where([
'status' => 'active',
'id' => [1, 2, 3],
]);
```
In the array, you can combine SQL expressions with placeholders and multiple parameters. This is suitable for complex conditions with precisely defined operators:
```php
// WHERE (`age` > 18) AND (ROUND(`score`, 2) > 75.5)
$table->where([
'age > ?' => 18,
'ROUND(score, ?) > ?' => [2, 75.5], // two parameters are passed as an array
]);
```
Multiple calls to `where()` automatically combine conditions using AND.
whereOr(array $parameters): static .[method]
--------------------------------------------
Similar to `where()`, this adds conditions, but combines them using OR:
```php
// WHERE (`status` = 'active') OR (`deleted` = 1)
$table->whereOr([
'status' => 'active',
'deleted' => true,
]);
```
More complex expressions can also be used here:
```php
// WHERE (`price` > 1000) OR (`price_with_tax` > 1500)
$table->whereOr([
'price > ?' => 1000,
'price_with_tax > ?' => 1500,
]);
```
wherePrimary(mixed $key): static .[method]
------------------------------------------
Adds a condition for the table's primary key:
```php
// WHERE `id` = 123
$table->wherePrimary(123);
// WHERE `id` IN (1, 2, 3)
$table->wherePrimary([1, 2, 3]);
```
If the table has a composite primary key (e.g., `foo_id`, `bar_id`), pass it as an array:
```php
// WHERE `foo_id` = 1 AND `bar_id` = 5
$table->wherePrimary(['foo_id' => 1, 'bar_id' => 5])->fetch();
// WHERE (`foo_id`, `bar_id`) IN ((1, 5), (2, 3))
$table->wherePrimary([
['foo_id' => 1, 'bar_id' => 5],
['foo_id' => 2, 'bar_id' => 3],
])->fetchAll();
```
order(string $columns, ...$parameters): static .[method]
--------------------------------------------------------
Specifies the order in which rows are returned. You can sort by one or more columns, in ascending or descending order, or according to a custom expression:
```php
$table->order('created'); // ORDER BY `created`
$table->order('created DESC'); // ORDER BY `created` DESC
$table->order('priority DESC, created'); // ORDER BY `priority` DESC, `created`
$table->order('status = ? DESC', 'active'); // ORDER BY `status` = 'active' DESC
```
select(string $columns, ...$parameters): static .[method]
---------------------------------------------------------
Specifies the columns to be returned from the database. By default, Nette Database Explorer returns only the columns that are actually used in the code. Use the `select()` method when you need to retrieve specific expressions:
```php
// SELECT *, DATE_FORMAT(`created_at`, "%d.%m.%Y") AS `formatted_date`
$table->select('*, DATE_FORMAT(created_at, ?) AS formatted_date', '%d.%m.%Y');
```
Aliases defined using `AS` are then accessible as properties of the `ActiveRow` object:
```php
foreach ($table as $row) {
echo $row->formatted_date; // access the alias
}
```
limit(?int $limit, ?int $offset = null): static .[method]
---------------------------------------------------------
Limits the number of returned rows (LIMIT) and optionally allows setting an offset:
```php
$table->limit(10); // LIMIT 10 (returns the first 10 rows)
$table->limit(10, 20); // LIMIT 10 OFFSET 20
```
For pagination, it is more appropriate to use the `page()` method.
page(int $page, int $itemsPerPage, &$numOfPages = null): static .[method]
-------------------------------------------------------------------------
Facilitates pagination of results. It accepts the page number (starting from 1) and the number of items per page. Optionally, you can pass a reference to a variable where the total number of pages will be stored:
```php
$numOfPages = null;
$table->page(page: 3, itemsPerPage: 10, numOfPages: $numOfPages);
echo "Total pages: $numOfPages";
```
group(string $columns, ...$parameters): static .[method]
--------------------------------------------------------
Groups rows according to the specified columns (GROUP BY). It is typically used in conjunction with aggregate functions:
```php
// Counts the number of products in each category
$table->select('category_id, COUNT(*) AS count')
->group('category_id');
```
having(string $having, ...$parameters): static .[method]
--------------------------------------------------------
Sets a condition for filtering grouped rows (HAVING). It can be used in conjunction with the `group()` method and aggregate functions:
```php
// Finds categories that have more than 100 products
$table->select('category_id, COUNT(*) AS count')
->group('category_id')
->having('count > ?', 100);
```
Reading Data
============
For reading data from the database, several useful methods are available:
.[language-php]
| `foreach ($table as $key => $row)` | Iterates through all rows, `$key` is the primary key value, `$row` is an ActiveRow object |
| `$row = $table->get($key)` | Returns a single row by primary key |
| `$row = $table->fetch()` | Returns the current row and advances the pointer to the next one |
| `$array = $table->fetchPairs()` | Creates an associative array from the results |
| `$array = $table->fetchAll()` | Returns all rows as an array |
| `count($table)` | Returns the number of rows in the Selection object |
The [ActiveRow |api:Nette\Database\Table\ActiveRow] object is read-only. This means you cannot change the values of its properties. This restriction ensures data consistency and prevents unexpected side effects. Data is loaded from the database, and any changes should be made explicitly and in a controlled manner.
`foreach` - Iterating Through All Rows
--------------------------------------
The easiest way to execute a query and retrieve rows is by iterating using a `foreach` loop. It automatically executes the SQL query.
```php
$books = $explorer->table('book');
foreach ($books as $key => $book) {
// $key is the primary key value, $book is ActiveRow
echo "$book->title ({$book->author->name})";
}
```
get($key): ?ActiveRow .[method]
-------------------------------
Executes the SQL query and returns the row by primary key, or `null` if it doesn't exist.
```php
$book = $explorer->table('book')->get(123); // returns ActiveRow with ID 123 or null
if ($book) {
echo $book->title;
}
```
fetch(): ?ActiveRow .[method]
-----------------------------
Returns the current row and advances the internal pointer to the next one. If there are no more rows, it returns `null`.
```php
$books = $explorer->table('book');
while ($book = $books->fetch()) {
$this->processBook($book);
}
```
fetchPairs(string|int|null $key = null, string|int|null $value = null): array .[method]
---------------------------------------------------------------------------------------
Returns results as an associative array. The first argument specifies the column name to be used as the key in the array, the second argument specifies the column name to be used as the value:
```php
$authors = $explorer->table('author')->fetchPairs('id', 'name');
// [1 => 'John Doe', 2 => 'Jane Doe', ...]
```
If only the first parameter is provided, the value will be the entire row, i.e., the `ActiveRow` object:
```php
$authors = $explorer->table('author')->fetchPairs('id');
// [1 => ActiveRow(id: 1, ...), 2 => ActiveRow(id: 2, ...), ...]
```
In case of duplicate keys, the value from the last row is used. When using `null` as the key, the array will be indexed numerically starting from zero (then no collisions occur):
```php
$authors = $explorer->table('author')->fetchPairs(null, 'name');
// [0 => 'John Doe', 1 => 'Jane Doe', ...]
```
fetchPairs(Closure $callback): array .[method]
----------------------------------------------
Alternatively, you can pass a callback as a parameter, which will return either a single value or a key-value pair for each row.
```php
$titles = $explorer->table('book')
->fetchPairs(fn($row) => "$row->title ({$row->author->name})");
// ['First Book (John Novak)', ...]
// The callback can also return an array with a key & value pair:
$titles = $explorer->table('book')
->fetchPairs(fn($row) => [$row->title, $row->author->name]);
// ['First Book' => 'John Novak', ...]
```
fetchAll(): array .[method]
---------------------------
Returns all rows as an associative array of `ActiveRow` objects, where the keys are the primary key values.
```php
$allBooks = $explorer->table('book')->fetchAll();
// [1 => ActiveRow(id: 1, ...), 2 => ActiveRow(id: 2, ...), ...]
```
count(): int .[method]
----------------------
The `count()` method without a parameter returns the number of rows in the `Selection` object:
```php
$table->where('category', 1);
$count = $table->count();
$count = count($table); // alternative
```
Note: `count()` with a parameter performs the COUNT aggregate function in the database, see below.
ActiveRow::toArray(): array .[method]
-------------------------------------
Converts the `ActiveRow` object to an associative array, where keys are column names and values are the corresponding data.
```php
$book = $explorer->table('book')->get(1);
$bookArray = $book->toArray();
// $bookArray will be ['id' => 1, 'title' => '...', 'author_id' => ..., ...]
```
Aggregation
===========
The `Selection` class provides methods for easily performing aggregate functions (COUNT, SUM, MIN, MAX, AVG, etc.).
.[language-php]
| `count($expr)` | Counts the number of rows |
| `min($expr)` | Returns the minimum value in a column |
| `max($expr)` | Returns the maximum value in a column |
| `sum($expr)` | Returns the sum of values in a column |
| `aggregation($function)` | Allows any aggregation function, such as `AVG()` or `GROUP_CONCAT()` |
count(string $expr): int .[method]
----------------------------------
Executes an SQL query with the COUNT function and returns the result. The method is used to determine how many rows match a certain condition:
```php
$count = $table->count('*'); // SELECT COUNT(*) FROM `table`
$count = $table->count('DISTINCT column'); // SELECT COUNT(DISTINCT `column`) FROM `table`
```
Note: [#count()] without a parameter only returns the number of rows in the `Selection` object.
min(string $expr) and max(string $expr) .[method]
-------------------------------------------------
The `min()` and `max()` methods return the minimum and maximum values in the specified column or expression:
```php
// SELECT MAX(`price`) FROM `products` WHERE `active` = 1
$maxPrice = $products->where('active', true)
->max('price');
```
sum(string $expr): int .[method]
--------------------------------
Returns the sum of values in the specified column or expression:
```php
// SELECT SUM(`price` * `items_in_stock`) FROM `products` WHERE `active` = 1
$totalPrice = $products->where('active', true)
->sum('price * items_in_stock');
```
aggregation(string $function, ?string $groupFunction = null): mixed .[method]
-----------------------------------------------------------------------------
Allows performing any aggregate function.
```php
// average price of products in a category
$avgPrice = $products->where('category_id', 1)
->aggregation('AVG(price)');
// joins product tags into a single string
$tags = $products->where('id', 1)
->aggregation('GROUP_CONCAT(tag.name) AS tags')
->fetch()
->tags;
```
If we need to aggregate results that already result from some aggregate function and grouping (e.g., `SUM(value)` over grouped rows), we specify the aggregate function to be applied to these intermediate results as the second argument:
```php
// Calculates the total price of products in stock for individual categories and then sums these prices together.
$totalPrice = $products->select('category_id, SUM(price * stock) AS category_total')
->group('category_id')
->aggregation('SUM(category_total)', 'SUM');
```
In this example, we first calculate the total price of products in each category (`SUM(price * stock) AS category_total`) and group the results by `category_id`. Then, we use `aggregation('SUM(category_total)', 'SUM')` to sum these intermediate totals `category_total`. The second argument `'SUM'` specifies that the SUM function should be applied to the intermediate results.
Insert, Update & Delete
=======================
Nette Database Explorer simplifies inserting, updating, and deleting data. All the methods mentioned throw a `Nette\Database\DriverException` in case of an error.
Selection::insert(iterable $data) .[method]
-------------------------------------------
Inserts new records into the table.
**Inserting a single record:**
Pass the new record as an associative array or iterable object (like `ArrayHash` used in [forms |forms:]), where keys correspond to column names in the table.
If the table has a defined primary key, the method returns an `ActiveRow` object, which is reloaded from the database to reflect any changes made at the database level (triggers, default column values, auto-increment column calculations). This ensures data consistency, and the object always contains the current data from the database. If it doesn't have a unique primary key, it returns the passed data as an array.
```php
$row = $explorer->table('users')->insert([
'name' => 'John Doe',
'email' => '[email protected]',
]);
// $row is an instance of ActiveRow and contains the complete data of the inserted row,
// including the automatically generated ID and any changes made by triggers
echo $row->id; // Outputs the ID of the newly inserted user
echo $row->created_at; // Outputs the creation time if set by a trigger
```
**Inserting multiple records at once:**
The `insert()` method allows inserting multiple records using a single SQL query. In this case, it returns the number of inserted rows.
```php
$insertedRows = $explorer->table('users')->insert([
[
'name' => 'John',
'year' => 1994,
],
[
'name' => 'Jack',
'year' => 1995,
],
]);
// INSERT INTO `users` (`name`, `year`) VALUES ('John', 1994), ('Jack', 1995)
// $insertedRows will be 2
```
A `Selection` object with a data selection can also be passed as a parameter.
```php
$newUsers = $explorer->table('potential_users')
->where('approved', 1)
->select('name, email');
$insertedRows = $explorer->table('users')->insert($newUsers);
```
**Inserting special values:**
We can also pass files, `DateTime` objects, or SQL literals as values:
```php
$explorer->table('users')->insert([
'name' => 'John',
'created_at' => new DateTime, // converts to database format
'avatar' => fopen('image.jpg', 'rb'), // inserts binary content of the file
'uuid' => $explorer::literal('UUID()'), // calls the UUID() function
]);
```
Selection::update(iterable $data): int .[method]
------------------------------------------------
Updates rows in the table according to the specified filter. Returns the number of actually modified rows.
Pass the columns to be changed as an associative array or iterable object (like `ArrayHash` used in [forms |forms:]), where keys correspond to column names in the table:
```php
$affected = $explorer->table('users')
->where('id', 10)
->update([
'name' => 'John Smith',
'year' => 1994,
]);
// UPDATE `users` SET `name` = 'John Smith', `year` = 1994 WHERE `id` = 10
```
To change numeric values, you can use the `+=` and `-=` operators:
```php
$explorer->table('users')
->where('id', 10)
->update([
'points+=' => 1, // increases the value of the 'points' column by 1
'coins-=' => 1, // decreases the value of the 'coins' column by 1
]);
// UPDATE `users` SET `points` = `points` + 1, `coins` = `coins` - 1 WHERE `id` = 10
```
Selection::delete(): int .[method]
----------------------------------
Deletes rows from the table according to the specified filter. Returns the number of deleted rows.
```php
$count = $explorer->table('users')
->where('id', 10)
->delete();
// DELETE FROM `users` WHERE `id` = 10
```
.[caution]
When calling `update()` or `delete()`, don't forget to use `where()` to specify the rows to be modified/deleted. If `where()` is not used, the operation will be performed on the entire table!
ActiveRow::update(iterable $data): bool .[method]
-------------------------------------------------
Updates data in the database row represented by the `ActiveRow` object. It accepts an iterable with data to be updated (keys are column names). To change numeric values, you can use the `+=` and `-=` operators:
After performing the update, the `ActiveRow` is automatically reloaded from the database to reflect any changes made at the database level (e.g., triggers). The method returns `true` only if a real data change occurred.
```php
$article = $explorer->table('article')->get(1);
$article->update([
'views += 1', // increments the view count
]);
echo $article->views; // Outputs the current view count
```
This method updates only one specific row in the database. For bulk updates of multiple rows, use the [#Selection::update()] method.
ActiveRow::delete(): int .[method]
----------------------------------
Deletes the row from the database represented by the `ActiveRow` object. Returns the number of deleted rows, which should be 1.
```php
$book = $explorer->table('book')->get(1);
$book->delete(); // Deletes the book with ID 1
```
This method deletes only one specific row in the database. For bulk deletion of multiple rows, use the [#Selection::delete()] method.
Relationships Between Tables
============================
In relational databases, data is divided into multiple tables and linked together using foreign keys. Nette Database Explorer provides a revolutionary way to work with these relationships β without writing JOIN queries and without needing to configure or generate anything.
To illustrate working with relationships, we'll use an example book database ([find it on GitHub |https://github.com/nette-examples/books]). In the database, we have tables:
- `author` β writers and translators (columns `id`, `name`, `web`, `born`)
- `book` β books (columns `id`, `author_id`, `translator_id`, `title`, `sequel_id`)
- `tag` β tags (columns `id`, `name`)
- `book_tag` β junction table between books and tags (columns `book_id`, `tag_id`)
[* db-schema-1-.webp *] *** Database structure used in examples .<>
In our example book database, we find several types of relationships (although the model is simplified compared to reality):
- **One-to-many (1:N)** β Each book **has one** author; an author can write **multiple** books.
- **Zero-to-many (0:N)** β A book **can have** a translator; a translator can translate **multiple** books.
- **Zero-to-one (0:1)** β A book **can have** a sequel.
- **Many-to-many (M:N)** β A book **can have several** tags, and a tag can be assigned to **several** books.
In these relationships, there is always a **parent table** and a **child table**. For example, in the relationship between authors and books, the `author` table is the parent, and the `book` table is the child β you can think of it as a book always "belonging" to an author. This is also reflected in the database structure: the child table `book` contains the foreign key `author_id`, which references the parent table `author`.
If we need to list books including their authors' names, we have two options. Either retrieve the data with a single SQL query using JOIN:
```sql
SELECT book.*, author.name FROM book LEFT JOIN author ON book.author_id = author.id;
```
Or retrieve the data in two steps β first the books, then their authors β and then assemble them in PHP:
```sql
SELECT * FROM book;
SELECT * FROM author WHERE id IN (1, 2, 3); -- IDs of authors from the selected books
```
The second approach is actually **more efficient**, although it might be surprising. Data is fetched only once and can be better utilized in the cache. This is precisely how Nette Database Explorer works β it handles everything under the hood and offers you an elegant API:
```php
$books = $explorer->table('book');
foreach ($books as $book) {
echo 'title: ' . $book->title;
echo 'written by: ' . $book->author->name; // $book->author is a record from the 'author' table
echo 'translated by: ' . $book->translator?->name;
}
```
Accessing the Parent Table
--------------------------
Accessing the parent table is straightforward. These are relationships like *a book has an author* or *a book may have a translator*. The related record is obtained via a property of the ActiveRow object β its name corresponds to the name of the foreign key column without the `_id` suffix:
```php
$book = $explorer->table('book')->get(1);
echo $book->author->name; // finds the author based on the author_id column
echo $book->translator?->name; // finds the translator based on the translator_id column
```
When accessing the `$book->author` property, Explorer looks in the `book` table for a column whose name contains the string `author` (i.e., `author_id`). Based on the value in this column, it loads the corresponding record from the `author` table and returns it as an `ActiveRow`. Similarly, `$book->translator` uses the `translator_id` column. Since the `translator_id` column can contain `null`, we use the nullsafe operator `?->` in the code.
An alternative approach is offered by the `ref()` method, which accepts two arguments: the name of the target table and the name of the joining column, and returns an `ActiveRow` instance or `null`:
```php
echo $book->ref('author', 'author_id')->name; // relationship to the author
echo $book->ref('author', 'translator_id')->name; // relationship to the translator
```
The `ref()` method is useful if property-based access cannot be used, for example, because the table contains a column with the same name (i.e., `author`). In other cases, using property-based access is recommended for better readability.
Explorer automatically optimizes database queries. When iterating through books in a loop and accessing their related records (authors, translators), Explorer does not generate a query for each book separately. Instead, it performs only **one SELECT query for each type of relationship**, significantly reducing the database load. For example:
```php
$books = $explorer->table('book');
foreach ($books as $book) {
echo $book->title . ': ';
echo $book->author->name;
echo $book->translator?->name;
}
```
This code executes only these three lightning-fast queries to the database:
```sql
SELECT * FROM `book`;
SELECT * FROM `author` WHERE (`id` IN (1, 2, 3)); -- IDs from the author_id column of selected books
SELECT * FROM `author` WHERE (`id` IN (2, 3)); -- IDs from the translator_id column of selected books
```
.[note]
The logic for finding the joining column is determined by the implementation of [Conventions |api:Nette\Database\Conventions]. We recommend using [DiscoveredConventions |api:Nette\Database\Conventions\DiscoveredConventions], which analyzes foreign keys and allows you to easily work with existing relationships between tables.
Accessing the Child Table
-------------------------
Accessing the child table works in the opposite direction. Now we ask *which books did this author write* or *which books did this translator translate*. For this type of query, we use the `related()` method, which returns a `Selection` with related records. Let's look at an example:
```php
$author = $explorer->table('author')->get(1);
// Outputs all books by the author
foreach ($author->related('book.author_id') as $book) {
echo "Wrote: $book->title";
}
// Outputs all books translated by the author
foreach ($author->related('book.translator_id') as $book) {
echo "Translated: $book->title";
}
```
The `related()` method accepts the join description as a single argument with dot notation or as two separate arguments:
```php
$author->related('book.translator_id'); // single argument
$author->related('book', 'translator_id'); // two arguments
```
Explorer can automatically detect the correct joining column based on the name of the parent table. In this case, it joins via the `book.author_id` column because the name of the source table is `author`:
```php
$author->related('book'); // uses book.author_id
```
If multiple possible connections exist, Explorer will throw an [AmbiguousReferenceKeyException |api:Nette\Database\Conventions\AmbiguousReferenceKeyException].
We can, of course, use the `related()` method when iterating through multiple records in a loop, and Explorer will automatically optimize the queries in this case as well:
```php
$authors = $explorer->table('author');
foreach ($authors as $author) {
echo $author->name . ' wrote:';
foreach ($author->related('book') as $book) {
echo $book->title;
}
}
```
This code generates only two lightning-fast SQL queries:
```sql
SELECT * FROM `author`;
SELECT * FROM `book` WHERE (`author_id` IN (1, 2, 3)); -- IDs of the selected authors
```
Many-to-Many Relationship
-------------------------
For a many-to-many (M:N) relationship, a **junction table** (in our case, `book_tag`) is needed, containing two foreign key columns (`book_id`, `tag_id`). Each of these columns refers to the primary key of one of the linked tables. To retrieve related data, we first get the records from the junction table using `related('book_tag')` and then proceed to the target data:
```php
$book = $explorer->table('book')->get(1);
// outputs the names of tags assigned to the book
foreach ($book->related('book_tag') as $bookTag) {
echo $bookTag->tag->name; // outputs the tag name via the junction table
}
$tag = $explorer->table('tag')->get(1);
// or the opposite way: outputs the names of books marked with this tag
foreach ($tag->related('book_tag') as $bookTag) {
echo $bookTag->book->title; // outputs the book title
}
```
Explorer again optimizes the SQL queries into an efficient form:
```sql
SELECT * FROM `book`;
SELECT * FROM `book_tag` WHERE (`book_tag`.`book_id` IN (1, 2, ...)); -- IDs of the selected books
SELECT * FROM `tag` WHERE (`tag`.`id` IN (1, 2, ...)); -- IDs of the tags found in book_tag
```
Querying Through Related Tables
-------------------------------
In the `where()`, `select()`, `order()`, and `group()` methods, you can use special notations to access columns from other tables. Explorer automatically creates the necessary JOINs.
**Dot notation** (`parent_table.column`) is used for 1:N relationships from the perspective of the child table:
```php
$books = $explorer->table('book');
// Finds books whose author's name starts with 'Jon'
$books->where('author.name LIKE ?', 'Jon%');
// Sorts books by author name descending
$books->order('author.name DESC');
// Outputs the book title and author name
$books->select('book.title, author.name');
```
**Colon notation** (`:child_table.column`) is used for 1:N relationships from the perspective of the parent table:
```php
$authors = $explorer->table('author');
// Finds authors who wrote a book with 'PHP' in the title
$authors->where(':book.title LIKE ?', '%PHP%');
// Counts the number of books for each author
$authors->select('*, COUNT(:book.id) AS book_count')
->group('author.id');
```
In the example above with colon notation (`:book.title`), the foreign key column is not specified. Explorer automatically detects the correct column based on the name of the parent table. In this case, it joins via the `book.author_id` column because the name of the source table is `author`. If multiple possible connections exist, Explorer will throw an [AmbiguousReferenceKeyException |api:Nette\Database\Conventions\AmbiguousReferenceKeyException].
The joining column can be explicitly specified in parentheses:
```php
// Finds authors who translated a book with 'PHP' in the title
$authors->where(':book(translator_id).title LIKE ?', '%PHP%');
```
Notations can be chained to access data across multiple tables:
```php
// Finds authors of books tagged with 'PHP'
$authors->where(':book:book_tag.tag.name', 'PHP')
->group('author.id');
```
Extending Conditions for JOIN
-----------------------------
The `joinWhere()` method extends the conditions specified when joining tables in SQL after the `ON` keyword.
Let's say we want to find books translated by a specific translator:
```php
// Finds books translated by a translator named 'David'
$books = $explorer->table('book')
->joinWhere('translator', 'translator.name', 'David');
// LEFT JOIN author translator ON book.translator_id = translator.id AND (translator.name = 'David')
```
In the `joinWhere()` condition, you can use the same constructs as in the `where()` method β operators, placeholders, arrays of values, or SQL expressions.
For more complex queries with multiple JOINs, you can define table aliases:
```php
$tags = $explorer->table('tag')
->joinWhere(':book_tag.book.author', 'book_author.born < ?', 1950)
->alias(':book_tag.book.author', 'book_author');
// LEFT JOIN `book_tag` ON `tag`.`id` = `book_tag`.`tag_id`
// LEFT JOIN `book` ON `book_tag`.`book_id` = `book`.`id`
// LEFT JOIN `author` `book_author` ON `book`.`author_id` = `book_author`.`id`
// AND (`book_author`.`born` < 1950)
```
Note that while the `where()` method adds conditions to the `WHERE` clause, the `joinWhere()` method extends the conditions in the `ON` clause when joining tables.