Aukcje - zapytanie SELECT Opcje

[markonix]

Struktura bazy w uproszczonym schemacie:
tabela `list` czyli tabela główna z opisem aukcji, wystawcą, datą zakończenia itp.
tebela `bids` czyli tabela z poszczególnymi licytacjami (kwota, użytkownik).
Relacja jeden do wielu (jedna aukcja, 0-* licytacji).

[SQL] pobierz, plaintext 
SELECT * FROM `auctions_list` AS `fr` LEFT JOIN 
( 
SELECT `auction_id`, `username`, COUNT(*) AS `bidsum`, MAX(`bid_value`) AS `maxbid` 
FROM `auctions_bids` WHERE `status` = 1 GROUP BY `auction_id` 
) AS `fm` 
ON fr.id = fm.auction_id 
WHERE ORDER BY `date_end` DESC 
[SQL] pobierz, plaintext 

Zapytanie kolejno bierze listę aukcji, następnie dołącza do niej na podstawie ID licytacje.
Bez problemu działa podsumowanie tj. maksymalny bid, liczba licytacji.
Potem oczywiście za WHERE jest troszkę więcej warunków ale nie chce zaciemniać.

Problem jest jak na razie jeden - `username` NIE zawiera nicku osoby, która wygrywa.
Bierze pierwszy lepszy nick z listy bidów dla danej aukcji.
Nie ma możliwości zastosowania ORDER BY, które jest blokowane przez grupowanie.
Gdy zaś nie ma grupowania to liczy sumę bidów i maksymalny bid globalnie, a nie dla danej aukcji.

Wynik ma mniej więcej wyglądać następująco:

Tytuł aukcji || Sprzedawca || Liczba ofert || Najwyższa oferta || Wygrywa (pogrubione = z tabeli bids)

Działa wszystko oprócz ostatniego, ogólnie zapytanie się strasznie rozrasta (dochodzą warunki dotyczące aukcji, bidów, sortowanie).
Zastanawiam się czy nie robię tego troszkę naokoło.
Nie ukrywam, że wynik ma być zbliżony od listy aukcji jak z Allegro (oprócz paginacji).

Ten post edytował markonix 28.06.2011, 15:45:49

[Magic WWW]

Pokaż mi struktury tych wszystkich użytych tabel, inaczej nie mogę Ci pomów

[markonix]

Raczej nic nie pomogą, są tam tylko atrybuty aukcji, które nie mają znaczenie dla problemu.
A tabela bidów składa się w uproszczonej formie z id aukcji (do łączenia), użytkownika i kwoty.

Po polsku pytanie brzmi - jak stworzyć listę aukcji w relacji 1 do 1 gdzie wygląda to tak:
AUKCJA + WIERSZ NAJWIĘKSZEGO BIDA DLA AUKCJI. (największa wartość w określonej kolumnie)

[thek]

Powiem tak... Mysql teoretycznie pobiera losowy wiersz spośród tych zgrupowanych. W praktyce jednak jest ciut inaczej. Ten losowy to pierwszy spośród grupy. Efekt? Posortuj rekordy według wysokości bid malejąco w podzapytaniu, zanim zaczniesz joinować i grupować. Count i max będą działać, ale dodatkowo będziesz miał tabelę bidów tak posortowaną, że zawsze pierwszy w grupie będzie bid z najwyższą ofertą. A tym samym będzie w nim na bank klucz do id usera, który go walnął.

[markonix]

Niestety ORDER BY w tym podzapytaniu nie zadziała - posortuje WYNIK grupowania, a nie bidy w danej grupie.
No chyba, że mam to jeszcze inaczej zrobił, 3 select w tym selectcie

[rollen]

A może tak...

[SQL] pobierz, plaintext 
SELECT * FROM `auctions_list` AS `fr` LEFT JOIN 
( SELECT stat.*, ab.username FROM
(SELECT `auction_id`, COUNT(*) AS `bidsum`, MAX(`bid_value`) AS `maxbid` 
FROM `auctions_bids` WHERE `status` = 1 GROUP BY `auction_id`) AS stat JOIN auctions_bids ab ON stat.auction_id = ab.auction_id
) AS `fm` 
ON fr.id = fm.auction_id 
WHERE ORDER BY `date_end` DESC 
[SQL] pobierz, plaintext 

[thek]

@Markonix: Pisałem wyraźnie... posortuj wcześniej

[SQL] pobierz, plaintext 
SELECT t.* FROM tabela1 AS t1 LEFT JOIN ( SELECT * FROM inna ORDER BY kolumna DESC) AS t2 ON t1.kolumna = t2.kolumna2 GROUP BY t1.kolumna3
[SQL] pobierz, plaintext 

Jak się nie da, jak się da

[markonix]

Zwraca wynik 1 aukcja -> wszystkie leady.
Ogólnie to w skrypcie poszedłem na skróty i dodałem te drugie zapytanie.
Myślę też o tym, aby właśnie napisać takie zapytanie jak Twoje, albo po prostu zwykłego JOINa + bidy posortowanego wg wartości.
Wtedy jednak zwracam gdzieś o 500% więcej wierszy niż potrzebuje oraz wymagana jest obróbka w PHP (wyświetlać tylko po jednym wierszu na id aukcji).

EDIT:

[SQL] pobierz, plaintext 
SELECT s1.auction_id, s1.username, s1.bid_value, s3.*
FROM `auctions_bids` s1
LEFT JOIN `auctions_bids` s2 ON ( s1.auction_id = s2.auction_id
AND s1.bid_value < s2.bid_value )
LEFT JOIN `auctions_list` s3 ON ( s1.auction_id = s3.id )
WHERE s2.auction_id IS NULL
[SQL] pobierz, plaintext 

http://dev.mysql.com/doc/refman/5.0/en/exa...-group-row.html

Coś tam mi wyszło ale jeszcze będę testował

Ten post edytował markonix 30.06.2011, 20:26:39