Poprzednia / następna pozycja
Mechanizm bardzo przydatny, umożliwiający przechodzenie do następnego lub poprzedniego rekordu z poziomu wybranego już rekordu. Dzięki temu użytkownik nie musi wracać do listy czy też z poziomu listy otwierać dziesiątek stron w osobnych zakładkach by je wszystkie przejrzeć.
Implementacja tego mechanizmu niestety nie jest taka prosta, ale zacznijmy od przygotowania przykładowych danych, na których będziemy operować.
Zakładamy, że nasza lista będzie sortowana wg daty, czyli:
Kliknęliśmy w "JAVA" ("devLanguageId" = 7, "publicationDate" = '2010-03-04') i teraz wiedząc wg czego sortowaliśmy listę, powinniśmy potrafić ustalić rekord poprzedni i następny. Gdyby kolumna "publicationDate" była unikalna, sprawa byłaby bardzo prosta.
W ten sposób uzyskalibyśmy pozycję wybranego języka. Niestety tak nie jest. Powyższe zapytanie zwróci nam wynik 3 bo jest kilka rekordów o tej samej dacie. Zastosowanie operatora "<=" także w niczym nie pomoże bo zawsze zliczy nam wtedy rekordy wcześniejsze plus z wybraną datą, czyli u nas 7. Musimy w naszym zapytaniu uwzględnić dodatkowo kolumnę, która zawsze będzie unikalna, czyli np. "devLanguageId". Oczywiście musimy sortowanie po tej kolumnie dodać także do listy. W niektórych przypadkach sprawdziło by się poniższe zapytanie. Nie uwzględnia jednak ono przypadku gdy data jest mniejsza, a identyfikator większy. Jest to bardzo złudne i przekonałem się o tym na własnej skórze.
Problem możemy rozwiązać na dwa sposoby, za pomocą dwóch zapytań lub jednego.
Oba wyniki do siebie dodajemy, co daje nam liczbę 5. Drugi sposób w jednym zapytaniu:
Tym sposobem udało nam się określić pozycję naszego rekordu i teraz wystarczy tylko pobrać rekord przed i po. W tym celu od liczby 5 odejmujemy 2 (jedną pozycja wstecz + aktualny rekord), jeśli wynik odejmowania wyjdzie nam poniżej 2 należy wpisać 2. U nas wyszło 3, więc nie ma problemu.
I tym sposobem dostajemy poprzedni, aktualny i następny rekord. W szczególnych przypadkach może to być jeden rekord (gdy lista składa się tylko z jednego rekordu) lub dwa rekordy (gdy obecny rekord jest pierwszym lub ostatnim na liście).
Wystarczy teraz w PHP sprawdzić, który z opisanych przypadków otrzymaliśmy.
Edycja:
Draakhan w komentarzach zaproponował użycie dwóch innych zapytań, które zwrócą nam od razu poprzednią i następną pozycję. Jest to zdecydowanie lepsze rozwiązanie niż te powyższe.
Słowa kluczowe: PHP, PostgreSQL, Techblog, lista, następna, poprzednia, sql, artykuł, blog
Komentarze i opinie
A w Pythonie/Django wystarczy tylko get_prev/next_by_date(). :D
@Szymon: damn, nie wiedziałem, że Python/Django to relacyjna baza danych. TIL.
@sf: całkiem przyjemne rozwiązanie, jest podobne w MySQL? :)
@BTM: będzie działać na MySQL też :)
W MySQL klauzula OFFSET jest realizowana przez przecinek - LIMIT 0,1 to to samo co LIMIT 1 OFFSET 0.
@Tomasz Kowalczyk: OFFSET też można używać w MySQL ;)
@BTM, damn, nie wiedziałem, że PHP to relacyjna baza danych. :)
To już wiesz ;p A tak poważnie - artykuł skupia się na części SQL rozwiązania, i stwierdzanie, że w Pyhtonie/Django wystarczy jakaś funkcja mija się z prawdą - bo ta funkcja zapewne wykonuje pod spodem jakiś SQL ;)
Poprawcie mnie, jeśli się mylę i nie widzę jakiegoś haczyka, ale czemu znając ID i datę aktualnego rekordu nie zrobić po prostu:
Następny:
SELECT * FROM devLanguages WHERE (addDate = '2010-03-04' AND devLanguageId > 7) OR addDate > '2010-03-04' ORDER BY addDate asc, devLanguageId asc LIMIT 1;
Poprzedni:
SELECT * FROM devLanguages WHERE (addDate = '2010-03-04' AND devLanguageId < 7) OR addDate < '2010-03-04' ORDER BY addDate desc, devLanguageId desc LIMIT 1;
zamiast męczenia bazy zapytaniem z niezbyt wydajnym countem w roli głównej?
(edit: Poprawiłem warunki w WHERE)
edit: po edycji, muszę jeszcze raz teraz sprawdzić ;)
Działa, działa :). Zdążyłem wcześniej przeedytować komentarz i poprawić warunki w WHERE :)
@Draakhan: masz rację, działa, rzeczywiście lepiej to zrobić w ten sposób co przedstawiłeś :)
O, to fajnie, cieszę się, że się przydało :).
@Draakhan Nie zawsze kolejność ID zgadza nam się z warunkami sortowania. Czasami też wolne ID są powtórnie używane i kolejność ID niekoniecznie pokrywa się z kolejnością dodawania rekordów.
@Diabl0: No, ale w "zadaniu" nie chodzi o sortowanie po dacie dodania rekordu (co sugerowałaby nazwa kolumny), tylko po prostu po jakiejś dacie. Sortowanie po ID jest tutaj używane jeśli nie uda się jednoznacznie posortować po niej.
Inna sprawa, że powtórne używanie ID'ków jest proszeniem się o kłopoty zarówno pod względem programistycznym (tworzenie skomplikowanych zapytań), jak też i biznesowym (pod danym adresem nagle pojawia się coś innego niż było kiedyś). Lepiej jest się trzymać zasady KISS :).
Jest tak jak pisze @Draakhan, zresztą dlatego daty dodania nie pokrywały się z identyfikatorami. Dla jasności zmieniłem nazwę kolumny z "addDate" na "publicationDate".
Nowy komentarz