|
1
|
|
|
<?php |
|
2
|
|
|
namespace Ivory\Showcase; |
|
3
|
|
|
|
|
4
|
|
|
use Ivory\Data\DbTableRelation; |
|
5
|
|
|
use Ivory\Data\DbViewRelation; |
|
6
|
|
|
use Ivory\Data\StatementRelation; |
|
7
|
|
|
|
|
8
|
|
|
$personRel = new DbTableRelation('person'); |
|
9
|
|
|
$availPersonRel = new DbViewRelation('vw_avail_person', 'datatemplate'); |
|
10
|
|
|
|
|
11
|
|
|
$employeeIds = [1, 432, 92, 32]; |
|
12
|
|
|
$roomIds = [3, 4, 5]; |
|
13
|
|
|
|
|
14
|
|
|
$sql = new StatementRelation( |
|
15
|
|
|
"SELECT avail.room_id, %ln@p, 'the % sign is not expanded inside string literals' |
|
16
|
|
|
FROM % avail |
|
17
|
|
|
JOIN % p ON p.id = avail.person_id |
|
18
|
|
|
WHERE avail.room_id IN (%l) AND p.id IN (%l) |
|
19
|
|
|
ORDER BY avail.room_id, p.lastname, p.firstname", |
|
20
|
|
|
$personRel->project('id', 'firstname', 'lastname'), // limit the person relation only to these attributes |
|
21
|
|
|
$availPersonRel, // the real power comes in: any relation shall be applicable in the query |
|
22
|
|
|
$personRel, |
|
23
|
|
|
$roomIds, $employeeIds |
|
24
|
|
|
); |
|
25
|
|
|
// "%l" generally means a list of something; another formatting token may follow, specifying the list subtype |
|
26
|
|
|
// the list subtype may be omitted to use the identity conversion, just like for any argument |
|
27
|
|
|
// here, "%ln" denotes a list of identifiers; in this case, it is fed with a relation, which causes to take all its (three) attributes |
|
28
|
|
|
// the "@" sign denotes aliasing the relation of attributes |
|
29
|
|
|
foreach ($sql as $row) { |
|
30
|
|
|
printf("%s %s available for room #%d\n", $row['firstname'], $row['lastname'], $row['room_id']); |
|
31
|
|
|
} |
|
32
|
|
|
|
|
33
|
|
|
|
|
34
|
|
|
class PersonRole extends DbTableRelation |
|
35
|
|
|
{ |
|
36
|
|
|
public function __construct() |
|
37
|
|
|
{ |
|
38
|
|
|
parent::__construct('person_role'); |
|
39
|
|
|
} |
|
40
|
|
|
} |
|
41
|
|
|
|
|
42
|
|
|
class Role extends DbTableRelation |
|
43
|
|
|
{ |
|
44
|
|
|
const ADMIN = 1; |
|
45
|
|
|
|
|
46
|
|
|
public function __construct() |
|
47
|
|
|
{ |
|
48
|
|
|
parent::__construct('role'); |
|
49
|
|
|
} |
|
50
|
|
|
} |
|
51
|
|
|
|
|
52
|
|
|
$adminInfo = $personRel->project([ |
|
53
|
|
|
'id', |
|
54
|
|
|
'fname' => 'firstname', |
|
55
|
|
|
'lastname' => 'UPPER(lastname)', |
|
56
|
|
|
'is_admin' => new Statement("EXISTS(SELECT 1 FROM %n WHERE role = %)", PersonRole::class, Role::ADMIN), // unfortunately, PHP implements ::class as mere string, not an object of a special class, thus, the explicit %n must be used |
|
57
|
|
|
]); |
|
58
|
|
|
foreach ($adminInfo as $row) { |
|
59
|
|
|
echo $row['fname'] . ' ' . $row['lastname']; |
|
60
|
|
|
if ($row['is_admin']) { |
|
61
|
|
|
echo ' is admin'; |
|
62
|
|
|
} |
|
63
|
|
|
echo "\n"; |
|
64
|
|
|
} |
|
65
|
|
|
|
ondrej-bouda /
ivory
