Query geeft slechte/foute resultaten
Ik ben een beginner op het gebied van SQL en tracht wat aanpassingen aan te brengen aan een bestaande site. We hebben daar 2 tabellen :
alarmeringen
1 id
2 timestamp
3 datetime
4 type
5 incident
6 message
7 zoektekst
8 city_id
labels
1 id
2 timestamp
3 messageid
4 incident
5 capcode
6 label
Nu wil ik uit beide tabellen een aantal kolommen opvragen op basis van labels.capcode, waarbij alarmeringen.id correleert met labels.messageid. Ik wil dan alle entries uit labels hebben die dezelfde messageid hebben als de opgegeven capcode, plus wat zaken uit alarmeringen. Ik heb dit in elkaar gezet, maar deze is erg traag en soms krijg ik helemaal geen resultaten (terwijl die er wel zouden moeten zijn) :
SELECT a.id, a.datetime, a.type, a.message, l.incident, l.capcode, l.label
FROM labels as l, alarmeringen as a
WHERE l.messageid = a.id AND l.messageid IN (SELECT messageid FROM labels WHERE capcode = $capcode)
ORDER BY l.id DESC
LIMIT 100
Misschien kan iemand mij wat verder op weg helpen? :)
Groeten, Peter.
- Ariën -:
Titel aangepast van 'Hulp bij Query' naar 'Query geeft slechte/foute resultaten'.
Gelieve in het vervolg duidelijke topictitels aan een topic mee te geven.
Gelieve in het vervolg duidelijke topictitels aan een topic mee te geven.
Gewijzigd op 07/12/2016 20:28:13 door - Ariën -
Probeer eens iets als volgt:
Code (php)
1
2
3
4
5
6
2
3
4
5
6
SELECT a.id, a.datetime, a.type, a.message, l.incident, l.capcode, l.label
FROM labels as l
JOIN alarmeringen as a ON l.messageid = a.id
WHERE l.capcode = '$capcode'
ORDER BY l.id DESC
LIMIT 100
FROM labels as l
JOIN alarmeringen as a ON l.messageid = a.id
WHERE l.capcode = '$capcode'
ORDER BY l.id DESC
LIMIT 100
Je zou er ook goed aan doen om op iets anders te sorteren dat een id, maar dat terzijde.
Vergeet ook $capcode niet te escapen.
Ben van Velzen op 06/12/2016 22:10:09:
Waarom heb je die subquery? Die is zinloos omdat je de tabel labels toch al gebruikt.
Probeer eens iets als volgt:
Je zou er ook goed aan doen om op iets anders te sorteren dat een id, maar dat terzijde.
Vergeet ook $capcode niet te escapen.
Probeer eens iets als volgt:
Code (php)
1
2
3
4
5
6
2
3
4
5
6
SELECT a.id, a.datetime, a.type, a.message, l.incident, l.capcode, l.label
FROM labels as l
JOIN alarmeringen as a ON l.messageid = a.id
WHERE l.capcode = '$capcode'
ORDER BY l.id DESC
LIMIT 100
FROM labels as l
JOIN alarmeringen as a ON l.messageid = a.id
WHERE l.capcode = '$capcode'
ORDER BY l.id DESC
LIMIT 100
Je zou er ook goed aan doen om op iets anders te sorteren dat een id, maar dat terzijde.
Vergeet ook $capcode niet te escapen.
Geprobeerd, maar dit geeft niet het gewenste resultaat :
http://monitor.p2kflex.nl/index.php?capcode=1500050
Waar je een groene "GROUP-1" ziet staan daar zouden meerdere regels onder elkaar moeten verschijnen (de "labels"). Hij pakt dus wel de 'capcode', dus het begin is er. Maar niet de andere entries die bij deze groep horen (lees: zelfde messageid)
Ben van Velzen op 06/12/2016 22:32:57:
Klopt je structuur dan wel? Want je label heeft een capcode, daar filter je op. Hoort de capcode dan niet bij de alarmering?
Nouja, misschien was mijn oorspronkelijke query niet helemaal netjes, maar die gaf wel het gewenste resultaat, zij het dat het soms erg lang duurde of helemaal niet werkte (erg onbetrouwbaar dus). De site draait verder probleemloos en de structuur is (mijns inziens) op orde, maar ik heb het niet zelf geschreven, slechts overgenomen enige tijd terug. Wellicht zijn alle queries niet helemaal standaard, geen idee. Ik merk wel dat alles vrij snel gaat, alleen wat ik nu zelf probeer gaat de mist in. Dus ik ga er vanuit dat ik nu zelf de fout in ga en dat de rest redelijk goed gaat. Navigeer eens op de site (knoppen onderin). Je zult wellicht niet weten wat je qua output moet verwachten, maar het gaat in elk geval redelijk vlot. Dan zie je in elk geval hoe de groepen eruit zouden moeten zien. Elke groep heeft een eigen id/messageid en volgens mij zit dat wel snor. Ik ging wat Googelen en kwam toen uit op die subqueries, vandaar mijn vage coding :)
Ivo P op 06/12/2016 22:44:13:
zoals ik het begrijp, wil je alle meldingen EN alle labels voor meldingen die in elk geval $capcode als een van de labels heeft?
Volgens mij heb je dat redelijk goed verwoord, ja :)
ik verwacht een tabel Meldinge
Een tabel Labels
en een tabel die de meldingen en de labels aan elkaar koppelt.
Want kennelijk sla je nu voor elke melding een compleet label op. Stel het label is "voorbeeld" en je wilt dat aanpassen naar "Voorbeeld", dan moet je nu dus alle entry's af?
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
SELECT a.id, a.datetime, a.type, a.message, l2.incident, l2.capcode, l2.label
FROM labels as l1
JOIN alarmeringen as a ON l1.messageid = a.id
JOIN labels as l2 ON l2.messageid = a.id
WHERE l1.capcode = '$capcode'
ORDER BY l1.id DESC
LIMIT 100
FROM labels as l1
JOIN alarmeringen as a ON l1.messageid = a.id
JOIN labels as l2 ON l2.messageid = a.id
WHERE l1.capcode = '$capcode'
ORDER BY l1.id DESC
LIMIT 100
Deze lijkt exact te doen wat ik wil! Ik test nog even verder, maar dat wordt morgen :)
Bedankt zo ver!!
Groeten, Peter.
Gewijzigd op 07/12/2016 00:25:53 door Peter Hunt
tabel alarmeringen
1 id
2 timestamp
3 datetime
4 type
5 incident
6 message
7 zoektekst
8 city_id
tabel labels
1 id
2 capcode
3 label
koppeltabel labels_alarmeringen
1 alarmering_id
2 label_id
de query zou dan worden
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
SELECT a.id, a.datetime, a.`type`, a.incident, l.label, l.capcode
FROM alarmeringen a
JOIN labels_alarmeringen la ON la.alarmering_id = a.id
JOIN labels l on l.id = la.label_id
GROUP BY a.id, a.datetime, a.`type`, a.incident, l.label, l.capcode
HAVING SUM(l.capcode = '$capcode') > 0
FROM alarmeringen a
JOIN labels_alarmeringen la ON la.alarmering_id = a.id
JOIN labels l on l.id = la.label_id
GROUP BY a.id, a.datetime, a.`type`, a.incident, l.label, l.capcode
HAVING SUM(l.capcode = '$capcode') > 0
Ivo P op 07/12/2016 09:40:39:
Volgens mij had je datamodel er meer uit moeten zien als
Dat had misschien mooier geweest, maar ik heb dit dus niet zelf opgezet, ik ben het ook maar als een bestaand project overgenomen :) Ik zou kunnen overwegen om de structuur aan te passen maar ik denk dat daar iets meer bij komt kijken dan een enkele query ;)
Bedankt in elk geval voor je feedback!
En wat @Ivo zegt. Wanneer je met een administratieef systeem werkt is het zeer belangrijk dat je database goed in elkaar zit en ook goed presteert.
https://s30.postimg.org/80dx4hbgx/Region.png
Kan ik hier iets mee?
Toevoeging op 07/12/2016 23:39:48:
Ik wil nog even iets verder, als het kan. Tevens wil ik wat meer uitleg geven over hoe/wat. Een jaar geleden is een vriend van mij vrij plotseling overleden. Hij draaide een zogenaamde P2000-website waarop de alarmeringen van de hulpdiensten te zien zijn (deze zijn vrij te ontvangen met simpele middelen). Samen met nog 2 personen hebben wij besloten de site draaiend te houden, maar het is, zelfs een jaar na dato, soms nog steeds lastig om door andermans code heen te worstelen, zeker als je geen expert bent. Voorgaande query was een soort aanvulling. Er zijn echter ook bestaande queries waar ik af en toe wat vraagtekens bij plaats. De website kent enerzijds vrij standaard, transparante weergaven, maar ook hele persoonlijke, met extra instellingen met bijvoorbeeld excludes en includes. Dingen die je juist/wel niet wilt zien in veel mogelijk combinaties. Laat ik vooropstellen, het werkt. Dat betekent uiteraard niet dat de coding altijd optimaal is, maar de gebruiker merkt daar niet veel van. Als er al grove fouten in zitten, dan heb ik een variabele wellicht niet goed uitgeschreven...
Zo kom ik ook deze query tegen, ik heb hem een beetje uit moeten schrijven omdat er nogal wat variabelen in zitten, maar enerzijds vraag ik me af of deze optimaal geschreven is, anderzijds wil ik hier ook de eerder door Ben voorgestelde aanvulling in verwerken, in het vetgedrukte gedeelte :
SELECT a.id, a.datetime, a.message, a.zoektekst, a.type, a.locked, l.incident, l.capcode, l.msgcolor, l.label
FROM alarmeringen AS a, labels AS l
WHERE a.id > 1000 AND l.messageid = a.id AND (l.incregio = 1 OR l.capcode='1234567') AND MATCH(a.zoektekst) AGAINST('brand') OR a.city_id='15025' AND NOT MATCH(a.zoektekst) AGAINST('test')
EDIT: Het kan zijn dat een aantal kolommen lijkt te ontbreken tov mijn eerdere posting. Dat komt omdat ik eerder een aantal kolommen heb weggelaten omdat die voor de betreffende query niet van toepassing waren. Ze zijn echter -uiteraard- wel aanwezig.
Gewijzigd op 07/12/2016 23:40:57 door Peter Hunt