Pomoc w zapytaniu.

Czesc, potrzebuje pomocy w budowowie bazy danych, a wlasciwie w zapytaniu. Zapytanie robie dla 3 tabel, a tutaj o to te tabelki

Idac od gory tabelki to posts,recommends,users.
Jaki efekt chce osiagnac? A taki, ze dla kazdgo poszczegolnego usera wyswietli sie lista postow polaczona relacja z tabela users oraz z tabela recommends aby uzyskac kolumny i wiersze z danym postem, jaki user wykonal ten post, czy dany jeser dla ktorego sprawdzamy moze jeszcze rekomendowac dany post oraz ilosc recommendow ile ma ten post.

Takie zapytanie zrobilem z tym ze wyswietla mi sie lista tylko jesli dany user takie posty rekomenduje, a potrzebuje wyswietlic cala liste postow, nawet te ktore nie rekomenduje.
Moje zapytanie wyglada w nastepujacy sposob.

 
SELECT recommends.topic_id,posts.topic ,users.username,'0' as CANRECOMMEND,COUNT(recommends.topic_id) AS recommendsN
FROM recommends
JOIN posts ON recommends.post_id = posts.id 
JOIN users ON posts.creator = users.id
WHERE posts.idIN(SELECT posts.id FROM recommends JOIN psts ON recommends.post_id = posts.id JOIN users ON posts.creator = users.id WHERE recommends.user_id = jakiesID)
GROUP BY posts.id

Zapytanie dziala pieknie dla tych uzytkownikow ktorzy rekomenduja jakies posty, ale nie tego oczekuje. Poniewaz brakuje calej listy postow wraz z tymi samymi kolumnami co tutaj, z tym wtedy recommendsN bedzie automatycznie ustawione na "1".

No bo wychodzisz od tabeli recommends w zapytaniu, więc join-ując posty i users dostajesz tylko te dane, które już dostały recommends. Zastanów się co chcesz uzyskać ? Zakładam, że każdy post z każdym użytkownikiem i flagą can_recommend i ilością recommends, czy tak ?

Pomijając, że będzie to duża ilość danych to będzie słownie coś takiego: Utwórz kartezjana z users i posts i zrób left join recommends (po user_id i topic_id), na podstawie tego czy dla konkretnego user+post masz coś w recommends, ustal flagę can_recommend i ilość recommends.

EDIT: Jeżeli tabela recommends może zawierać wiele rekordów dla tego samego user_id+topic_id, a z tego co widzę to nie masz unique-a, więc może to musisz jeszcze zrobić agregację i grupowanie.

Nie mam pewności czy dobrze zrozumiałem Twój problem, jeżeli nie to podaj więcej szczegółów.

Zakładam, że każdy post z każdym użytkownikiem i flagą can_recommend i ilością recommends, czy tak ?
Tak, wlasnie o to mi chodzi. A co do UNIQUE juz wlasnie to zrobilem. Ja do tego mojego zapytania robilem right join z unionem dla wynikow gdzie nie ma tych id badz nie istenieja albo mi nie wyszlo.

Moglbys to jakos przedstawic w formie kodu? bo nie bardzo rozumiem.

Coś takiego:

SELECT
  u.*,
  p.*,
  (CASE WHEN r.id IS NULL THEN 0 ELSE 1 END) AS ilosc_recommends
FROM
  users u,
  posts p
LEFT JOIN recommends r ON (r.user_id = u.id AND r.post_id = p.id)

Założyłem, że user_id i post_id są UNIQUE w recommends, wtedy albo masz 1 recommend albo 0, nigdy więcej.
Aha, być może będziesz to musiał do MySQL-a dopracować, napisałem z głowy i to tak jakbym pisał na Oracle-a (na co dzień używam).

EDIT:

To może Ci trochę zamulić bazę danych zwłaszcza jak masz dużo danych, więc ogranicz jakimś WHERE-em.

Zapytanie dziala dobrze, ale niestety jesli zrobie klauzule where dla konrketnego uzytkownika to nie dostaje juz wartosci NULL, czyli naszego 0.

Ok juz sobie poradzilem dodalem warunek OR.

A Co jesli chce policzyc te posty? Bo to jedynie jest 1,0, czyli teoretycznie pokazuje ktore zostaly zaglosowane. ale jak je teraz policzyc?

Chcesz policzyć wszystkie, czy zagłosowane w podziale dla user-ów ?

chce policzyc wszystkie. aby obok 1 badz 0 pojawila sie prawdziwa liczba zaglosowan ogolem.

SELECT
  u.*,
  p.*, 
  (CASE WHEN r.id IS NULL THEN 0 ELSE 1 END) AS ilosc_recommends,
  (SELECT COUNT(*) FROM recommends r1 WHERE r1.post_id = p.id) ilosc_wszystkich
FROM
  users u,
  posts p
LEFT JOIN recommends r ON (r.user_id = u.id AND r.post_id = p.id)

Nie myślę już dziś, ale spróbuj takie coś.

Cos nie chcial chodzic ale zainstpirowales mnie do zrobienia tego i udalo sie, zrobilem cos takiego.

SELECT users.login, posts.name,
(
	SELECT COUNT(recommends.post_id)
    FROM highfives
	WHERE recommends.post_id = post.id   
) result

moze tak byc?

Zauwazylem pewna niscislosc w zapytaniu. Dopiero teraz to zobaczylem. Niestety nie dziala to prawidlowo. Kiedy rekomenduje cos z usera 1 automatycznie jest to przypisywanie do wszystkich userow. Teraz nie mam juz sily tego zobaczcyc, ale jesli mialbys czas to spojrz jeszcze raz na to zapytanie, bo chyba cos jest w nim nie tak skoro pokazuje tki wynik. Lub ja cos zlego zrobilem.

Wrzuć aktualne zapytanie jakiego używasz, coś mi się zdaje, że problemem może być zmiana AND na OR.

                SELECT posts.id,posts.topic,\n"
    			. "CASE WHEN users.username IS NULL THEN (SELECT users.username FROM users WHERE users.id = posts.creator) ELSE users.username END AS creator,\n"
   			 . "CASE WHEN recommends.id IS NULL THEN 1 ELSE 0 END AS canrecommend,(\n"
    			. "SELECT COUNT(recommends.post_id)\n"
    			. " FROM recommends\n"
    			. "WHERE recommends.post_id = posts.id) \n"
    			. "recommends\n"
    			. "FROM posts\n"
    			. "LEFT JOIN recommends ON recommends .post_id = posts.id LEFT JOIN users ON recommends.user_id = users.id\n"
    			."AND posts.creator = users.id\n"
    			. "WHERE users.id = ? OR users.id IS NULL

ten OR zrobilem dlatego, bo jesli wybralem jakiegos uzytkownika konkretnego to nie wybieralo mi wszystich postow.

Przecież zrobiłeś to zapytanie całkiem inaczej niż Ci napisałem :)

Bo pojawily mi sie bledy w sqlu i musialem je poprawic, np nie moglem zrobic

 FROM
  users u,
  posts p

Bo wyskakiwal blad i znalazlem odpowiedz ze musze wybrac tylko jedno bo cos sie duplikuje, to raz.
Dwa dodalem tez swoje bo po czystym Twoim zapytaniu mialem w usersach tam gdzie bylo 0 - null, wiec dodalem funkcjonalnosc naprawiajaca to. Kolejna rzecz jaka zmienilem to fakt ze jesli wybralem konkretnego usera wyswietlaly sie posty tylko te ktore rekomendowal, czyli same 1, 0 nie sa uwzgledniane. No a skroty przeciez nie maja znaczenia, ja tak bardziej to widze. Wiec nie wiem co innego tutaj jeszcze zrobilem.

Wyskakuje wtedy bledy typu takie cos

Unknown column 'users.id' in 'on clause'

Dobra, od zera, spróbuj tak:

SELECT
  u.*,
  p.*,
  (CASE WHEN r.id IS NULL THEN 0 ELSE 1 END) AS ilosc_recommends
FROM
  users u
CROSS JOIN posts p
LEFT JOIN recommends r ON (r.user_id = u.id AND r.post_id = p.id)

Zwróć uwagę, że dodałem alias u do users czyli zamiast pisać users.id piszesz u.id (krócej).
Zobacz czy ten CROSS JOIN zadziała, opierałem się na tym: http://www.w3resource.com/mysql/advance-query-in-mysql/mysql-cross-join.php

Wyjdź od tego zapytania i daj mi znać czy Ci działa.

To nie jest to samo co Ty napisales?

SELECT
  users.login,
  posts.topic, 
  (CASE WHEN recommend.id IS NULL THEN 0 ELSE 1 END) AS canreommend,
  (SELECT COUNT(*) FROM recommend WHERE recommend.post_id= posts.id) ilosc_wszystkich
FROM
  posts
LEFT JOIN recommend ON recommend.post_id = posts.id
LEFT JOIN users ON recommend.user_id = users.id

W ten sposob dziala a w ten ktory Ty napisales wyskakuje wlasnie unknown

Zobacz na linka jak działa CROSS JOIN, cały czas zakładam, że właśnie o to Ci chodzi.
Teraz podałem inne rozwiązanie, zamiast wylistowania 2 tabel w FROM zrobiłem CROSS JOIN, obczaj linka i spróbuj.

Chyba chodzi, robilem kilka prob, kilka kombinacji i chyba chodzi prawidlowo. Mozesz tak jak najprosciej powiedziec jak dziala cross join i czy nie zwalnia od dzialanie bazy?

To jak działa masz w linku: http://www.w3resource.com/mysql/advance-query-in-mysql/mysql-cross-join.php chodzi o pictorial presentation.
Czy zwalnia.... tak, jeżeli masz dużo danych, dlatego należy ograniczać where-em.

Fajna sprawa, dopiero zaczynam z bazami danych, bo od zeszlego tygodnia a widze ze kolega sie zna bardzo dobrze. Dzieki wielkie za pomoc! Mam takie pytanie, bo ja potrzebuje baz danych do webu i teraz moje pytanie brzmi czy znajomosc baz danych dla webdevelopera i bazodanowca musi bys taka sama? Czy to jednak bazodanowiec jest sql master? A webdeveloper ma jedynie zrobic pewne zapytania (takie jak chociazby to) bez wiekszego wglebiania sie w jego optymalizacje itp etc.

Myślę, że to są pytania na osobny wątek.

Ja tam jakimś wielkim specem nie jestem, myślę, że na tym forum znalazłbyś dziesiątki lepszych.
Uważam, że webdeveloper powinien znać język SQL i umieć pisać optymalne zapytania.
W przypadku baz danych posiadających możliwość pisania procedur składowanych warto znać jeszcze ichni język programowania (PL/SQL, PL/pgSQL, TSQL w zależności od bazy danych).

Ponadto jako webdeveloper zainteresuj się jeszcze bazami NoSQL.