Pomoc - Szukaj - Użytkownicy - Kalendarz
Pełna wersja: UNION a ORDER BY w łączonych zapytaniach
Forum PHP.pl > Forum > Bazy danych > MySQL
tornax
3.02.2011, 13:14:13
Witam

Mam do zrealizowania zadanie polegające na odpowiednim porządkowaniu wyników wyszukiwania po podanej frazie w sklepie internetowym.

Początkowy wymóg był taki aby po wpisaniu frazy (dajmy na to) 'xt' na początku pojawiały się produkty, w których 'xt' funkcjonuje jako osobne 'słowo', w następnej kolejności mają się pojawiać wpisy gdzie 'xt' jest częścią słowa np. w słowie 'next', potem produkty gdzie w opisie znalazło się 'xt'.

Kwestia została rozwiązana po przez wykorzystanie dwóch UNION łączących 3 zapytania, wyniki ze słowem 'xt' lądowały na początku (grupa 1), potem tam gdzie 'xt' było zawarte w słowie (grupa 2), na końcu tam gdzie znalazło się w opisach (grupa 3).

Zapytanie dla grupy 1 szuka uzywajac LIKE '% xt %', zapytanie dla grupy 2 LIKE '%xt%' oba po nazwie produktu, przy takiej konstrukcji wyniki z pierwszego zapytania zawierają się w wynikach z drugiego zapytania, tutaj przed powtarzaniem wyników chroni nas UNION (które domyślnie działa jako DISTINCT), kolejność zapytań zapewnia odpowiednia kolejność wyników.


Problem pojawił się gdy zostało ustalone aby wyniki w tych grupach były sortowane wedle tego czy produkt jest "ważny" czy nie (wartość 0 lub 1).

Zastosowałem w każdym z 3 zapytań zwykłe "ORDER BY important DESC", jednak po otrzymaniu wyników okazało się, że w grupach 1 i 2 oczekiwane sortowanie nie nastąpiło


Pytanie jest takie czy jest to wada UNION czy też moje błędne podejście do tematu i ewentualnie czy można to zrealizować za pomocą jednego zapytania omijając programistyczne (php) przetwarzanie wyników ?


wookieb
3.02.2011, 13:17:22
Union "mixuje" wyniki jak chcesz. Musisz je na końcu potraktować znowu ORDER BY
[SQL] pobierz, plaintext 
  1. SELECT * FROM (zapytania z UNION)
  2. ORDER BY pole
[SQL] pobierz, plaintext

Poza tym pokaż zapytanie to przemilimy je do postaci jednego.
tornax
3.02.2011, 13:32:48
Cytat(wookieb @ 3.02.2011, 14:17:22 ) *
[SQL] pobierz, plaintext 
  1. SELECT * FROM (zapytania z UNION)
  2. ORDER BY pole
[SQL] pobierz, plaintext


Nawet SELECT nie jest potrzebny na początku, ale to rozwiązanie nie wchodzi w gre bo to posortuje wyniki po połączeniu, a potrzeba jest taka aby posortować przed połączeniem, a całość imo niedziała bo składowe zapytania zwracają wyniki, które się pokrywają. Wykonywałem testy z UNION łącząc 2 zapytania, które zwracają całkowicie różne wyniki i dla takiej sytuacji ORDER BY w tych zapytaniach sprawuje się ok.


Zapytanie jest kolos, i oryginalnie nie moje więc jest tam kilka "perełek" (narazie zostają aby się cały sklep nie wysypał), dlatego opisując problem użyłem trochę okrojonego przykładu

[SQL] pobierz, plaintext 
  1. (SELECT product.promotion, product.id, REPLACE(product.name, 'Inny', '') AS name, product.subname, MIN(productvariant.price) AS price, IF(product.introduction IS NULL,product.description, product.introduction) AS introduction, product.promotion, MAX(productvariant.availability) AS availability, node.important AS important
  2.       FROM product 
  3.       LEFT JOIN productvariant ON product.id = productvariant.productid
  4.       LEFT JOIN brand ON product.brandid = brand.id 
  5.       LEFT JOIN node ON product.nodeid = node.id 
  6.       WHERE product.visible = 1 AND productvariant.visible = 1 AND product.id != -1  AND product.name LIKE '% xt %' GROUP BY id ORDER BY important DESC ) 
  7.  
  8. UNION 
  9.  
  10. (SELECT product.promotion, product.id, REPLACE(product.name, 'Inny', '') AS name, product.subname, MIN(productvariant.price) AS price, IF(product.introduction IS NULL,product.description, product.introduction) AS introduction, product.promotion, MAX(productvariant.availability) AS availability, node.important AS important
  11.       FROM product 
  12.       LEFT JOIN productvariant ON product.id = productvariant.productid
  13.       LEFT JOIN brand ON product.brandid = brand.id 
  14.       LEFT JOIN node ON product.nodeid = node.id 
  15.       WHERE product.visible = 1 AND productvariant.visible = 1 AND product.id != -1  AND product.name LIKE '% xt%' GROUP BY id ORDER BY important DESC ) 
  16.  
  17. UNION 
  18.  
  19. (SELECT product.promotion, product.id, REPLACE(product.name, 'Inny', '') AS name, product.subname, MIN(productvariant.price) AS price, IF(product.introduction IS NULL,product.description, product.introduction) AS introduction, product.promotion, MAX(productvariant.availability) AS availability, node.important AS important
  20.       FROM product 
  21.       LEFT JOIN productvariant ON product.id = productvariant.productid
  22.       LEFT JOIN brand ON product.brandid = brand.id 
  23.       LEFT JOIN node ON product.nodeid = node.id 
  24.       WHERE product.visible = 1 AND productvariant.visible = 1 AND product.id != -1  AND product.name LIKE '%xt %' GROUP BY id ORDER BY important DESC ) 
  25.  
  26. UNION 
  27.  
  28. (SELECT product.promotion, product.id, REPLACE(product.name, 'Inny', '') AS name, product.subname, MIN(productvariant.price) AS price, IF(product.introduction IS NULL,product.description, product.introduction) AS introduction, product.promotion, MAX(productvariant.availability) AS availability, node.important AS important
  29.       FROM product 
  30.       LEFT JOIN productvariant ON product.id = productvariant.productid
  31.       LEFT JOIN brand ON product.brandid = brand.id 
  32.       LEFT JOIN node ON product.nodeid = node.id 
  33.       WHERE product.visible = 1 AND productvariant.visible = 1 AND product.id != -1  AND product.name LIKE '%xt%' GROUP BY id ORDER BY important DESC ) 
  34.  
  35. UNION 
  36.  
  37. (SELECT product.promotion, product.id, REPLACE(product.name, 'Inny', '') AS name, product.subname, MIN(productvariant.price) AS price, IF(product.introduction IS NULL,product.description, product.introduction) AS introduction, product.promotion, MAX(productvariant.availability) AS availability, node.important AS important
  38.       FROM product 
  39.       LEFT JOIN productvariant ON product.id = productvariant.productid
  40.       LEFT JOIN brand ON product.brandid = brand.id 
  41.       LEFT JOIN node ON product.nodeid = node.id 
  42.       WHERE product.visible = 1 AND productvariant.visible = 1 AND product.id != -1  AND product.subname LIKE '%xt%' GROUP BY id ORDER BY important DESC ) 
  43.  
  44. UNION 
  45.  
  46. (SELECT product.promotion, product.id, REPLACE(product.name, 'Inny', '') AS name, product.subname, MIN(productvariant.price) AS price, IF(product.introduction IS NULL,product.description, product.introduction) AS introduction, product.promotion, MAX(productvariant.availability) AS availability, node.important AS important
  47.       FROM product 
  48.       LEFT JOIN productvariant ON product.id = productvariant.productid
  49.       LEFT JOIN brand ON product.brandid = brand.id 
  50.       LEFT JOIN node ON product.nodeid = node.id 
  51.       WHERE product.visible = 1 AND productvariant.visible = 1 AND product.id != -1  AND product.introduction LIKE '%xt%' GROUP BY id ORDER BY important DESC ) 
  52.  
  53. UNION 
  54.  
  55. (SELECT product.promotion, product.id, REPLACE(product.name, 'Inny', '') AS name, product.subname, MIN(productvariant.price) AS price, IF(product.introduction IS NULL,product.description, product.introduction) AS introduction, product.promotion, MAX(productvariant.availability) AS availability, node.important AS important
  56.       FROM product 
  57.       LEFT JOIN productvariant ON product.id = productvariant.productid
  58.       LEFT JOIN brand ON product.brandid = brand.id 
  59.       LEFT JOIN node ON product.nodeid = node.id 
  60.       WHERE product.visible = 1 AND productvariant.visible = 1 AND product.id != -1  AND brand.name LIKE '%xt%' GROUP BY id ORDER BY important DESC )
[SQL] pobierz, plaintext
Noidea
3.02.2011, 17:05:25
UNION olewa sortowanie w łączonych zapytaniach. Cytat z manuala:
Cytat
To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT:

[SQL] pobierz, plaintext 
  1. (SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
  2. UNION
  3. (SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);
[SQL] pobierz, plaintext


However, use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces an unordered set of rows. Therefore, the use of ORDER BY in this context is typically in conjunction with LIMIT, so that it is used to determine the subset of the selected rows to retrieve for the SELECT, even though it does not necessarily affect the order of those rows in the final UNION result. If ORDER BY appears without LIMIT in a SELECT, it is optimized away because it will have no effect anyway.



Proponuję przerobić to na:
[SQL] pobierz, plaintext 
  1. ( SELECT 6 + priority AS order_col, col1, col2, ... )
  2. UNION
  3. ( SELECT 4 + priority AS order_col, col1, col2, ... )
  4. UNION
  5. ( SELECT 2 + priority AS order_col, col1, col2, ... )
  6. UNION
  7. ( SELECT 0 + priority AS order_col, col1, col2, ... )
  8. ORDER BY order_col DESC
[SQL] pobierz, plaintext
maly_swd
3.02.2011, 18:29:54
a nie chodzi Ci o UNION ALL?
tornax
4.02.2011, 16:17:49
@Noidea: już wyżej pisałem że sortowanie po całości (=po połaczeniu wyników) nie wchodzi w grę ponieważ nie da porządanego rezultatu opisywanego na samym początku

@maly_swd: nie ponieważ zwróci pewne wyniki podwójnie, dwa UNION ALL nijak ma się do kwestii sortowania


Dlatego stawiam, że pozostaje metoda programistyczna, wywoływania zapytań osobno i odpowiedniego łączenia wyników :/
Noidea
4.02.2011, 16:28:18
@tornax Co jest dla ciebie pożądanym rezultatem? Taka kolejność wierszy:
Cytat
WHERE product.name LIKE '% xt %' AND important = 1
WHERE product.name LIKE '% xt %' AND important = 0
WHERE product.name LIKE '% xt%' AND important = 1
WHERE product.name LIKE '% xt%' AND important = 0
WHERE product.name LIKE '%xt %' AND important = 1
WHERE product.name LIKE '%xt %' AND important = 0
WHERE product.name LIKE '%xt%' AND important = 1
WHERE product.name LIKE '%xt%' AND important = 0
tornax
7.02.2011, 14:30:37
Ogólnie nie dla mnie ale tak
Noidea
7.02.2011, 16:11:39
No więc jeśli napiszesz takie zapytanie:
[SQL] pobierz, plaintext 
  1. SELECT 6 + important AS col_order, (reszta_zapytania...) WHERE product.name LIKE '% xt %'
[SQL] pobierz, plaintext
to wiersze z tekstem " xt " i `important` równym 1 będą miały dodatkową kolumnę z wartością 7. Te z `important` = 0 będą miały 6

w kolejnym zapytaniu:
[SQL] pobierz, plaintext 
  1. SELECT 4 + important AS col_order, (reszta_zapytania...) WHERE product.name LIKE '% xt%'
[SQL] pobierz, plaintext
wiersze z tekstem " xtra" będą miały wartości 5 lub 4 w zależności od pola `important`. Oznacza to, że:
1. Wiersze z tekstem " xt " zawsze będą miały większą wartość `col_order` niż wiersze z tekstem " xtra"
2. Wiersze z tekstem pasującym do tego samego LIKE będą miały większą wartość w `col_order`, jeśli będą ważne (`important` = 1)

Więc nasza sztucznie utworzona kolumna `col_order` idealnie nadaje się do sortowania:
[SQL] pobierz, plaintext 
  1. ( SELECT 6 + important AS col_order, (reszta_zapytania...) WHERE product.name LIKE '% xt %' )
  2. UNION
  3. ( SELECT 4 + important AS col_order, (reszta_zapytania...) WHERE product.name LIKE '% xt%' )
  4. UNION
  5. ( SELECT 2 + important AS col_order, (reszta_zapytania...) WHERE product.name LIKE '%xt %' )
  6. UNION
  7. ( SELECT 0 + important AS col_order, (reszta_zapytania...) WHERE product.name LIKE '%xt%' )
  8. ORDER BY col_order DESC
[SQL] pobierz, plaintext


Jedyny problem z tym zapytaniem jest taki, że ten sam wiersz może zostać dopasowany wiele razy, a UNION go nie odsieje, bo będzie miał różne wartości w `col_order`. Dlatego trzeba całe to zapytanie umieścić w środku SELECTa, który pobierze wszystkie kolumny oprócz `col_order` + zastosowanie DISTINCT

Jeśli to nie da dobrych rezultatów, to już nie mam pomysłów i może faktycznie pozostanie metoda programistyczna
tornax
9.02.2011, 14:23:38
Pomysł wydaje się sensowny, ponieważ kwestia rozwiązania tego sortowania dziś znów do mnie wróciła to do jutra powinienem to zaimplementować i przy okazji napisać jakiś ticket na stronie mysql'a zeby może pomyśleli nad sortowaniem w podzapytaniach UNION, skoro obecnie ten ORDER nic w tych podzapytaniach nie robi

Edit

Zrobiłem, na pierwszy rzut oka zachowuje się jak należy, kudos dla Ciebie Noidea za podsunięcie rozwiązania z dodatkową kolumną, myślę, że od tego momentu już sobie spokojnie dam radę.
To jest wersja lo-fi głównej zawartości. Aby zobaczyć pełną wersję z większą zawartością, obrazkami i formatowaniem proszę kliknij tutaj.
Invision Power Board © 2001-2025 Invision Power Services, Inc.