GoldenLine
Zaloguj się
Elżbieta Kroszyńska

Elżbieta Kroszyńska elegancja i
profesjonalizm

Temat: dwie tabele jedno zapytanie proszę o pomoc!

Witajcie :)

Mam problem z którym męcze się od 2 dni... mam dwie tabele:

watki
`id`, `rozpoczal`, `odbiorca`, `data`

oraz

wiadomosci
`id`, `id_watku`, `nadawca`, `txt`, `data`, `odczyt` (0-niedoczytana, 1-odczytana)

Chcę zrobić takie zapytanie, aby wyświetlało ilość nie doczytanach wiadomości wysłanych do użytkownika zalogowanego. Wiadomości są wątkowane, więc zapytanie musi zliczyć wiadomości z tabeli wiadomości które mają status 0 czyli niedoczytane ale tylko te, które są przypiasne do danego wątku w którym uczestniczy dany zalogowany użytkownik. Oczywiści deny użytkownik zalogowany może uczestniczyć w kilku wątkach. Więc zapytanie powinno pobierać "id" z tabeli wątki, gdzie użytkownik jest odbiorcą albo rozpoczął wątek, następnie "id" powinno porównywać się z "id_wątku" w tabeli wiadomości, i wyselkcjonowane rekordy sprawdzić czy jest tam status 0 i zliczyć ich ilość w tedy wyjdzie liczna nieodczytanych wiadomości. Tak mi się wydaję :) ale nie mam pojęcia jak zmusić do tego baze danych...

Błagam o pomoc! Wskazówki lub przykład takiego zapytania. Z góry dziękuje bardzo!
Link do wypowiedziLink

konto usunięte

Temat: dwie tabele jedno zapytanie proszę o pomoc!

Elżbieta Bajor-Kroszyńska:
Witajcie :)

Mam problem z którym męcze się od 2 dni... mam dwie tabele:

watki
`id`, `rozpoczal`, `odbiorca`, `data`

oraz

wiadomosci
`id`, `id_watku`, `nadawca`, `txt`, `data`, `odczyt` (0-niedoczytana, 1-odczytana)

Chcę zrobić takie zapytanie, aby wyświetlało ilość nie doczytanach wiadomości wysłanych do użytkownika zalogowanego. Wiadomości są wątkowane, więc zapytanie musi zliczyć wiadomości z tabeli wiadomości które mają status 0 czyli niedoczytane ale tylko te, które są przypiasne do danego wątku w którym uczestniczy dany zalogowany użytkownik. Oczywiści deny użytkownik zalogowany może uczestniczyć w kilku wątkach. Więc zapytanie powinno pobierać "id" z tabeli wątki, gdzie użytkownik jest odbiorcą albo rozpoczął wątek, następnie "id" powinno porównywać się z "id_wątku" w tabeli wiadomości, i wyselkcjonowane rekordy sprawdzić czy jest tam status 0 i zliczyć ich ilość w tedy wyjdzie liczna nieodczytanych wiadomości. Tak mi się wydaję :) ale nie mam pojęcia jak zmusić do tego baze danych...

Błagam o pomoc! Wskazówki lub przykład takiego zapytania. Z góry dziękuje bardzo!

select count(1), odbiorac from wiadomosci wi, watki wa where wa.id = wi.id_watku and odczyt =0 group by odbiorca;

To tak na czuja - co to za baza - jaki silnik?
Link do wypowiedziLink

konto usunięte

Temat: dwie tabele jedno zapytanie proszę o pomoc!

http://pl.wikipedia.org/wiki/Join_%28SQL%29 - dość formalnie, ale jest sporo przykładów. W google proszę wpisać - bazy danych sql join i wybrać sobie jakiś kurs z podstaw sql. Każdy kiedyś zaczynał - dobry tutorial to podstawa.
PS - muszę powiedzieć, że jestem pod dużym wrażeniem. Pytanie konkretne, dobrze określone już w tytule. Jak by jeszcze HR tak umieli...
Link do wypowiedziLink
Elżbieta Kroszyńska

Elżbieta Kroszyńska elegancja i
profesjonalizm

Temat: dwie tabele jedno zapytanie proszę o pomoc!

A tak bawię się w php i w bazie MySQL...

Wymyśliłam sobie zrobić system wiadomości z wątkami bo nigdzie nie znalazłam gotowego kodu. Wiadomości już mi działają tylko problem z tymi nieprzeczytanymi :P tak bardziej z ciekawości i hobby to robię bo nigdy się tego nie uczyłam nigdzie, tyle co w googlu przeczytam :) ale czasem czytam czytam i nic nie rozumiem :)
Link do wypowiedziLink
Elżbieta Kroszyńska

Elżbieta Kroszyńska elegancja i
profesjonalizm

Temat: dwie tabele jedno zapytanie proszę o pomoc!

Tomek P.:
Elżbieta Bajor-Kroszyńska:
Witajcie :)

Mam problem z którym męcze się od 2 dni... mam dwie tabele:

watki
`id`, `rozpoczal`, `odbiorca`, `data`

oraz

wiadomosci
`id`, `id_watku`, `nadawca`, `txt`, `data`, `odczyt` (0-niedoczytana, 1-odczytana)

Chcę zrobić takie zapytanie, aby wyświetlało ilość nie doczytanach wiadomości wysłanych do użytkownika zalogowanego. Wiadomości są wątkowane, więc zapytanie musi zliczyć wiadomości z tabeli wiadomości które mają status 0 czyli niedoczytane ale tylko te, które są przypiasne do danego wątku w którym uczestniczy dany zalogowany użytkownik. Oczywiści deny użytkownik zalogowany może uczestniczyć w kilku wątkach. Więc zapytanie powinno pobierać "id" z tabeli wątki, gdzie użytkownik jest odbiorcą albo rozpoczął wątek, następnie "id" powinno porównywać się z "id_wątku" w tabeli wiadomości, i wyselkcjonowane rekordy sprawdzić czy jest tam status 0 i zliczyć ich ilość w tedy wyjdzie liczna nieodczytanych wiadomości. Tak mi się wydaję :) ale nie mam pojęcia jak zmusić do tego baze danych...

Błagam o pomoc! Wskazówki lub przykład takiego zapytania. Z góry dziękuje bardzo!

select count(1), odbiorac from wiadomosci wi, watki wa where wa.id = wi.id_watku and odczyt =0 group by odbiorca;

To tak na czuja - co to za baza - jaki silnik?


Tylko że musi być w zapytaniu "rozpoczal or odbiorca", ponieważ czasem zalogowany user bedzie inicjatorem watku a czsem od kogos dostanie wiadomosc jako pierwszy...Elżbieta Bajor-Kroszyńska edytował(a) ten post dnia 21.11.12 o godzinie 13:49
Link do wypowiedziLink
Elżbieta Kroszyńska

Elżbieta Kroszyńska elegancja i
profesjonalizm

Temat: dwie tabele jedno zapytanie proszę o pomoc!

select count(1), odbiorca from wiadomosci wi, watki wa where wa.id = wi.id_watku and wa.odbiorca = '".$_SESSION['login']."' or wa.rozpoczal = '".$_SESSION['login']."' and odczytano = 0 group by odbiorca

coś takiego tworze ale to nie działa... ciągle pokazuje że jest zero takich rekordów :P i zapewne po tym zapytaniu też bym nic nie wyświetliła :PElżbieta Bajor-Kroszyńska edytował(a) ten post dnia 21.11.12 o godzinie 20:02
Link do wypowiedziLink
Elżbieta Kroszyńska

Elżbieta Kroszyńska elegancja i
profesjonalizm

Temat: dwie tabele jedno zapytanie proszę o pomoc!

to może tak graficznie pokaze

tabela wątki:


Obrazek


tabela wiadomości:


Obrazek


I teraz zapytanie owo musi wyselekcjonować zalogowanego użytkownika np. ADAMIN z tabeli wątki czyli z wątku w tym przypadku 1 i 2 (gdzie raz jest odbiorcą a raz inicjatorem wątku)

Póżniej id z tabelki wątki (czyli tu 1 i 2) musi porównać z tabelą wiadomości, gdzie wyszukuje rekordów wiadomości dopisanych do id danego wątku, musi sprawdzuć czy w polu odczytana jest 0 jeżeli jest to musi zliczyć te rekordy ile ich jest (ale pominąć te gdzie ADMIN jest nadawcą) i w tedy pokazać liczbę nieprzeczytanych wiadomości ze wszystkich wątków dla ADMINA czyli zalogowanego użytkownika. Już wątpie czy to wogóle da się zrobić... :(
Link do wypowiedziLink

konto usunięte

Temat: dwie tabele jedno zapytanie proszę o pomoc!

Co do systemu wiadomości - phpbb nie wystarczy? :)
Wracając do problemu.
http://dev.mysql.com/doc/refman/5.0/en/operator-preced... -> dałbym nawiasy wkoło OR.
Jakie dokładnie zapytanie trafia do bazy? Mogę zobaczyć jak to wygląda u mnie. Idealnie byłoby dorzucić create table - w sumie - też zgadnąć mogę. No, ale jako inżynier - słaby jestem w zgadywaniu... :)
Link do wypowiedziLink

konto usunięte

Temat: dwie tabele jedno zapytanie proszę o pomoc!

Michał Z.:
Co do systemu wiadomości - phpbb nie wystarczy? :)

phpBB jest tak dziurawy że lepiej już sobie samemu coś napisać - mniejsza szansa na atak zakończony sukcesem.

Przy czym to niekoniecznie wina autorów samego skryptu, a raczej dodatków do niego.

Przykładowe zestawienia skryptów typu forum:

http://www.ajaxline.com/10-best-free-php-forum-scripts
http://hellboundbloggers.com/2010/05/18/free-open-sour...Piotr L. edytował(a) ten post dnia 22.11.12 o godzinie 00:40
Link do wypowiedziLink
Albert D.

Albert D. Software Developer

Temat: dwie tabele jedno zapytanie proszę o pomoc!

Elżbieta Bajor-Kroszyńska:
Już wątpie czy to wogóle da się zrobić... :(

Da się..., nie takie rzeczy da się robić w SQLu :)
Konstrukcja tabel nie jest najszcześliwsza do wyciagania takich informacji. Wg mnie kolumna odbiorca powinna
być w tabeli "wiadomości" lub w obu jesli musi.Wtedy uprosciloby to zapytanie bo byloby z jednej tabeli.
Do tego jesli wiemy dla jakiego odbiorcy wyciagamy dane to po co po nim grupowac, wystarczy wyciagnac liczbe wiadomosci nie przeczytanych.
No ale skoro mamy tak jak mamy to moze zapytanie ponizsze pomoze (pisane z glowy-nie testowane):

SELECT count(1) FROM wiadomosci wi
WHERE wi.id_watku IN (SELECT wa.id FROM watki wa WHERE wa.odbiorca = '".$_SESSION['login']."' or wa.rozpoczal = '".$_SESSION['login']."' )
AND wi.odczytano = 0
AND wi.nadawca <> '".$_SESSION['login']."'
Link do wypowiedziLink

konto usunięte

Temat: dwie tabele jedno zapytanie proszę o pomoc!

Albert D.:
Elżbieta Bajor-Kroszyńska:
Już wątpie czy to wogóle da się zrobić... :(

Da się..., nie takie rzeczy da się robić w SQLu :)
Konstrukcja tabel nie jest najszcześliwsza do wyciagania takich informacji. Wg mnie kolumna odbiorca powinna
być w tabeli "wiadomości" lub w obu jesli musi.Wtedy uprosciloby to zapytanie bo byloby z jednej tabeli.
Do tego jesli wiemy dla jakiego odbiorcy wyciagamy dane to po co po nim grupowac, wystarczy wyciagnac liczbe wiadomosci nie przeczytanych.
No ale skoro mamy tak jak mamy to moze zapytanie ponizsze pomoze (pisane z glowy-nie testowane):

SELECT count(1) FROM wiadomosci wi
WHERE wi.id_watku IN (SELECT wa.id FROM watki wa WHERE wa.odbiorca = '".$_SESSION['login']."' or wa.rozpoczal = '".$_SESSION['login']."' )
AND wi.odczytano = 0
AND wi.nadawca <> '".$_SESSION['login']."'

Czy pani syn naprawdę nazywa się "delete from users"?
Link do wypowiedziLink

konto usunięte

Temat: dwie tabele jedno zapytanie proszę o pomoc!

@Szymon: http://xkcd.com/327/ - chociaż... zwykle jest osobny zestaw grantów dla usera i admina.
@Piotr: Jestem programistą, ale wyleczyłem się z podejścia - że wszystko muszę zrobić sam i to w jedynie słusznej technologii... :) To mała rzecz - wdrożenie gotowego rozwiązania jest pewniejsze, szybsze i tańsze. Każde rozwiązanie ma jednak swoje wady - dziurawy kod dodatków, które się instaluje, czy tam samego narzędzia - to jedno z zagrożeń... Robienie od zera ma przewagę w elastyczności, a nie w bezpieczeństwie - IMVHO.
Link do wypowiedziLink

konto usunięte

Temat: dwie tabele jedno zapytanie proszę o pomoc!

Michał Z.:
@Szymon: chociaż... zwykle jest osobny zestaw grantów
dla usera i admina.

Optymista…
Link do wypowiedziLink

konto usunięte

Temat: dwie tabele jedno zapytanie proszę o pomoc!

Michał Z.:
@Piotr: (ciach) Robienie od zera ma przewagę w elastyczności, a nie w bezpieczeństwie - IMVHO.

Jest jedna przewaga niewątpliwa własnego rozwiązania: tak wyśmiewane security-through-obscurity. W przypadku phpBB miałem kilka skutecznych ataków na moje serwisy, jeden z nich zrobiony jako zero-day attack (atak na podstawie opublikowanej dziury w dniu publikacji), z tego powodu musiałem napisać swój własny filtr bezpieczeństwa bo nie dało się inaczej...

W przypadku phpBB polecam przynajmniej zainstalowanie CrackerTracker, ale najlepiej także zgłębienie tematu "phpBB hardening" bo to się przyda.
Link do wypowiedziLink
Marcin Mackiewicz

Marcin Mackiewicz Programista JAVA, RS
Adware Polska

Temat: dwie tabele jedno zapytanie proszę o pomoc!

Koledzy koledzy :) Może założycie grupę filozoficzną. Problem jest prosty do rozwiązania a koleżanka prosiła o zapytanie SQL rozwiązujące jej problem. Podała konkretnie o co chodzi... eh... uśmiecham się szeroko.

Rozwiązanie testowane na PostgreSQL 9.1. Na MySQL powinno działać tylko typy pól zamiast 'TEXT' trzeba zrobić 'VARCHAR' :)

Dane do testów:
drop table if exists wiadomosci;

drop table if exists watki;

create table watki(

  id int primary key,

  rozpoczal text,

  odbiorca text,

  "data" date

);

insert into watki values ( 1, 'admin', 'test', now()::date );

insert into watki values ( 2, 'tomasz', 'admin', now()::date );

insert into watki values ( 3, 'test', 'tomasz', now()::date );

create table wiadomosci(

  id int primary key,

  id_watku int,

  nadawca text,

  txt text,

  "data" date,

  odczyt int default 0,

  constraint wiadomosci_fkey foreign key (id_watku)

    references watki (id) match simple

    on delete cascade on update no action

);

insert into wiadomosci values ( 1, 1, 'admin', 'część', now()::date, 0 );

insert into wiadomosci values ( 2, 1, 'test', 'a witaj', now()::date, 0 );

insert into wiadomosci values ( 3, 2, 'tomasz', 'witaj adminie', now()::date, 0 );

insert into wiadomosci values ( 4, 1, 'admin', 'jak tam?', now()::date, 0 );

insert into wiadomosci values ( 5, 2, 'admin', 'a bardzo dobrze :)', now()::date, 0 );

insert into wiadomosci values ( 6, 1, 'test', 'dobrze leci', now()::date, 0 );

insert into wiadomosci values ( 7, 1, 'admin', 'to się bardzo cieszę', now()::date, 0 );

insert into wiadomosci values ( 8, 3, 'test', 'hej ;/', now()::date, 0 );

Zapytanie SQL wyciągające odpowiednie dane dla zadanego przykładowego użytkownika 'admin'
-- Zliczam nieprzeczytane wiadomosci i grupuje po numerze watku

SELECT

  w.id AS id_watku,

  count(w.id) AS liczba_wiadomosci

FROM 

  watki w

-- Join zwroci te rekordy w ktorych uzytkownik byl rozpoczynajacym lub odbiorca

-- ale nie byl sporzadzajacym wiadomosc

JOIN wiadomosci m 

   ON w.id = m.id_watku

  AND ( rozpoczal = 'admin' OR odbiorca = 'admin' )

  AND m.nadawca != 'admin' AND m.odczyt = 0

-- Grupuje wyniki po id watku

GROUP BY w.id

Temat zamknięty :D
Link do wypowiedziLink

konto usunięte

Temat: dwie tabele jedno zapytanie proszę o pomoc!

Pytanie dotyczy mysqla - zaprezentowany kod nie zadziała. Jeden powód:


ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '::date'

Bez ::date powinno być lepiej.
Wydaje mi się, że klucz obcy można było sobie darować, bo składnia mysqla jest inna.
Skoro już się czepiamy.

Co do tematu. Pytanie dotyczyło zapytania, potem pojawił się pełen opis, z którego wynika po co to wszystko. No więc pytanie, czy robić samemu, czy użyć czegoś gotowego jest jak najbardziej na miejscu. Bazy danych to nie klepanie kodu - trzeba myśleć co się robi. Czasem lepiej się dobrze zastanowić, niż lecieć z robotą, odtrąbić sukces... bo może się okazać, że zupełnie o coś innego chodzi.

Efekt jest taki, że kolega Piotr przekonał mnie dość skutecznie do nieużywania PHPBB. Pewnie autorka pytania i tak nie zmieniłaby podejścia, ale informacja, że ktoś poszedł inną drogą i jej nie poleca to cenna rzecz - przynajmniej dla mnie. Jak rozumiem taki wniosek nie jest filozoficzny?

Z mojej strony to wszystko.
Link do wypowiedziLink
Marcin Mackiewicz

Marcin Mackiewicz Programista JAVA, RS
Adware Polska

Temat: dwie tabele jedno zapytanie proszę o pomoc!

Ano nie działa bo MySQL nie obsługuje funkcji now() :) oraz mapowań :: (::date). Zapomniałem o tym ale nie chciało mi się poprawiać.

MySQL aby zwrócić bieżącą datę używa funkcji curdate() zamiast now()::date. Osoba korzystająca na co dzień z MySQL powinna o tym wiedzieć :)

Pisałem że testowane na PostgreSQL 9.1 a na MySQL powinno zadziałać a nie że na pewno zadziała.
Ponad to jeżeli engine bazy danych pozwala kontrolować spójność danych (klucze obce) to staram się to wykorzystywać i nie zabudowywać obsługi spójności w aplikacji.

Klucz obcy z opcją 'on delete cascade' pozwala mi kasować automatem wszystkie wiadomości dodane do wątku w momencie usunięcia wątku. Robię to jednym delete na tabeli wątki i nie muszę się martwić, że trzeba usunąć jeszcze krotki z tabeli wiadomości.Marcin Mackiewicz edytował(a) ten post dnia 24.11.12 o godzinie 00:22
Link do wypowiedziLink

konto usunięte

Temat: dwie tabele jedno zapytanie proszę o pomoc!

Marcin Mackiewicz:
Pisałem że testowane na PostgreSQL 9.1 a na MySQL powinno zadziałać a nie że na pewno zadziała.

Myślę że od dzisiaj możesz śmiało zakładać coś zupełnie odwrotnego. Zwłaszcza że DBMS-y zwykle różnią się przede wszystkim zestawem dostarczanych funkcji i obsługą kluczy obcych...
Link do wypowiedziLink
« Wróć do tematów
Odpowiedz

Podobne tematy





Wyślij zaproszenie do
Anuluj