SQL Joins
Ik lees alle tickets uit vanuit tabel ticket, dit zijn de basis gegevens maar zonder de gebruikersnamen.
Om deze terug te vinden is er een tabel: relationship.
De waarde r.target_id die volgt uit t.base_id & r.source_id overeenkomen en r.relationshiptype = '15E577A3-6BF9-6713-4EAC-BA5A5B7C4722' is u.base_id waar ik dan weer de username wil weten, dit is dan de affected user. Maar er is ook nog r.relationshiptype = 'DF738111-C7A2-B450-5872-C5F3B927481A' en de username die daaruit volgt is de assigned user.
Nog een probleem is dat hij enkel de meest recente relationshiptype mag nemen die overeenkomt.
Is dit mogelijk om in 1 query uit te voeren?
Tot zover ben ik:
$sql = "SELECT t.ticket_id, t.baseid, t.title, t.createddate, r.target_id, r.relationshiptype FROM scsm_ticket t
RIGHT JOIN scsm_relationship r ON t.base_id = r.source_id && (r.relationshiptype = 'DF738111-C7A2-B450-5872-C5F3B927481A' OR r.relationshiptype = '15E577A3-6BF9-6713-4EAC-BA5A5B7C4722')
WHERE
t.status NOT IN ('Closed', 'Cancelled', 'Closed by user', 'Failed', 'Completed', 'Resolved', '')
&&
(t.ticket_type = 'Service Request' or t.ticket_type = 'Incident') GROUP BY t.ticket_id";
Opbouw tabellen:
Tabel ticket t
- base_id
- ticket_id
- title
- createddate
- status
- ticket_type
Tabel relationship r
- relationship_id
- source_id
- target_id
- relationship_type
Tabel user u
- base_id
- username
Maar wat voor systeem is dit? En ik denk dat twee queries een stuk makkelijker is.
Ter info: het is een geëxporteerde mssql System Center Service Manager database
Code (php)
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
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
<?php
include("../inc/db.php");
$sql = "SELECT t.ticket_id, t.baseid, t.title, t.createddate FROM scsm_ticket t
WHERE t.status NOT IN ('Closed', 'Cancelled', 'Closed by user', 'Failed', 'Completed', 'Resolved', '') && (t.ticket_type = 'Service Request' or t.ticket_type = 'Incident') GROUP BY t.ticket_id";
$result = mysqli_query($db, $sql);
if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result)) {
$baseid = $row['baseid'];
// Search for assigned user
$get_assigned_user_sql = mysqli_query($db, "SELECT r.target_id, u.username FROM scsm_relationship r
LEFT JOIN scsm_user u ON r.target_id = u.base_id
WHERE r.relationshiptype = '15E577A3-6BF9-6713-4EAC-BA5A5B7C4722' && r.source_id = '$baseid' ORDER BY r.lastmodified DESC LIMIT 1");
if (mysqli_num_rows($get_assigned_user_sql) > 0) {
while ($assigned_user_id = mysqli_fetch_assoc($get_assigned_user_sql)) {
echo 'assigned to: ' . $assigned_user_id['username'] . '<br>';
}
} else {
echo 'assigned to: <br>';
}
// Search for affected user
$get_affected_user_sql = mysqli_query($db, "SELECT r.target_id, u.username FROM scsm_relationship r
LEFT JOIN scsm_user u ON r.target_id = u.base_id
WHERE r.relationshiptype = 'DFF9BE66-38B0-B6D6-6144-A412A3EBD4CE' && r.source_id = '$baseid' ORDER BY r.lastmodified DESC LIMIT 1");
if (mysqli_num_rows($get_affected_user_sql) > 0) {
while ($affected_user_id = mysqli_fetch_assoc($get_affected_user_sql)) {
echo 'affected user: ' . $affected_user_id['username'] . '<br>';
}
} else {
echo 'affected user: <br>';
}
echo 'createddate: ' . $row['createddate'] . '<br>';
echo 'ticket_id: ' . $row['ticket_id'] . '<br>';
echo 'title: ' . $row['title'] . '<br>';
echo '<br>';
}
}
?>
include("../inc/db.php");
$sql = "SELECT t.ticket_id, t.baseid, t.title, t.createddate FROM scsm_ticket t
WHERE t.status NOT IN ('Closed', 'Cancelled', 'Closed by user', 'Failed', 'Completed', 'Resolved', '') && (t.ticket_type = 'Service Request' or t.ticket_type = 'Incident') GROUP BY t.ticket_id";
$result = mysqli_query($db, $sql);
if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result)) {
$baseid = $row['baseid'];
// Search for assigned user
$get_assigned_user_sql = mysqli_query($db, "SELECT r.target_id, u.username FROM scsm_relationship r
LEFT JOIN scsm_user u ON r.target_id = u.base_id
WHERE r.relationshiptype = '15E577A3-6BF9-6713-4EAC-BA5A5B7C4722' && r.source_id = '$baseid' ORDER BY r.lastmodified DESC LIMIT 1");
if (mysqli_num_rows($get_assigned_user_sql) > 0) {
while ($assigned_user_id = mysqli_fetch_assoc($get_assigned_user_sql)) {
echo 'assigned to: ' . $assigned_user_id['username'] . '<br>';
}
} else {
echo 'assigned to: <br>';
}
// Search for affected user
$get_affected_user_sql = mysqli_query($db, "SELECT r.target_id, u.username FROM scsm_relationship r
LEFT JOIN scsm_user u ON r.target_id = u.base_id
WHERE r.relationshiptype = 'DFF9BE66-38B0-B6D6-6144-A412A3EBD4CE' && r.source_id = '$baseid' ORDER BY r.lastmodified DESC LIMIT 1");
if (mysqli_num_rows($get_affected_user_sql) > 0) {
while ($affected_user_id = mysqli_fetch_assoc($get_affected_user_sql)) {
echo 'affected user: ' . $affected_user_id['username'] . '<br>';
}
} else {
echo 'affected user: <br>';
}
echo 'createddate: ' . $row['createddate'] . '<br>';
echo 'ticket_id: ' . $row['ticket_id'] . '<br>';
echo 'title: ' . $row['title'] . '<br>';
echo '<br>';
}
}
?>
Gewijzigd op 09/03/2019 20:58:31 door Tom Joppen
Maar ik zou inderdaad alles kunnen opsplitsen in andere tabellen bij het inlezen, dat maakt het zoeken in ieder geval een heel pak sneller.