SQL Nummerierung nach Sortierung

Yuuri · 26. Juni 2015

Hallo zusammen,

ich stell mich grad irgendwie ein bisschen deppert an. Geht um ne Produktlistung mit mehreren Seiten. In der Suche soll dabei natürlich auf die richtige Seite gesprungen werden. Dazu würd ich einfach gern die Produkte einer Kategorie raussuchen, diese ordentlich sortieren, mit ner Nummerierung versehen und im Query darüber einfach ne Where-Klausel hinzufügen, um das richtige Produkt zu bekommen. Die Nummerierung verrät mir dann die Stelle in der Liste und somit weiß ich auf welcher Seite das Produkt gezeigt wird. Ganz einfach in der Theorie, nur in der Praxis haperts.

Der Query um den es geht:

Code:

SELECT
	`p`.`products_model`,
	`p`.`products_id`,
	`p`.`products_sort`
FROM
	`products_to_categories` `ptc`
		JOIN `products` `p` ON `ptc`.`products_id` = `p`.`products_id`
		JOIN `products_description` `pd` ON `p`.`products_id` = `pd`.`products_id`
WHERE
	`ptc`.`categories_id` = '10138'
	AND `pd`.`language_id` = '2'
ORDER BY
	`p`.`products_sort` ASC

Code:

products_model	products_id	products_sort
AAA00133      	39110      	10           
AAA00531      	36244      	15           
AAA00305      	36518      	100          
AAA00355      	36495      	120          
AAA00304      	36509      	140          
AAA00565      	46843      	150          
AAA00390      	36553      	160          
AAA00094      	40687      	180          
AAA00532      	36221      	185          
AAA00301      	36515      	200          
AAA00300      	36514      	220          
AAA00126      	36238      	240          
...

Das verfrachte ich jetzt in einen Subquery und nummerier das Ganze durch:

Code:

SELECT
	*,
	(@row := @row + 1) `row`
FROM
	(
		SELECT
			`p`.`products_model`,
			`p`.`products_id`,
			`p`.`products_sort`
		FROM
			`products_to_categories` `ptc`
				JOIN `products` `p` ON `ptc`.`products_id` = `p`.`products_id`
				JOIN `products_description` `pd` ON `p`.`products_id` = `pd`.`products_id`
		WHERE
			`ptc`.`categories_id` = '10138'
			AND `pd`.`language_id` = '2'
		ORDER BY
			`p`.`products_sort` ASC
	) t1
	JOIN (SELECT @row := 0) trow;

Erhalte nun zwar nummerierte Ergebnisse, aber die Sortierung vom Subquery ist hinüber, ergo die Nummerierung fürn Allerwertesten (bzw. ist nun nach products_id sortiert).

Code:

products_model	products_id	products_sort	@row := 0	row
AAA00011      	36171      	1220         	0        	1  
AAA00400      	36177      	820          	0        	2  
AAA00029      	36179      	680          	0        	3  
AAA00030      	36180      	1000         	0        	4  
AAA00033      	36185      	760          	0        	5  
AAA00536      	36186      	810          	0        	6  
AAA00045      	36195      	1604         	0        	7  
AAA00046      	36196      	920          	0        	8  
AAA00050      	36199      	1180         	0        	9  
AAA00051      	36200      	1300         	0        	10 
AAA00049      	36201      	1260         	0        	11 
AAA00052      	36202      	1200         	0        	12 
...

Grund? Lösung?

Nur den ersten Query absetzen, in ner Schleife durchlaufen, Produkt vergleichen + Key setzen, ... Mag ich nicht wirklich und wirkt sehr unelegant.

LG

marcOcram · 26. Juni 2015

Dein Subquery t1 ergibt eine sortierte Tabelle. Nun liest du mit deinem übergeordetem SELECT aus dieser Tabelle aus. Wie das DBMS aus der generierten Tabelle ausliest ist willkürlich dementsprechend ist deine Sortierung auch kaputt.
Sortiere einfach im übergeordnetem Query und nicht im Subselect.

Yuuri · 26. Juni 2015

Wenn ich im übergeordneten Query sortiere, ist die Nummerierung ja hinüber. Ich will ja gerade, dass erst nach products_sort sortiert wird und dieses Result durchnummeriert wird.

fhtagn · 26. Juni 2015

Es gibt das ROWNUMBER-Konstrukt:

Code:

SELECT a, b, c, ROWNUMBER() OVER (ORDER BY d, e, f) AS nr
  FROM ...

Das generiert weitere Spalte mit einer Nummer nach angegebener Sortierung.

marcOcram · 26. Juni 2015

EDIT: too late

welches DBMS wird benutzt?
In Postgresql funktioniert das ganze einwandfrei (nach rand sortiert und nummeriert)

Code:

postgres=> SELECT id, rand, row_number() OVER(ORDER BY rand ASC) as row FROM SpeedTest LIMIT 5;
 id  |               rand               | row
-----+----------------------------------+-----
 822 | 000d2403c4f02c73489a544a4b57dce7 |   1
 396 | 0010f748a9b8d924ec1bc787ca0ed2d9 |   2
 308 | 00b1f38dcd5461fc66062d0b9e33e805 |   3
 172 | 00b9213d21876d8df64f81c486d08751 |   4
 306 | 00bd305995be9706042144db0f041083 |   5
(5 Zeilen)

Yuuri · 26. Juni 2015

MySQL/MariaDB, aber wie ich sehe, gibts sowas dafür nicht... :/

AlbertLast · 27. Juni 2015

Mal davon abgesehen das ich kein mysql und mariadb fan bin...
ich verstehe dein gedanken nicht, wieso die zeilemn nummerierung außerhalb lagerst und
wenn du das machst, wieso du dem kein order by mit gibst.

hier mal ein bsp: http://sqlfiddle.com/#!9/7cf1b/3

Yuuri · 27. Juni 2015

Bei dir fehlt ein Join, die Sortierung ist in der falschen Tabelle und die Where-Klausel fehlt ebenso. http://sqlfiddle.com/#!9/dc3da/7

AlbertLast · 27. Juni 2015

Okay dann nimmst halt das sort in den darüber liegende qry: http://sqlfiddle.com/#!9/dc3da/10

Code:

SET @position := 0;

select *, (@position := @position + 1)
from (
select p.id,p.mdl,p.sort
from assoc a
  join prods p on a.p = p.id
where
a.c = 2) b
order by b.sort;

Yuuri · 27. Juni 2015

Query:

Code:

SET @row := 0;

SELECT `p`.`products_id`, `p`.`products_sort`, (@row := @row + 1) `row`
FROM `products` `p` JOIN `products_to_categories` `ptc` ON `ptc`.`products_id` = `p`.`products_id`
WHERE `ptc`.`categories_id` = '10138'
ORDER BY `p`.`products_sort` ASC
LIMIT 5

Result:

Code:

array (size=5)
  0 => 
    array (size=3)
      'products_id' => string '39110' (length=5)
      'products_sort' => string '10' (length=2)
      'row' => string '78' (length=2)
  1 => 
    array (size=3)
      'products_id' => string '36244' (length=5)
      'products_sort' => string '15' (length=2)
      'row' => string '33' (length=2)
  2 => 
    array (size=3)
      'products_id' => string '36518' (length=5)
      'products_sort' => string '100' (length=3)
      'row' => string '71' (length=2)
  3 => 
    array (size=3)
      'products_id' => string '36495' (length=5)
      'products_sort' => string '120' (length=3)
      'row' => string '60' (length=2)
  4 => 
    array (size=3)
      'products_id' => string '36509' (length=5)
      'products_sort' => string '140' (length=3)
      'row' => string '62' (length=2)

Query:

Code:

SET @row := 0;

SELECT *, (@row := @row + 1) `row`
FROM
(
	SELECT `p`.`products_id`, `p`.`products_sort`
	FROM `products` `p` JOIN `products_to_categories` `ptc` ON `ptc`.`products_id` = `p`.`products_id`
	WHERE `ptc`.`categories_id` = '10138'
	ORDER BY `p`.`products_sort` ASC
) r
LIMIT 5

Result:

Code:

array (size=5)
  0 => 
    array (size=3)
      'products_id' => string '36171' (length=5)
      'products_sort' => string '1220' (length=4)
      'row' => string '1' (length=1)
  1 => 
    array (size=3)
      'products_id' => string '36177' (length=5)
      'products_sort' => string '820' (length=3)
      'row' => string '2' (length=1)
  2 => 
    array (size=3)
      'products_id' => string '36179' (length=5)
      'products_sort' => string '680' (length=3)
      'row' => string '3' (length=1)
  3 => 
    array (size=3)
      'products_id' => string '36180' (length=5)
      'products_sort' => string '1000' (length=4)
      'row' => string '4' (length=1)
  4 => 
    array (size=3)
      'products_id' => string '36185' (length=5)
      'products_sort' => string '760' (length=3)
      'row' => string '5' (length=1)

Query:

Code:

SELECT *, (@row := @row + 1) `row`
FROM
(
	SELECT `p`.`products_id`, `p`.`products_sort`
	FROM `products` `p` JOIN `products_to_categories` `ptc` ON `ptc`.`products_id` = `p`.`products_id`
	WHERE `ptc`.`categories_id` = '10138'
) r
ORDER BY `r`.`products_sort` ASC
LIMIT 5

Result:

Code:

array (size=5)
  0 => 
    array (size=3)
      'products_id' => string '39110' (length=5)
      'products_sort' => string '10' (length=2)
      'row' => string '78' (length=2)
  1 => 
    array (size=3)
      'products_id' => string '36244' (length=5)
      'products_sort' => string '15' (length=2)
      'row' => string '33' (length=2)
  2 => 
    array (size=3)
      'products_id' => string '36518' (length=5)
      'products_sort' => string '100' (length=3)
      'row' => string '71' (length=2)
  3 => 
    array (size=3)
      'products_id' => string '36495' (length=5)
      'products_sort' => string '120' (length=3)
      'row' => string '60' (length=2)
  4 => 
    array (size=3)
      'products_id' => string '36509' (length=5)
      'products_sort' => string '140' (length=3)
      'row' => string '62' (length=2)

Ich hab mal nen Dump hochgeladen mit den relevanten Spalten. Vielleicht kann hier jemand zaubern oder mir einfach sagen, dass es mit MySQL/MariaDB nicht geht.

AlbertLast · 28. Juni 2015

mit mysql 5.5 und 5.6 funktionieren methode 1 und 3:
http://sqlfiddle.com/#!9/6559d/4

Meine annahme wäre es liegt an deiner Version/Configuration oder
an der Art und Weis wie du zur db connectes.

mambokurt · 28. Juni 2015

Da du das ganze eh nach Php rüberholst würde ich mir das Drama komplett sparen, nimm doch einfach die allererste Query und ergänze die Nummerierung dann in Php. Das Problem wird schon sein, dass du das order by im Subselect hast (andere Dbms lassen das gar nicht erst zu), sprich wenn du aussen die Daten holst kannst du dich halt nicht mehr darauf verlassen dass die noch in der Reihenfolge kommen. Wenn du das Order By nach aussen setzt, ist Imho erst due Zeilennummerierung dran und dann wird erst das Order by gemacht, hilft dir also auch nichts.

Wie gesagt: ich würde entweder in Php eine Nummerierung ergänzen oder ganz und gar gleich den Schlüssel vom Ergebnisarray verwenden, das ist ja im Endeffekt schon durchnummeriert von 0 bis X.

Edith: wenn du die Nummerierung unbedingt im Ergebnis brauchst könnte man als ganz kruden Hack eine temporäre Tabelle erstellen und da den Primary key als Nummerierung rausselektieren, das wär jetzt das einzige was mir da noch Sql seitig einfällt...

http://dev.mysql.com/doc/refman/5.5/en/create-table-select.html gleich das erste Beispiel, sprich den Pk legst du extra an und den Rest selektierst du als zusätzliche Felder wie in deiner ersten Query.

Yuuri · 28. Juni 2015

AlbertLast schrieb:
mit mysql 5.5 und 5.6 funktionieren methode 1 und 3:

5.5.5-10.0.20-MariaDB

Wäre das erste Mal für mich, dass MariaDB inkompatibel wäre. Ich installier mir dann mal nen MySQL und probiers da mal.

@ mambokurt: So mach ich es ja bereits bzw. muss. Das Unschöne daran ist aber, dass ich mir vom Resultset alle Ergebnisse ziehen muss, anstatt dass ich einfach nur ein Ergebnis hole und daraus die Spalte row ziehen und vergleichen kann (könnte man natürlich auch noch direkt im Query berechnen lassen).

Code:

$q          = new SelectStatement( <<<SQL
SELECT `p`.`products_id`
FROM
	`products` p
		JOIN `products_to_categories` `ptc`
			ON `ptc`.`products_id` = `p`.`products_id`
WHERE
	`ptc`.`categories_id` = @catid
ORDER BY
	`p`.`products_sort` ASC
SQL
	, [
			"catid"  => $listing["categories_id"],
			"langid" => (int)$_SESSION['languages_id'],
			"prodid" => $listing["products_id"]
		] );
$maxResults = (int)MAX_DISPLAY_SEARCH_RESULTS;
foreach( $q as $k => $row )
{
	if( $row["products_id"] == $listing["products_id"] && $maxResults > 0 && $k > $maxResults )
	{
		$page = ceil( $k / $maxResults );
		if( $page > 1 )
		{
			$url->SetQueryParameter( "page", $page );
			break;
		}
	}
}

Ne extra Tabelle dafür find ich zu viel des Guten. Der Code ist ja nur dafür da, dass bei der Suche direkt auf die richtige Seite der Kategorie gesprungen wird. Das Einzige, was ich davon wissen will, ist also ob nun Seite 1, 2, 3 etc bzw. eben an welcher Position das Gewünschte liegt.

Smartass · 28. Juni 2015

.
Das müßte sich meiner Meinung nach per CSS * lösen lassen:

Fiddle Fullscreen Result

Code:

table                           {counter-reset: rowNumber; counter-reset: headings 0}
table tr                        {counter-increment: rowNumber}
table tr td:first-child::before {content: counter(rowNumber)}
table tr td:first-child::after  {content:"."}
table tr td:first-child         {text-align:right; padding-right:15px}

* zumindest wenn die darzustellenden "mehreren Seiten" z.B. per JS-Pagination erzeugt werden.

mambokurt · 28. Juni 2015

EDIT: hier stand zu viel.

Du bekommst die Produkte ja schon sortiert nach deinem Sortiermerkmal. Wenn du also ein SELECT count(*) as cnt from xxx where sortiermerkmal < (select min(sortiermerkmal from xxx where SUCHOPTIONEN)) bekommst du die Anzahl Sätze vor deinem ersten Suchtreffer, (diese Anzahl) / (die Anzahl der Sätze je Seite) ist doch dann die erste Seite auf deiner Gesamtauflistung?

Sprich Numerierung und Kram kannste dir dann sparen. Du brauchst eigentlich auch nicht immer alle Produkte abholen, oder gibst du alles rüber und blätterst dann per Javascript? Also wenn du immer nur die aktuelle Seite rübergibst solltest du vielleicht über Limit und Offset in der Query auch nur das abholen was du brauchst

.

Yuuri · 28. Juni 2015

mambokurt schrieb:
Du machst das nur wegen Pagination?

Nein nicht wegen Pagination. Wie gesagt: Das ist für die Suche. Dort werden die zur Eingabe relevanten Artikel angezeigt und daraufhin wird in die entsprechende Kategorie gesprungen, nicht direkt aufs Produkt (weil es prinzipiell keine Produktseite gibt, sondern die letzte Kategorie immer nur Produktlisten besitzt). Wenn dann nun mal 100 Produkte in einer Kategorie stecken, wird die Kategorie selbst auf mehrere Seiten aufgeteilt. Und um nun zu erfahren, an welcher Stelle das Produkt in der Kategorie sitzt, muss ich ja wissen, auf welcher Seite dies auftritt, damit ich entsprechend dahin springen kann (zusätzlich wird dazu noch ein Anker mit der Artikel-Nr. gesetzt, sowie die detailierte Beschreibung dessen aufgeklappt).

Vielleicht ist es veranschaulicht besser:

https://www.alternate.de/Prozessoren-%28CPU%29/Alle-anzeigen (dies ist bereits die letzte Seite in meinem Fall, es gibt keine tiefere Ebene oder detailiertere Produktansicht(en))
-> Seite 3, Artikel A4-5300 Accelerated Processor, Prozessor

Über die Suche find ich nun genau diesen Artikel und spring nun auf obige Seite, allerdings richtig auf die dritte Seite, mit entsprechendem Anker in der URL und lande mit dem Viewport direkt auf dem Produkt.

Ergo die resultierende URL:
https://www.alternate.de/Prozessoren-%28CPU%29/Alle-anzeigen?page=3#A4-5300...

Aber nett, dass CB auch bei Alternate ein Partnerprogramm hat.

mambokurt schrieb:
Wenn du dann eine Suche durchführst und wissen willst, auf welcher Seite aller Produkte die einer bestimmten Kategorie liegen (macht das überhaupt Sinn, warum nicht gleich nur die Ergebnisse der entsprechenden Suche anzeigen?) brauchst du eigentlich nur min(products_sort) von deiner Suche selektieren und selektierst dann aus deiner ursprünglichen Query count(*) mit products_sort < diesem Minimum, dann weisst du wieviel Sätze davor kämen und kannst die Seite ausrechnen.

Der Ansatz klingt auch ganz interessant. Allerdings denke ich ist das nicht immer praktikabel, wenn mehrere Produkte mit gleicher Sortierung vorhanden sein sollten. Hier müsste man mal durchspielen, ob ein <= bzw. < + 1 ausreicht. An der Anzahl der Rows erkenn ich ja dann die Position, ja.

mambokurt · 28. Juni 2015

Der Ansatz klingt auch ganz interessant. Allerdings denke ich ist das nicht immer praktikabel, wenn mehrere Produkte mit gleicher Sortierung vorhanden sein sollten. Hier müsste man mal durchspielen, ob ein <= bzw. < + 1 ausreicht. An der Anzahl der Rows erkenn ich ja dann die Position, ja.

Jup, wenn die Sortierreihenfolge nicht eindeutig ist sortierst du halt zusätzlich nach Produktname (products_model?) und zusätzlich noch nach product_id, damit wird es eineindeutig(das würde ich so oder so machen, ansonsten ist die Reihenfolge der Sätze nicht eindeutig definiert und es kann passieren, dass dir das DBMS auf zwei verschiedenen Seiten das selbe Produkt als letzes auf der einen UND erstes auf der anderen Seite zurückgibt, muss nicht passieren, kann aber(so einen Bug hatte ich mal bei einem Kunden, da hieß es dann ein bestimmter Satz würde nie angezeigt, wenn ichs richtig im Kopf habe war da auch das Sortierkriterium mehrdeutig, die DB hat auf der ersten Seite einen Satz mit aufgelistet und dann beim ziehen der zweiten Seite sortiert nach dem Kriterium den Satz von der anderen mit zurückgeworfen, weil der halt im Ram lag und der andere Satz mit dem Kriterium noch nicht)).

Damit müsstest du die Seitenzahl eigentlich erschlagen kriegen

Ergänzung (28. Juni 2015)

Also eigentlich:

Code:

SELECT count(*) as cnt from XXX A 
WHERE EXISTS 
  (SELECT 1 FROM XXX B 
   WHERE  (Suchoptionen) 
   AND ((B.Sortiermerkmal1 > A.Sortiermerkmal1) 
             OR 
             ( (B.Sortiermerkmal1 = A.Sortiermerkmal1) 
               AND  
               (B.Sortiermerkmal2 >= A.Sortiermerkmal2) 
               AND 
               (B.Sortiermerkmal3 > A.Sortiermerkmal3)
             )
           )
   )

Ist a weng spät, aber so ungefähr, mit Sortiermerkmal1 = Produktsortierungsfeld, Sortiermerkmal2 = Produktname und Sortiermerkmal3 = ProduktId

Suche

SQL Nummerierung nach Sortierung

Yuuri

Fleet Admiral

marcOcram

Lieutenant

Yuuri

Fleet Admiral

fhtagn

Ensign

marcOcram

Lieutenant

Yuuri

Fleet Admiral

AlbertLast

Lieutenant

Yuuri

Fleet Admiral

AlbertLast

Lieutenant

Yuuri

Fleet Admiral

AlbertLast

Lieutenant

mambokurt

Gast

Yuuri

Fleet Admiral

Smartass

Gast

mambokurt

Gast

Yuuri

Fleet Admiral

mambokurt

Gast

Ähnliche Themen