Witam,

mam problem natury algorytmicznej. Piszę aktualnie skrypt, w którym użytkownicy się zapisać do poszczególnych kanałów, a w tych z kolei pojawiają się co jakiś czas systemowe wiadomości. Wiadomości wyświetlają się w sidebarze, ale przy dopisaniu się do kanału można wybrać również opcję wysyłania wiadomości mailem. Dostępne są możliwości wysyłki natychmiastowej, raportu raz w tygodniu, albo brak e-maili. Nie ma problemu z wysyłką natychmiastową i brakiem e-maili. Zaciąłem się jednak przy wysyłce maili raz na tydzień.
Jak wiadomo różni użytkownicy mogą się zapisać do różnych kanałów, a więc każdy dostanie inne wiadomości w swoim mailu. Moje pytanie więc, jak pobrać te dane z bazy? Schemat jest następujący:

user:
- id
- email

channel
- id
- name

message:
- id
- title
- content
- channel_id

channel_user:
- id
- channel_id
- user_id

Można oczywiście pobrać najpierw użytkowników, a później dla każdego usera w pętli for pobrać odpowiednie wiadomości. Nie chciałbym jednak robić tego w ten sposób. Macie może jakieś inne pomysły?


Pozdrawiam
Marcin

Ale przecież jakoś musisz połączyć te wszystkie informacje... W jednym z serwisów podczas zapisu do newslettera daję możliwość wyboru kategorii jakie usera interesują. Podczas wysyłki rozwiązuję to tak, że robię odpowiednie joiny tabel między userem, kategorią i informacjami parsując ja do postaci tablicy o strukturze mniej więcej takiej:
array( user1 => array( email, informacje => array( kategoria1 => array(wiadomosc1, wiadomosc2, wiadomosc3), kategoria2 => array(wiadomosc1, wiadomosc2) ) ), user2 => array( email, informacje => array( kategoria1 => array(wiadomosc1), kategoria2 => array(wiadomosc1) ) ) )
Dodatkowo user ma pole typu datowego w bazie. Czemu? Ponieważ porcjuję wyniki dla skryptu. Na serwerach są limity maili i czasu działania. Według nich obliczam "wielkość paczki", czyli ilości userów mniej więcej do obrobienia i co kilka minut uruchamiam ów skrypt. Może być cron, ale ja stworzyłem odpowiednio napisany skrypt, który uruchamia ze sleepem swoje procesy potomne. Użytkownikom, którym udało się posłać maila ustawiam datę wysyłki na aktualną. Daję też możliwość 2-3 nieudanych prób. Po nich też ustawiam datę na aktualną, ale dodaję do loga, że temu userowi nie dostarczono przesyłki. W takim układzie jedynym moim problemem jest odpowiednie zapytanie, które pobierze dane i część skryptu obrabiająca je do podanej struktury. Mając ją mam bardzo łatwą do obróbki porcję danych tablicowych. Mógłbym z tego także XML utworzyć, który bym userowi jako prywatny RSS udostępnił. Bo i struktura tych danych pozwala mi na to.

No właśnie chodziło mi o to jak połączyć te dane Czyli pobierasz dane z bazy w postaci:

user1 | kategoria1 | wiadomosci1
user1 | kategoria1 | wiadomosci2
user1 | kategoria1 | wiadomosci3
user1 | kategoria2 | wiadomosci1
user1 | kategoria2 | wiadomosci2
user2 | kategoria1 | wiadomosci1
user2 | kategoria1 | wiadomosci2
user2 | kategoria1 | wiadomosci3

i wtedy już bezpośrednio w php tworzysz odpowiednia tablice?

Nie czaje generalnie w czym jest w tym problemie dla tego moja podpwiedź może nie mieć najmniejszego sensu, ale ja bym nie bawił się w sortowanie tablic w php bo od takich pierdół jest baza, a po prostu wywalał je po order by

coś na zasadzie:


user: select u.email,c.name,m.* from user u,channel c, message m, channel_user cu.usr_id=u.id and cu.channel_id=m.channel_id and cu.channel_id=c.id
order by c.id

i potem jak wrzucasz do maila to po ORDER id

Tak... Struktura rekordów otrzymanych z bazy jest identyczna z podaną przez Ciebie. Kontroluję tylko tak zmienia się user i kategoria (prosty if) i w zależności od sytuacji odpowiednio reaguję strukturą danych dokładając gałęzie w odpowiednich miejscach. Można to zrobić jeszcze ciut inaczej. Ale to już optymalizacja Interesuję się nie całą wiadomością, ale tylko jej id. W Twoim wypadku Całość zakończyła by się na... id kanału. Dane kanału są zapewne wspólne dla wszystkich userów, a więc wiadomości z tego samego kanału są identyczne dla wszystkich, co pozwala sobie je na boku stworzyć na podstawie zapamiętanych id kanałów. Miałbyś wtedy strukturę danych z bazy user - kanał oraz kanał - wiadomości. To skok wydajnościowy pewien, gdyż pozbywasz się nie tylko JOIN z zapytania na rzecz prostszych dwóch zapytań, ale i upraszczasz dane z racji tego, że powtarzające się informacje o wiadomościach przechowujesz jako osobna strukturę. Masz więc ostatecznie coś takiego:
array( user1 => array( email, informacje => array( kategoria1, kategoria2 ) ), user2 => array( email, informacje => array( kategoria1 , kategoria3 ) ) )
array( kategoria1 => array(wiadomosc1, wiadomosc2, wiadomosc3), kategoria2 => array(wiadomosc1, wiadomosc2), kategoria3 => array(wiadomosc1) )
W tym przykładzie obaj userzy mają wspólną kategoria1. Zapewne są tam identyczne wiadomości, które byśmy tylko dublowali dla każdego użytkownika. Lepiej je mieć osobno i tylko w razie potrzeby wybierać właściwą kategorię i obrabiać (czytaj wrzucać dane wiadomości ). Oszczędza to nie tylko czas, ale i zasoby - usuwając nadmiarowe dane. A jak to rozwiązać? Podczas pierwszego zapytania prostego o userów i ich kanały wrzucaj je do osobnej tablicy. Potem zrób na niej array_unique by wydobyć unikalne. Teraz zapytanie do bazy o wiadomości w tych kanałach. Dołączenie podczas wysyłki odpowiedniej kategorii wiadomości już jest proste.

EDIT: Wykrywacz i robisz coś, co jest bardzo nieoptymalne, czyli iloczyny kartezjańskie. A struktura jaką podał zelu ma sens. Jest to bowiem relacja wiele-do-wielu. Każdy user może być zapisany do dowolnej liczby kanałów a każdy kanał może mieć wielu subskrybentów. Poza tym skąd wiesz z góry jaka wiadomość do kogo trafi? Użytkownik zapisuje się do kanału, nie do konkretnej wiadomości w nim, co właśnie zrobiłeś. To co Ty podałeś jest właśnie bez sensu. Z góry zgadujesz, która nowa wiadomość do kogo trafi? Po to właśnie wiadomość ma przyporządkowanie do id kanału, by można było określić: "Hej to jest mój kanał i masz mnie w nim szukać". Sprawa nieco się komplikuje, gdy wiadomość jest klasyfikowana do kilku kanałów. Wtedy albo trzeba ją zawrzeć w obu, albo na poziomie tworzenia danych wyłapać takiego "dubla".

W channels_users wszystko jest ok User dopisuje się do kanału a nie do wiadomości. W każdym kanale są różne wiadomości. Więc nie da się tak łatwo pobrać jednym zapytaniem wszystkich wiadomości, które powinien dostać user. Tym bardziej, że każdy user może się zapisać do różnych kanałów, a więc i dostanie inne wiadomości w mailu.

To tak jak np zapisujesz się do jakiegoś jobalerta. Każdy user może zażyczyć sobie oferty pracy z innych działów

Wydaje mi się że za bardzo to Thek utrudniasz, to naprawdę jest przecież zadanie na jedego selecta i jedną pętle która przekaże to do maila

W miedzy czasie poprawiłem bo źle na to spojrzałem.

W każdym razie w czym jest problem?

w massage wrzucasz sobie wiadomości, przyporządkowujesz je po channel_id.

[SQL] pobierz, plaintext 
SELECT u.email,c.name,m.* FROM user u,channel c, message m, channel_user cu WHERE cu.usr_id=u.id AND cu.channel_id=m.channel_id AND cu.channel_id=c.id
ORDER BY c.id,u.id
[SQL] pobierz, plaintext 

I pozamiatane... dodałem do order by id z usr, który weźmiesz sobie puszczając w while pętlę
i sprawdzisz czy się powtarza jak tak do dokładasz do treści kolejne zawartość message.

User id się zmienia i gitara wywołujesz mail dla poprzedniego id(wysyłasz indywidualnego maila userowi) i jedziesz dalej z następnym...

Tworzycie jakiś niepotrzebny problem.. tak mi się wydaje bo ta struktura bazy nie pozwala na żadne bardziej skomplikowanie sprawy

Wielkie dzięki Thek Wiele mi się rozjaśniło

Edit:
Wykrywacz - a jaką strukturę bazy zaproponowałbyś w tym przypadku?

Thek ale o jakim iloczynie kartezjańskim ty mówisz?? Wybierasz dane układając sobie je w order by i tak ułożone po prostu przekazujesz. Nie interesuje cię ile tam tych wiadomości on ma i na ilu kanałach.

zelu, dokładnie taką jaką masz. struktura słownik, operacyjna, magazyn jest najlepsza strukturą moim zdaniem.

Bo tak jak staram wam się pokazać można z niej wyciągać wszystko jednym prostym zapytaniem.

Panowie przeczytajcie proszę kolejne moje posty (dla tego thek lepiej jest napisać kolejną wiadomość niż swoją edytować).

Ta struktura jest dobra i wyciąganie z niej inaczej niż przez (wsadzam po raz 3;P):

[SQL] pobierz, plaintext 
 
      SELECT u.email,c.name,m.* FROM user u,channel c, message m, channel_user cu WHERE cu.usr_id=u.id AND cu.channel_id=m.channel_id AND cu.channel_id=c.id
 
      ORDER BY c.id,u.id
[SQL] pobierz, plaintext 

Jeżeli potrzebujesz bardziej to sortować to po prostu dodajesz do order by

No bawienie się w układanie tablicy to jest jak używanie malucha do orania pola. Po co tworzyć tablice w php, skoro można zrobić to dopisaniem nazwy pole w order by?

Generalnie Twoje rozwiązanie Wykrywacz jest łatwiejsze w implementacji, z tym że dla każdego usera muszę po kilka razy pobierać treść tej samej wiadomości. Przy 1000 userów i 10 wiadomościach w kanale muszę pobierać 10 000 rekordów. Natomiast biorąc rozwiązanie Thek'a pobieram wiadomości raz, łącze je w jedną dużą wiadomość dla danego kanału i z bazy mogę pobrać wtedy tylko 1000 rekordów. Czyli zyskuję X (wiadomości) razy mniej rekordów do pobrania.

No chyba że gdzieś zgubiłem wątek rozumowania

Żle zrozumiałeś. Pobierasz wszystko naraz, przez order by układasz sobie to już w kolejności. Czyli pomijasz cały element theka.
Po czym po prostu ładujesz to maile wypluwając dane whilem i sprawdzając użytkownika if'em.

no napisze ci to aby ci pokazać.

[PHP] pobierz, plaintext 
$sql = mysql_query("SELECT u.*,c.*,m.* FROM user u,channel c, message m, channel_user cu WHERE cu.usr_id=u.id AND cu.channel_id=m.channel_id AND cu.channel_id=c.id ORDER BY c.id,u.id");
 
while ($s = mysql_fetch_array($sql)){
if ($user == $s['id_usr']){
$message .= $s['message'];
}
else {
if (isset($user)){mail($user,$message itd); }
$user = $s['id_usr'];
$message .= $s['message'];
}
[PHP] pobierz, plaintext 

Wszystko się zgadza, z pierwotną wersją, która zaproponował Thek

Później była mowa o optymalizacji tego Czyli:
1) pobieram zapytaniem wiadomości sortując je po channel ID.
2) W pętli łączę wiadomości wg kanału (czyli zostaje mi tablica [channel_id] => "Wszystkie wiadomości z kanału"
3) Pobieram użytkowników i kanały w ktorych są zapisani
4) Pętla po 3) i jeżeli następuje zmiana usera, to robię wysyłkę.

Pominąłem oczywiście całe formatowanie wiadomości itp

Order by najpierw po u.id. Będziesz wysyłał do jednego usera X maili, gdzie X to liczba kanałów do jakich się zapisał? To się załatwia jednym mailem zbierającym wszystkie kategorie wiadomości stąd struktura początkowa jaką podałem jest taka a nie inna... To do usera są przyporządkowane kanały, a nie na odwrót. Stąd wydzieliłem jako dwie osobne struktury user-kanał i kanał-wiadomości. Taki podział sprawia, że w mailu w pętli wybieram usera i sprawdzam jakie kanały subskrybował. Wtedy ze struktury kanałów wybieram przypisane do tegoż kanału wiadomości. Poza tym po stronie PHP mam więcej do kontroli w Twoim wariancie, ponieważ muszę kontrolować strukturę userów oraz kategorii. Bo przecież nie wrzucam tego jak leci, ale muszę to jakoś sensownie opakować. To co podałeś ma sens gdy maila tworzymy "in-line", mieszając php z html. Jeśli już myślisz o sensownej strukturze obiektowej, to przecież do widoku nie wyślesz tego co podałeś, bo widok dostanie mnóstwo nadmiarowej informacji, którą jeszcze będzie musiał obrabiać. Inna sprawa, że wrzucanie warunku łączenia do where jest najgorszym możliwym rozwiązaniem. Robisz bowiem najpierw iloczyn kartezjański wszystkich możliwości, które potem redukujesz warunkiem.

EDIT: zulu ma rację... To co Ty proponujesz Wykrywacz to identyczne rozwiązanie z tym co opisałem w swoim pierwszym poście. Jednak to można optymalizować. I wariant optymalizacyjny podałem w swoim kolejnym poście. Liczba zwróconych rekordów zmniejsza Ci się wielokrotnie. Inna sprawa polega na tym, że skrypt w Podanym przez Ciebie i mój pierwszy wariant są bezsensowne na hostingu, gdzie są limity. 30 sekund czasu skryptu + limit maili na godzinę wymusza porcjowanie wyników. To się robi poprzez wrzucenie do zapytania LIMIT, a jak rozpoznasz kiedy go włożyć? Nie wiesz bowiem ile rekordów należy pobrać by nie podzieliło nam w połowie kategorii danego usera czy maili. Może się zdarzyć tak bowiem, że wybrany limit wybierze tylko 4 wiadomości z pierwszej kategorii jakiegoś usera, zaś reszta poszła by drugim w kolejnym porcjowaniu. Bezpieczniej zrobić... Grupowanie kategorii po userze i group_concat numeru kanału lub jego nazwy Mamy redukcję rekordów poprzez pominięcie niepotrzebnych w takiej sytuacji łączeń. Otrzymujemy zamiast user x kategoria x 10 rekordów w najbardziej i najlepiej optymalizowanym przypadku user + unikalne_kategorie x 10
Wrzućmy liczby... 1500 userów, 10 kategorii, z których średnio liczac userzy wybierają po 2.

Wariant1: (uśredniłem tu tę dwójkę)
1500 x 2 x 10 = 30000 rekordów

Wariant2: (pójdę najmniej korzystnym wariantem, czyli unikalne kategorie stworzą wybranie wszystkich możliwych, a więc 10)
1500 + 10 x 10 = 1600

W przypadku gdy userzy zaczną wybierać po kilka kategorii, nie ząś tylko 2 masz do obrobienia w pętlach 15000 x uśredniona_liczba_kategorii_na_użytkownika dla wariantu1. W wariancie2 nawet zwiększenie liczby wiadomości do z 10 do 20 sprawi śmiesznie niskie zwiększenie liczby operacji (ze 100 do 200). By ilość przekroczyła 3000 rekordów obrabianych musielibyśmy zwiększyć przykładowo liczbę kategorii do 30, a liczbę wiadomości do 50 na kategorię lub 30 wiadomości przy pełnym wykorzystaniu 50 kategorii. To zaś jest niemal nierealne. Przy tych zmienionych parametrach wariant1 obrabiał by już setki tysięcy rekordów. To jest potęga dobrze przemyślanego algorytmu pod kątem wydajnościowym

W pierwszym przypadku na php jest duży nacisk przy selekcji tego na userów oraz kategorie oraz mamy możliwość wysłania maili kilku do jednego usera przy porcjowaniu danych. W drugim przypadku na php też jest nacisk przy obróbce danych, ale ilość przebiegów pętli jest znacząco zredukowana i całość jest odporna na stosowanie LIMIT (jeśli zastosujemy grupowanie). Trochę inne będą operacje po stronie php też. Zamiast pętli pojawi się kilka operacji na tablicach.

o fakt że w order by odwrotnie wpisałem, mniejsza o to. Jakoś nie widzę idei twojego jednego maila, rozpisz jak to thek widzisz

Gdzie ty masz kontrolę kategori po stronie php? Jedyne co kontrolujesz to usera, całą resztę po prostu przekazujesz do zmiennej.
Tak samo przy wyświetleniu tego, jeżeli byś chciał przekazać to do html (a w tym formacie idzie najczęściej mail), wyświetlasz już tylko poukładane już dane, nie widzę co miało by być nadmiarowego.

Bo cały czas nie rozumiem ideai budowania najpierw tablic, dla tak prostego zastosowania jak wysłanie maila. Bo owszem może i miało by to sens, jak by na tej podstawie budować dalszą nawigację dla użytkownika przekazując już tak zbudowaną tablicę ... - wyjaśnij proszę thek

Jak byś mi rozpisał w jaki sposób iloczyn kartezjański miał by się budować przy łączeniu tabel w where? pomijając już indeksację dla pól? Przecież bazy są właśnie do tego łącznia przygotowane.... i o ile wiem nie tworzy się w ten sposób właśnie zbioru z którego wybierane są wartości, a wskazywane powiązania są 1 do 1.

Ok, zrobiłem następującą rzecz:

1) Pobrałem z bazy wszystkie wiadomości, wraz z channel_id, które zostały utworzone w ostatnim tygodniu.
2) Przejechałem po wynikach forem i stworzyłem nową tablicę, gdzie klucze to channel_id, a wartości to połączone wiadomości, które należą do danego kanału
3) Następnie pobrałem emaile oraz kanały, do których są zapisani użytkownicy. Skorzystałem tutaj z podpowiedzi thek'a i użyłem grupowania. Dostałem więc następujące dane:

test1@example.com | 1,2,4
test2@example.com | 1,3,5

4) W pętli for zrobiłem explode() na polach z kanałami, a następnie w drugiej zagnieżdzonej pętli po explode'owanych kanałach pobrałem z tablicy z 2) odpowiednie wiadomości i je ze sobą połączyłem.

5) Mając email i wiadomość, którą mam wysłać przekazałem to do funkcji, która się tym odpowiedni juz zajmie.

Teraz moje pytanie czy takie podejście jest ok? Czy te zagnieżdżone pętle to nie samobójstwo

Nie.. Nie polegniesz z prostej przyczyny. Te pętle są bowiem małe, mają po kilka cykli góra. Nie są to kolosy więc. Poza tym używając konstrukcji w stylu foreach( explode( $row['kanały'] AS kanal ) ) {tutaj operacje na tablicy wpisów_kategorii} czy coś w ten deseń bardzo szybko operujesz na wpisach tablicowych. Tak więc może i są to zagnieżdżone pętle, ale króciutkie. Nie posiadające tysięcy przebiegów.

EDIT: A tu odpowiedź dla Wykrywacza:
A więc... Dopóki jest to wciąż ten sam użytkownik, to musi być wszystko pakowane jako treść jednego maila. Stąd pierwszy w ORDER BY jest user. Dopóki nie zmienia się też kategoria to jest ok. Ale w przypadku kilku chcesz chyba wiedzieć która wiadomość której kategorii się tyczy (nie wszystko na hurra tylko jakoś rozgraniczone). To więc też musisz kontrolować i najlepiej jeśli po kategorii jest sortowanie. Dlatego drugi w ORDER BY jest kanał. Do tego momentu (poza kontrolą kategorii) się zgadzamy oboje chyba.

Budowa tablicy jest rozwiązaniem elastyczniejszym. Pozwala Ci na łatwiejszą kontrole nad danymi. Zwróciłem już uwagę na problem porcjowania wyników, ale nie jest on jedyny. W przypadku modyfikacji musisz przebudowywać skrypt. Jest po prostu nieskalowalny. W razie zaś próby wykorzystywania tego do czegoś innego niż wysyłka maila jedynie (wspomniana przeze mnie możliwość tworzenia indywidualnego kanału RSS użytkownika) musimy tworzyć nowy skrypt metodą kopiuj-wklej i zmień fragmenty, ponieważ nie przewidujesz jakiegokolwiek operowania na danych poza tym jednym blokiem instrukcji. Czy nie prościej obsłużyć te same dane innym widokiem? Zamiast wysłać je do widoku maila poślę do rss Struktura zawiera o wiele mniej zdublowanych danych (parametry to dane usera/ów i tablica kategorii z wiadomościami). Jest elastyczna i w przypadku zmian o wiele prostsza do zarządzania.

To co widzisz z wierzchu inaczej działa od strony silnika łączenie tabel przecinkiem tworzy iloczyn kartezjański ich rekordów, z którego dopiero warunki eliminują nie pasujące do wzorca łączenia. Przy operowaniu na wielotysięcznych rekordowo tabelach jest to już niestety powoli odczuwalne nawet z zastosowaniem indeksów. Nie bez powodu określa się w ON warunki łączenia Inna sprawa, że silnik bazodanowy napisane przez nas zapytania stara przetworzyć do optymalizowanej wersji. Nie przypomnę sobie teraz, ale na stronie dokumentacji mysql czytałem o tym, że są one już po stronie silnika konwertowane także do innych zapytań, które mają na celu je zoptymalizować te napisane przez użytkownika.

Dzięki thek