UNION a ORDER BY w łączonych zapytaniach, które zwracają przecinające się zbiory wyników Opcje

[tornax]

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]

Union "mixuje" wyniki jak chcesz. Musisz je na końcu potraktować znowu ORDER BY

[SQL] pobierz, plaintext 
SELECT * FROM (zapytania z UNION)
ORDER BY pole
[SQL] pobierz, plaintext 

Poza tym pokaż zapytanie to przemilimy je do postaci jednego.

[tornax]

[SQL] pobierz, plaintext 
SELECT * FROM (zapytania z UNION)
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 
(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
      FROM product 
      LEFT JOIN productvariant ON product.id = productvariant.productid
      LEFT JOIN brand ON product.brandid = brand.id 
      LEFT JOIN node ON product.nodeid = node.id 
      WHERE product.visible = 1 AND productvariant.visible = 1 AND product.id != -1  AND product.name LIKE '% xt %' GROUP BY id ORDER BY important DESC ) 
 
UNION 
 
(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
      FROM product 
      LEFT JOIN productvariant ON product.id = productvariant.productid
      LEFT JOIN brand ON product.brandid = brand.id 
      LEFT JOIN node ON product.nodeid = node.id 
      WHERE product.visible = 1 AND productvariant.visible = 1 AND product.id != -1  AND product.name LIKE '% xt%' GROUP BY id ORDER BY important DESC ) 
 
UNION 
 
(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
      FROM product 
      LEFT JOIN productvariant ON product.id = productvariant.productid
      LEFT JOIN brand ON product.brandid = brand.id 
      LEFT JOIN node ON product.nodeid = node.id 
      WHERE product.visible = 1 AND productvariant.visible = 1 AND product.id != -1  AND product.name LIKE '%xt %' GROUP BY id ORDER BY important DESC ) 
 
UNION 
 
(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
      FROM product 
      LEFT JOIN productvariant ON product.id = productvariant.productid
      LEFT JOIN brand ON product.brandid = brand.id 
      LEFT JOIN node ON product.nodeid = node.id 
      WHERE product.visible = 1 AND productvariant.visible = 1 AND product.id != -1  AND product.name LIKE '%xt%' GROUP BY id ORDER BY important DESC ) 
 
UNION 
 
(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
      FROM product 
      LEFT JOIN productvariant ON product.id = productvariant.productid
      LEFT JOIN brand ON product.brandid = brand.id 
      LEFT JOIN node ON product.nodeid = node.id 
      WHERE product.visible = 1 AND productvariant.visible = 1 AND product.id != -1  AND product.subname LIKE '%xt%' GROUP BY id ORDER BY important DESC ) 
 
UNION 
 
(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
      FROM product 
      LEFT JOIN productvariant ON product.id = productvariant.productid
      LEFT JOIN brand ON product.brandid = brand.id 
      LEFT JOIN node ON product.nodeid = node.id 
      WHERE product.visible = 1 AND productvariant.visible = 1 AND product.id != -1  AND product.introduction LIKE '%xt%' GROUP BY id ORDER BY important DESC ) 
 
UNION 
 
(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
      FROM product 
      LEFT JOIN productvariant ON product.id = productvariant.productid
      LEFT JOIN brand ON product.brandid = brand.id 
      LEFT JOIN node ON product.nodeid = node.id 
      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]

UNION olewa sortowanie w łączonych zapytaniach. Cytat z manuala:

To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT:

[SQL] pobierz, plaintext 
(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(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 
( SELECT 6 + priority AS order_col, col1, col2, ... )
UNION
( SELECT 4 + priority AS order_col, col1, col2, ... )
UNION
( SELECT 2 + priority AS order_col, col1, col2, ... )
UNION
( SELECT 0 + priority AS order_col, col1, col2, ... )
ORDER BY order_col DESC
[SQL] pobierz, plaintext 

[maly_swd]

a nie chodzi Ci o UNION ALL?

[tornax]

@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 :/

Ten post edytował tornax 4.02.2011, 16:20:26

[Noidea]

@tornax Co jest dla ciebie pożądanym rezultatem? Taka kolejność wierszy:

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]

Ogólnie nie dla mnie ale tak

[Noidea]

No więc jeśli napiszesz takie zapytanie:

[SQL] pobierz, plaintext 
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 
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 
( SELECT 6 + important AS col_order, (reszta_zapytania...) WHERE product.name LIKE '% xt %' )
UNION
( SELECT 4 + important AS col_order, (reszta_zapytania...) WHERE product.name LIKE '% xt%' )
UNION
( SELECT 2 + important AS col_order, (reszta_zapytania...) WHERE product.name LIKE '%xt %' )
UNION
( SELECT 0 + important AS col_order, (reszta_zapytania...) WHERE product.name LIKE '%xt%' )
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

Ten post edytował Noidea 7.02.2011, 16:12:50

[tornax]

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ę.

Ten post edytował tornax 9.02.2011, 15:01:47