[MySQL][PHP] Wyszukiwanie przepisów po składnikach Opcje

[d0m1n1k_]

Witam,
szukam dobrego rozwiązania które pozwoliłoby mi na wyszukiwanie przepisów po podanych składnikach.
Ważne aby pokazywało tylko te przepisy które nie mają innych składników niż te które wprowadziłem w inpucie.

Struktura bazy danych:
(Tabela: kolumna1,...)
przepisy: id, nazwa
przepisy_skladniki: ps_id, ps_przepis, ps_skladnik

Na chwilę obecną zdołałem napisać następujące zapytania:

[PHP] pobierz, plaintext 
select DISTINCT r.nazwa, r.id
from 
    przepisy r
    inner join przepisy_skladniki i
    on i.ps_przepis = r.id
where i.ps_skladnik IN (15, 99)
[PHP] pobierz, plaintext 

Problem: Pokazuje przepisy które zawierają wymienione składniaki (15 i 99 to id składników o które zapytałem)

[PHP] pobierz, plaintext 
select r.nazwa, r.id
from 
    przepisy r
    inner join przepisy_skladniki i
    on i.ps_przepis = r.id
where i.ps_skladnik IN (15, 99)
GROUP BY r.nazwa
HAVING COUNT(*) = 2
[PHP] pobierz, plaintext 

Problem: Pokazuje ten przepis który ma te składniki ale i inne, a nie ten który ma tylko te wyniki.

Co robię nie tak?
Count(*) > 2?
Też nie działa poprawnie.
Bardzo proszę o pomysł, pomoc lub rozwiązanie za które po stokroć dziękuję już teraz!

[trueblue]

Sprawdź czy jest ok.

[SQL] pobierz, plaintext 
SELECT DISTINCT p.nazwa,p.id_przepis
FROM przepis AS p,przepis_skladnik AS ps
WHERE p.id_przepis=ps.id_przepis
GROUP BY p.id_przepis
HAVING SUM(IF(ps.id_skladnik IN(1,2),1,-1))=2
[SQL] pobierz, plaintext 

[Pyton_000]

Albo coś takiego:
http://stackoverflow.com/a/7505147/3732803

[trueblue]

Pyton_000,
to zapytanie zwróci przepisy, które zawierają również składniki spoza wybranych.

[Pyton_000]

Nie powinno chyba że zdefiniuje warunek HAVING >= X

[trueblue]

Nie powinno, ale zwróci:)
Spięcie tabel jest po id_przepis, więc jeśli przepis ma 5 składników, ale 2 z nich wybrane, to i tak zostanie wyselekcjonowany do puli wyników.

[mmmmmmm]

trueblue
Twoje zapytanie rónież zwróci źle. Zakładając, że najważniejsze jest:

[SQL] pobierz, plaintext 
HAVING SUM(IF(ps.id_skladnik IN(1,2),1,-1))=2
[SQL] pobierz, plaintext 

to dla ps.id_skladnik ={1,2,3,4} również zwróci 2 (IMG:style_emoticons/default/smile.gif)
dla ps.id_skladnik = {1,1} także zwróci 2

[trueblue]

trueblue
Twoje zapytanie rónież zwróci źle. Zakładając, że najważniejsze jest:

[SQL] pobierz, plaintext 
HAVING SUM(IF(ps.id_skladnik IN(1,2),1,-1))=2
[SQL] pobierz, plaintext 

to dla ps.id_skladnik ={1,2,3,4} również zwróci 2 (IMG:style_emoticons/default/smile.gif)
dla ps.id_skladnik = {1,1} także zwróci 2

mmmmmmm,
Dla pierwszego przykładu zwróci 0 (+1,+1,-1,-1)
Dla drugiego, tak, masz rację. Choć wiadomo jakie powinno być założenie co do tabeli.

Ten post edytował trueblue 3.09.2015, 12:09:42

[d0m1n1k_]

trueblue nie wiem czy to moja wina przepisania czy zrozumienia, ale zapytanie:

[PHP] pobierz, plaintext 
SELECT DISTINCT p.nazwa, p.id 
FROM przepisy AS p, przepisy_skladniki AS ps 
WHERE p.id = ps.ps_przepis 
GROUP BY p.id 
HAVING SUM(ID(ps.ps_id IN(1,2),1-1))=2
[PHP] pobierz, plaintext 

wyrzuca wynik w phpmyadmin: #1064 - 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 '))=2 LIMIT 0, 30' at line 5

Poza tym zastanawiam się, jeśli chciałbym podawać mniej lub więcej niż dwa składniki (np. w przedziale od 1 do 20) to jak powinienem do tego podjeść?
Czy może to wyglądać w ten sposób:

[PHP] pobierz, plaintext 
$id_wszystkichskladnikow = $_POST['skladnik1'] . ", " . $_POST['skladnik2'] . ", " ... ", " . $_POST['skladnik20'];
$suma_id = "20";
 
select r.nazwa, r.id
from 
    przepisy r
    inner join przepisy_skladniki i
    on i.ps_przepis = r.id
where i.ps_skladnik IN (" . $id_wszystkichskladnikow . ")
GROUP BY r.nazwa
HAVING COUNT(*) = " . $suma_id . "
 
[PHP] pobierz, plaintext 

Czy jestem na dobrym tropie? Czy nadal głęboko w lesie :-///

Ten post edytował d0m1n1k_ 3.09.2015, 14:15:51

[trueblue]

[SQL] pobierz, plaintext 
HAVING SUM(ID(ps.ps_id IN(1,2),1-1))=2
[SQL] pobierz, plaintext 

IF nie ID

Tyle ile id składników, takie porównanie z ilością.
Dla 4-ech byłoby:

[SQL] pobierz, plaintext 
HAVING SUM(IF(ps.id_skladnik IN(1,2,5,10),1,-1))=4
[SQL] pobierz, plaintext 

Pamiętaj aby w tabeli przepisy_skladniki nie mieć przypisanego kilkukrotnie tego samego składnika do przepisu (powinien być indeks unikalny na dwóch polach).

[d0m1n1k_]

Z tym ID/IF to mój błąd przy wprowadzaniu zapytania na forum.
W phpMyAdmin było poprawnie (IF) i błąd wystąpił przy IF'ie ten który opisałem w poprzednim poście.
Czyli ostatnia linijka powinna wyglądać następująco?:

[PHP] pobierz, plaintext 
HAVING SUM(IF(ps.id_skladnik IN(" . $id_wszystkichskladnikow . "),1,-1))=" . $suma_id . "
[PHP] pobierz, plaintext 

Dobrze to rozumiem?

Co do mysql i unikalności, mam to wykonać w następujący sposób?:

[PHP] pobierz, plaintext 
ALTER TABLE przepisy_skladniki
ADD UNIQUE KEY `unikalnoscskladnikow` (`ps_przepis`, `ps_skladnik`)
[PHP] pobierz, plaintext 

POPRAWKA!

Kod poprawiony:

HAVING SUM(IF(ps.ps_id IN(1,2),1,-1))=2

Takie szczegóły ;-)

Ale teraz wywala mi wynik zerowy dla zapytania 15,99 a powinno pokazać dwa przepisy :-///

Ten post edytował d0m1n1k_ 3.09.2015, 14:50:43

[trueblue]

Pokaż zapytanie dla id 15 i 99.

Indeks ok, jeśli te pola to id przepisu i składnika.

Ten post edytował trueblue 3.09.2015, 14:53:15

[d0m1n1k_]

Na chwilę obecną sprawdzam statyczne zapytanie, bez zmiennych.

[PHP] pobierz, plaintext 
SELECT DISTINCT p.nazwa, p.id 
FROM przepisy AS p, przepisy_skladniki AS ps 
WHERE p.id = ps.ps_przepis 
GROUP BY p.id 
HAVING SUM(IF(ps.ps_id IN(15, 99),1,-1))=2
[PHP] pobierz, plaintext 

Przypomnę układ tabel:
(Przepis) przepisy: id, nazwa
(Składniki) przepisy_skladniki: ps_id, ps_przepis, ps_skladnik

[trueblue]

[SQL] pobierz, plaintext 
SELECT DISTINCT p.nazwa, p.id 
FROM przepisy AS p, przepisy_skladniki AS ps 
WHERE p.id = ps.ps_przepis 
GROUP BY p.id 
HAVING SUM(IF(ps.ps_skladnik IN(15, 99),1,-1))=2
[SQL] pobierz, plaintext 

Pole ps_id w tabeli przepisy_skladniki nie jest potrzebne jeśli założyłeś indeks unikalny.

[d0m1n1k_]

Przeanalizowałem.
Mały błąd już teraz działa (IMG:style_emoticons/default/smile.gif)

[PHP] pobierz, plaintext 
SELECT DISTINCT p.nazwa, p.id 
FROM przepisy AS p, przepisy_skladniki AS ps 
WHERE p.id = ps.ps_przepis 
GROUP BY p.id 
HAVING SUM(IF(ps.ps_skladnik IN(15, 99),1,-1))=2
[PHP] pobierz, plaintext 

W ostatniej linijce zamiast ps.ps_id wykorzystałem ps.ps_skladnik


Równo to zauważyliśmy ;-)
Ale za tyle pomocy Piwo Ci się należy! I po trój kroć klik pomógł!

Ale teeeeraz. Jak już sprawę podstawową mam załatwioną, to teraz zastanawiam się nad rozwinięciem.
Jak mógłbym zmodyfikować kod aby pokazywał mi przepisy które mają jeden lub dwa składniki więcej, sortowane po tej wartości od 1 do 10 składników dodatkowych.

Ten post edytował d0m1n1k_ 3.09.2015, 15:03:33

[trueblue]

Zerknij, jeśli działa, to być może da się uprościć:

[SQL] pobierz, plaintext 
SELECT DISTINCT p.nazwa, p.id, (SUM(IF(ps.ps_skladnik IN(15, 99),1,-1)) AS ilosc_podstawowa,SUM(IF(ps.ps_skladnik NOT IN(15, 99),1,-1))) AS ilosc_dodatkowa
FROM przepisy AS p, przepisy_skladniki AS ps 
WHERE p.id = ps.ps_przepis 
GROUP BY p.id 
HAVING ilosc_podstawowa=2 AND ilosc_dodatkowa>=5
ORDER BY ilosc_dodatkowa
[SQL] pobierz, plaintext 

[d0m1n1k_]

Niestety nie działa.
Error: #1064 - 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 'AS ilosc_podstawowa,SUM(IF(ps.ps_skladnik NOT IN(15, 99),1,-1))) AS ilosc_dodatk' at line 1

Zaś po zmianie nawiasów dls ilosc_podstawowa i ilosc_dodatkowa, aby były osobnymi wartościami wywala mi pusty wynik.

[SQL] pobierz, plaintext 
SELECT DISTINCT p.nazwa, p.id, 
(SUM(IF(ps.ps_skladnik IN(15, 99),1,-1))) AS ilosc_podstawowa, 
(SUM(IF(ps.ps_skladnik NOT IN(15, 99),1,-1))) AS ilosc_dodatkowa
FROM przepisy AS p, przepisy_skladniki AS ps 
WHERE p.id = ps.ps_przepis 
GROUP BY p.id 
HAVING ilosc_podstawowa=2 AND ilosc_dodatkowa>=5
ORDER BY ilosc_dodatkowa
[SQL] pobierz, plaintext 

Głowię się nad tym od 2h i nie mogę tego ogarnąć :-//

Jak usunę w HAVING "AND ilosc_dodatkowa>=5" pokazuje elementy które spełniają warunek podstawowy (przepisy w których są tylko wymienione składniki),
zaś ilosc_dodatkowa w obu przypadkach wynosi -2
Gdy usunę cały zwrot HAVING otrzymuję wszystkie przepisy, ułożone od spełniających do niespełniających.

Tabela przepisy_skladniki ma następujące wpisy:

[PHP] pobierz, plaintext 
ps_przepis(1) = ps_skladniki(1,2,5,6)
ps_przepis(2) = ps_skladniki(99,15)
ps_przepis(3) = ps_skladniki(162,164,234)
ps_przepis(5) = ps_skladniki(15,99)
ps_przepis(6) = ps_skladniki(8,51,53,15)
ps_przepis(7) = ps_skladniki(1,29)
ps_przepis(8) = ps_skladniki(25,331,99,340,234,15)
[PHP] pobierz, plaintext 

Wynik po całkowitym usunięciu klauzury HAVING jest następujący:

[PHP] pobierz, plaintext 
nazwa | id | id_podstawowa | id_dodatkowa
przepis(5) | 5 | 2 | -2
przepis(2) | 2 | 2 | -2
przepis(6) | 6 | -2 | 2
przepis(7) | 7 | -2 | 2
przepis(8) | 8 | -2 | 2
przepis(3) | 3 | -3 | 3
przepis(1) | 1 | -4 | 4
[PHP] pobierz, plaintext 

Ten post edytował d0m1n1k_ 3.09.2015, 19:21:05

[trueblue]

[SQL] pobierz, plaintext 
(SUM(IF(ps.ps_skladnik IN(15, 99),1,0))) AS ilosc_podstawowa, 
(SUM(IF(ps.ps_skladnik NOT IN(15, 99),1,0))) AS ilosc_dodatkowa
[SQL] pobierz, plaintext 

[d0m1n1k_]

Trochę przekombinowałem, ale

po zmianie zapytania na następujące:

[SQL] pobierz, plaintext 
SELECT DISTINCT p.nazwa, p.id, 
(SUM(IF(ps.ps_skladnik IN(15, 99),1,0))) AS ilosc_podstawowa, 
(SUM(IF(ps.ps_skladnik NOT IN(15, 99),1,0))) AS ilosc_dodatkowa
FROM przepisy AS p, przepisy_skladniki AS ps 
WHERE p.id = ps.ps_przepis 
GROUP BY p.id 
HAVING ilosc_podstawowa<=2 AND ilosc_dodatkowa<=3
ORDER BY ilosc_podstawowa DESC, ilosc_dodatkowa ASC
[SQL] pobierz, plaintext 

Uzyskałem wynik już bardzo satysfakcjonujący:

[PHP] pobierz, plaintext 
nazwa | id | ilosc_podstawowa | ilosc_dodatkowa
przepis(5) | 5 | 2 | 0
przepis(2) | 2 | 2 | 0
przepis(6) | 6 | 1 | 3
przepis(7) | 7 | 0 | 2
przepis(3) | 3 | 0 | 3
[PHP] pobierz, plaintext 

ilosc_dodatkowa<=3 zmniejszyłem, aby wyeliminowało mi jakikolwiek przepis ;-)
Zastanawiam się jeszcze nad zmianą klauzury:

[SQL] pobierz, plaintext 
HAVING ilosc_podstawowa<=2 AND ilosc_dodatkowa<=3
[SQL] pobierz, plaintext 

na

[SQL] pobierz, plaintext 
HAVING ilosc_dodatkowa<=3
[SQL] pobierz, plaintext 

bo obecny efekt jest identyczny ale nie wiem czy jest to dobre rozwiązanie.

[trueblue]

Wynik jest identyczny, ale to akurat przypadek.
Nie zawsze tak musi być.
Jeśli będziesz mieć przepis z czterema wybranymi składnikami i trzema innymi dodatkowymi, to przepis będzie spełniał tylko jeden z tych warunków (ten krótszy).