Positie van record in database gesorteerd

[WoBBeL]

Voor een klein hobbyprojectje heb ik een paar honderd stuks hardware in de tabel "hardware" gezet. Ik ben op zoek naar de positie van een record in de gehele database zodat ik voor mn pagination weet op welke pagina hij komt. Ik zou dit natuurlijk in PHP kunnen doen door alle data in te laten en dan de positie te zoeken maar dat is nogal CPU/geheugen intensief en (mijn inziens) totaal onnodig. Een ander truukje zou het IdHardware veld kunnen zijn echter wordt er wel eens wat verwijderd en dan klopt het niet meer (ID's niet meer oplopend)

Als ik wat queries gevonden op internet probeer dan krijg ik wat enge fouten dat de query niet geldig is. Mogelijk dat dit te maken heeft met de versie van MariaDB (is 10.3.29).

De tabel heeft 2 velden:
IdHardware - INT (auto increment)
Naam - VARCHAR

Ik heb hierin de volgende waarden staan (voor dit voorbeeld):
IdHardware - Naam
1 - Router
2 - Switch 8 poort
3 - Switch 16 poort
6 - Switch 24 poort
7 - Muis
9 - Toetsenbord

Als ik dit sorteer op Naam krijg ik het volgende:
IdHardware - Naam
7 - Muis
1 - Router
2 - Switch 8 poort
3 - Switch 16 poort
6 - Switch 24 poort
9 - Toetsenbord

Nu wil ik de positie weten in de database van record "1 - Router" van de gesorteerde naam, in dit geval zal dat dus 2 moeten zijn.

Positie - IdHardware - Naam
1 - 7 - Muis
2 - 1 - Router
3 - 2 - Switch 8 poort
4 - 3 - Switch 16 poort
5 - 6 - Switch 24 poort
6 - 9 - Toetsenbord

Ik heb de volgende query die werkt voor de positie maar ik krijg hem NIET werkend met ORDER BY Naam ASC

code:

SELECT 
(SELECT COUNT(*) FROM hardware WHERE IdHardware <= '1') AS positie,
Naam
FROM hardware
WHERE  IdHardware = '1'

[ Voor 7% gewijzigd door WoBBeL op 20-05-2021 11:16 ]

[Juup]

Hoe ziet je query met ORDER BY er uit?
Welke error krijg je als je 'm uitvoert?

[Woy]

Ik zou eens kijken naar de row_number() function: https://www.mysqltutorial...ysql-row_number-function/

[WoBBeL]

MariaDB geupgraded naar 10.5.10-MariaDB in de tussentijd maar zonder succes voor de oudere queries die ik al had gevonden.

Juup schreef op donderdag 20 mei 2021 @ 11:37:
Hoe ziet je query met ORDER BY er uit?
Welke error krijg je als je 'm uitvoert?

Geen, ongeacht waar ik de ORDER BY neer zet (of allebei). Ik krijg echter nog steeds de positie zonder ORDER BY

Woy schreef op donderdag 20 mei 2021 @ 11:41:
Ik zou eens kijken naar de row_number() function: https://www.mysqltutorial...ysql-row_number-function/

Dat lukt maar niet voor 1 record. Ik krijg dan altijd positie "1" terug als ik een WHERE toe voeg. Ik kan dan wel het juiste record eruit vissen aan de hand van het ID maar dan moet ik alsnog alles in het geheugen inladen. Het is geen functie die vaak gebruikt wordt maar ik wil t graag netjes maken zodat ik hier in t vervolg ook gebruik van kan maken

[ Voor 21% gewijzigd door WoBBeL op 20-05-2021 11:46 ]

[Woy]

WoBBeL schreef op donderdag 20 mei 2021 @ 11:43:
[...]
Dat lukt maar niet voor 1 record. Ik krijg dan altijd positie "1" terug als ik een WHERE toe voeg. Ik kan dan wel het juiste record eruit vissen aan de hand van het ID maar dan moet ik alsnog alles in het geheugen inladen. Het is geen functie die vaak gebruikt wordt maar ik wil t graag netjes maken zodat ik hier in t vervolg ook gebruik van kan maken

Je zult voor de window function wel de juiste set moeten selecteren. Door de WHERE limiteer je de set naar 1 record, en zal de window function altijd 1 opleveren ja. Je zult de window function bijvoorbeeld in een CTE moeten gebruiken, en dan uit de CTE de juiste row selecteren.

Ik zou zeggen maak eerst eens een query die de volledige set oplevert, stop die in een CTE, en selecteer daar het record uit dat je wil hebben

[Woy]

De window function wordt namelijk uitgevoerd na de WHERE, maar voor bijvoorbeeld de LIMIT

https://dev.mysql.com/doc...ndow-functions-usage.html

Window functions are permitted only in the select list and ORDER BY clause. Query result rows are determined from the FROM clause, after WHERE, GROUP BY, and HAVING processing, and windowing execution occurs before ORDER BY, LIMIT, and SELECT DISTINCT.

De ORDER BY kun je dan echter weer opgeven binnen de window.

[WoBBeL]

@Woy ik ben echt heel blij met je hulp maar verder dan normale SELECT, UPDATE, DELETE en hier en daar een INNER/RIGHT/OUTER/LEFT JOIN kom ik niet Dit gaat mij echt de pet te boven

Ik snap dat ik de foutieve data opgeef met de huidige waarde, daarom krijg ik ook maar 1 Hoe geef ik een andere set/window op om te tellen en toch maar 1 record terug met de positie?

[ Voor 10% gewijzigd door WoBBeL op 20-05-2021 12:03 ]

[Woy]

Een CTE kun je zie als een tijdelijke tabel, waar je weer uit kunt selecteren

SQL:

WITH OrderedHardware AS (
    SELECT ROW_NUMBER() OVER ( ORDER BY Naam ) row_num, *
    FROM hardware )
SELECT *
FROM OrderedHardware
WHERE IdHardware = 1

Hier maak ik dus eerst een CTE OrderedHardware, die alle hardware bevat, inclusief een row number gebaseerd op de naam ordering. Uit die CTE selecteer ik het record met ID 1

Hetzelfde kun je overigens voor elkaar krijgen door een sub-query als FROM te gebruiken, maar dit is IMHO een stuk leesbaarder.

[ Voor 14% gewijzigd door Woy op 20-05-2021 12:14 ]

[heuveltje]

oeps.

[ Voor 99% gewijzigd door heuveltje op 20-05-2021 12:13 . Reden: .. ]

[WoBBeL]

Woy schreef op donderdag 20 mei 2021 @ 12:12:
Een CTE kun je zie als een tijdelijke tabel, waar je weer uit kunt selecteren

SQL:

WITH OrderedHardware AS (
    SELECT ROW_NUMBER() OVER ( ORDER BY Naam ) row_num, *
    FROM hardware )
SELECT *
FROM OrderedHardware
WHERE IdHardware = 1

Hier maak ik dus eerst een CTE OrderedHardware, die alle hardware bevat, inclusief een row number gebaseerd op de naam ordering. Uit die CTE selecteer ik het record met ID 1

Hetzelfde kun je overigens voor elkaar krijgen door een sub-query als FROM te gebruiken, maar dit is IMHO een stuk leesbaarder.

Ah thanks voor de uitleg. Is dat geen performance dingetje?

Zou de query 1 op 1 moeten werken die jij hebt gepost of moet ik hem combineren met een eerdere query? Ik krijg namelijk een foutmelding (You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '* FROM hardware ) SELECT * FROM OrderedHardware WHERE IdHardware = 1' at line 2 )

[EDIT]
Zo werkt ie met alleen * mocht het niet schijnbaar. Ik heb alleen maar het row_num nodig dus ik heb de IdHardware toegevoegd omdat die voor de WHERE clause nog nodig is

code:

WITH OrderedHardware AS (
    SELECT ROW_NUMBER() OVER ( ORDER BY Naam ) row_num, IdHardware
    FROM hardware )
SELECT *
FROM OrderedHardware
WHERE IdHardware = >ID<

Als ik het goed begrijp wordt zon tijdelijke tabel alleen voor die query gebruikt en daarna weer weggegooid?

[ Voor 18% gewijzigd door WoBBeL op 20-05-2021 12:49 ]

[Woy]

WoBBeL schreef op donderdag 20 mei 2021 @ 12:29:
[...]
Ah thanks voor de uitleg. Is dat geen performance dingetje?

Dat is nogal afhankelijk van je tabelstructuur en indexen e.d. Je zult dit in je execution plans moeten nakijken ( EXPLAIN ). Maar het punt is dat als je een row-number in een bepaalde set wil, de DB deze of samen moet stellen, of je moet het nummer ergens opslaan.

In dit geval zou ik er geen problemen mee verwachten, zeker niet als je een goede index op Naam hebt.

Als ik het goed begrijp wordt zon tijdelijke tabel alleen voor die query gebruikt en daarna weer weggegooid?

Het is niet echt een tijdelijke tabel, maar een virtuele tabel, die alleen gebruikt wordt om het execution plan op te stellen. Het is daarna aan de DB server om te bepalen of hij er daadwerkelijk ergens een tijdelijke tabel voor moet maken, of dat hij het gewoon direct op de bron-tabel kan mappen.

[WoBBeL]

Thanks voor de goede uitleg! @Woy Die "WITH" is dus wel echt iets anders dan "CREATE TEMPORARY TABLE" begrijp ik dus

[Woy]

WoBBeL schreef op donderdag 20 mei 2021 @ 12:52:
Thanks voor de goede uitleg! @Woy Die "WITH" is dus wel echt iets anders dan "CREATE TEMPORARY TABLE" begrijp ik dus

Ja de CTE die je met de WITH specificeert bestaat echt puur alleen in de context van de query waarbij je hem specificeert. Het is een soort View die alleen binnen de Query bestaat.