MySQL upit

[Mid]

Imam tabelu "tagovi" koja sadrži id taga,ključnu riječ s kojom povezujem taj tag sa sadržajem i sam tag:

id | rijec | tagovi
01 | naziv_igre1 | tag
02 | naziv_igre1 | tag1
03 | naziv_igre2 | tag3
04 | naziv_igre2 |tag1

Naravno,nisu ovo svi recordi u tabeli,ima ih mnogo,mnogo više...

Imam stranicu sa igrama koje učitavam preko query stringa a na toj istoj strani imam tabelu u kojoj želim da se prkazuju sve igre koje su po nekom tagu slične njoj.
Smislio sam kako to napraviti,ali ne znam napraviti pravi upit,znači recimo da imam "naziv_igre2".Trebam selektirati sve njezine tagove iz tabele "tagovi" te nakon toga pronaći sve tagove koji su isti kao barem jedan tag od te igre te nakon toga dobiti "naziv_igre1".
Konkretno u ovoj tabeli,recimo da imam "naziv_igre2",kako dobiti "naziv_igre1" pošto im je jedan ("tag1") isti?
Također,kako proći kroz cijelu tabelu i naći sve nazive čiji su tagovi slični zadanima?
Evo što sam ja pokušao napisati ali,naravno, ne radi:

//konekcija na bazu
mysql_connect("localhost","user","pass");
mysql_select_db("baza");
//napravio sam dva upita i kasnije ih pokušao usporediti....neuspješno :)
$uzorak = mysql_query("SELECT * FROM tagovi WHERE rijec='".$_GET['b']."'");
$upit = mysql_query("SELECT * FROM tagovi");
while ($uzorak1 = mysql_fetch_array($uzorak) && $upit1 = mysql_fetch_array($upit)){
//tu ih uspoređujem i ako nađe slične dodjeljuje ih varijabli '$naziv'
if ($uzorak1['tagovi'] == $upit1['tagovi']){
$naziv = $upit1['rijec'];
}
}


Nadam se da ste razumjeli što sam htio.Hvala!

[tsereg]

U principu ti treba nešto u ovom smislu. Uoči da je primjer samo ilustrativan da shvatiš poantu.
FROM
TAGOVI T1
INNER JOIN
TAGOVI T2
ON
T1.tagovi = T2.tagovi
WHERE
T2.rijec = "naziv_igre2"


Ono gore i ovo dolje su identični upiti. Čak bi i po performansama trebali biti slični ako optimalizator može biti dovoljno inteligentan.
FROM
TAGOVI T1
INNER JOIN
(
SELECT
tagovi
FROM
TAGOVI T3
WHERE
T3.rijec = "naziv_igre2"
) T2
ON
T1.tagovi = T2.tagovi


Ovo bi bio "školski" primjer, pretpostavljam?
FROM
TAGOVI T1
WHERE
T1.tagovi IN (
SELECT
tagovi
FROM
TAGOVI T2
WHERE
T2.rijec = "naziv_igre2"
)

Uoči da će svi upiti izabrati i "naziv_igre2", pa to trebaš dodatno isključiti ako ne želiš. Također uoči da ćeš morati koristiti nešto tipa "SELECT DISTINCT rijec".

[Mid]

Hvala,tsereg!

No,sada imam jedan drugi problem.Naime,kada dobijem tu drugu riječ koja je zapravo povezana igra s trenutnom,tu riječ trebam ubaciti u sljedeći query:


$query = "SELECT * FROM igre WHERE rijec='rijec_koju_sam_dobio' ORDER BY datum DESC LIMIT 0, 6";


a pošto nisam spretan sa JOIN i sličnim sql funkcijama,ne znam točno kako napraviti ispravan query koji obuhvaća sve to.

Hvala!

[tsereg]

Gornji upit (ali isto tako i bilo koji drugi upit) možeš ubaciti po špranci:
SELECT
*
FROM
igre
WHERE
rijec IN ( ... gornji upit ... )
ORDER BY
datum DESC LIMIT 0, 6

Alternativno, možeš ga ubaciti po špranci:
SELECT
*
FROM
igre
INNER JOIN
(
... gornji upit ...
) GORNJI_UPIT
ON
igre.rijec = GORNJI_UPIT.rijec
ORDER BY
datum DESC LIMIT 0, 6

U prvoj špranci je važno da "gornji upit" ima SAMO JEDAN atribut, dakle gornji upit mora biti oblika:SELECT DISTINCT nesto FROM ... Nikako ne smije biti oblikaSELECT DISTINCT nesto, nesto_drugo, nesto_trece FROM ...

U drugoj špranci gornji upit može imati više atributa, ali jedan se mora zvati "rijec".

----

Konkretan primjer.

1. Uoči da u tom gornjem upitu nisi dobio samo jednu "riječ", već sve "riječi" koje su povezane s makar jednim od "tagova" kojima je obilježena izvorna "riječ". Znači originalni upit bi mogao biti nešto oblika:
SELECT DISTINCT
T1.rijec AS rijec
FROM
TAGOVI T1
WHERE
T1.tagovi IN (
SELECT
tagovi
FROM
TAGOVI T2
WHERE
T2.rijec = "naziv_igre2"
)Taj upit izabire sve "rijeci" iz "TAGOVI" koje imaju makar jedan "tag" iz skupa "tagova" kojima je obilježena "riječ" "naziv_igre2". Zbog "DISTINCT" se niti jedna "rijec" neće ponoviti više od jednom, a sigurno će se pojaviti "riječ" "naziv_igre2". U konkretnom primjeru koji si ti dao, gornji upit bi trebao rezultirati s dva zapisa, "naziv_igre1" i "naziv_igre2". Općenito, može rezultirati i s tri, četiri, pet ili više zapisa.

2. Ubačen, ovaj konkretni gornji upit izgleda ovako:

SELECT
*
FROM
igre
WHERE
rijec IN (
SELECT DISTINCT
T1.rijec AS rijec
FROM
TAGOVI T1
WHERE
T1.tagovi IN (
SELECT
tagovi
FROM
TAGOVI T2
WHERE
T2.rijec = "naziv_igre2"
)
)
ORDER BY
datum DESC LIMIT 0, 6

Ili ovako:


SELECT
*
FROM
igre
INNER JOIN
(
SELECT DISTINCT
T1.rijec AS rijec
FROM
TAGOVI T1
WHERE
T1.tagovi IN (
SELECT
tagovi
FROM
TAGOVI T2
WHERE
T2.rijec = "naziv_igre2"
)
) GORNJI_UPIT
ON
igre.rijec = GORNJI_UPIT.rijec
ORDER BY
datum DESC LIMIT 0, 6

Naravno, gornji upit može biti bilo što drugo, pa možeš napraviti nešto poput ovog:


SELECT
*
FROM
igre
INNER JOIN
(
SELECT DISTINCT
T1.rijec as rijec
FROM
TAGOVI T1
INNER JOIN
(
SELECT
tagovi
FROM
TAGOVI T3
WHERE
T3.rijec = "naziv_igre2"
) T2
ON
T1.tagovi = T2.tagovi
) GORNJI_UPIT
ON
igre.rijec = GORNJI_UPIT.rijec
ORDER BY
datum DESC LIMIT 0, 6


-----------

NAPOMENA: ovo su već dosta kompleksni upiti, a ja ih pišem iz glave. Nemoj očekivati da će ti to proraditi iz prve!

Osobno, ja mislim da će se ovaj posljednji primjer (bez upotrebe IN operatora), a s upotrebom INNER JOIN operatora izvoditi najbrže, ali ćeš svakako trebati dobro postaviti indekse (tagovi bi trebali biti indeks i sl.). Moj prijedlog je da tagove vodiš u zasebnoj tablici, tj. da ih indeksiraš kroz cijele brojeve koji su ključ te tablice s tagovima. Slično možeš i s "riječima". To će ti uštediti prostor na disku, a i vrijeme pri izvođenju ovakvih složenih upita.

[Mid]

Puno hvala tsereže na brzom i odličnom odgovoru!

Ipak sam se odlučio za ovaj primjer sa IN operatorom jer ne znam koliko je brzina presudna u mojem slučaju...

Uglavnom,sve radi kako treba nakon što sam još isključio da mi se pojavljuje rezultat "naziv_igre2" jer mi taj ne treba.

[Mid]

Evo mene opet...

Do prije par dana koristio sam ovaj slučaj sa IN operatorom i sve je radilo kako treba dok mi host nije javio da mi je skripta zagušila server i da je pod hitno maknem (imao sam preko 700 recorda u tabeli "tagovi").
Onda sam se sjetio ovog posta i ponovo navratio misleći koristiti ovu INNER JOIN funkciju jer sam se sjetio da si rekao da će se brže izvoditi.
No evo već sat vremena studiram i ne mogu se snaći među ovih 2-3 ogromna upita što si napisao.Nisam mogao shvatiti što znači "GORNJI_UPIT" tj. na što se on odnosi.

Poslušao sam tvoj savjet na kraju - podijelio sam tablicu "tagovi" i sad imam jednu tablicu "rijec" koja sadrži samo jednu kolonu "rijec" sa svim riječima i drugu tablicu "tagovi" koja također sadrži samo jednu kolonu "tagovi" sa svim tagovima.

Dakle,malo bi preformulirao svoje prošlo pitanje - kako napraviti upit koji će se najbrže izvoditi i koji će davati isti rezultat (tsereg,molio bih na jednom konkretnom primjeru jer se od ovakvih "općih" stvari zbunim za dvije sekunde,pogotovo sad kad imam dvije tablice...jednostavno nisam čovjek za mysql upite :p )

Hvala još jednom...

[Mid]

Oprostite na daveži ali zbilja bi mi hitno trebalo,znači sada imam sljedeće tablice:

rijec

id | rijec
1 | igra1
2 | igra1
3 | igra2
4 | igra2

tagovi

id | tagovi
1 | tag1
2 | tag2
3 | tag1
4 | tag3

Dakle,koji točno upit (sa INNER JOIN operatorom) trebam napisati ako imam "igra2" da dobijem "igra1" pošto ime je barem jedan tag zajednički i tako proći kroz sve tagove i naći sve riječi koje imaju iste tagove kao zadana riječ.

Evo dokuda sam stigao i ne mogu dalje (unutar upita je još dodan i upit za uzimanje tih riječi iz druge tablice....to samo zanemarite,to radi):

$query = "
SELECT
*
FROM
igre
INNER JOIN
(
SELECT DISTINCT
T1.rijec as rijec
FROM
rijec T1
INNER JOIN
(
SELECT
tagovi
FROM
tagovi T3
INNER JOIN
rijec T4
ON
T3.id = T4.id
WHERE
T4.rijec = '$rijec'
) T2
ON
T1.tagovi = T2.tagovi
) GORNJI_UPIT
ON
igre.rijec = GORNJI_UPIT.rijec
ORDER BY
datum DESC LIMIT 0, 6";

Hvala na bilo kakvom odgovoru!

[tsereg]

-1-

U postu br. #4 zadnji upit ti je ono što tražiš u postu br. #6. Čitaj pažljivije. :)

-2-
Nisi me baš razumio što sam ti rekao o razdvajanju tablica.

Mislio sam ovo:

TAG := ( TAG_ID, TAG_TXT ), gdje je

TAG_ID ... primarni ključ, neka cjelobrojna auto-increment vrijednost
TAG_TXT ... konkretni tag, pri čemu je ovaj atribut UNIQUE.

Efektivno, kada korisnik dodaje novi tag prvo se pogleda da li tag postoji, pa se dodaje samo ako ne postoji. U ostatku sustava se umjesto _teksta_ taga uvijek koristi njegov interni cjelobrojni id.

IGRA := ( IGRA_ID, IGRA_NASLOV ), gdje je

IGRA_ID .. primarni ključ, neka cjelobrojna auto-increment vrijednost
IGRA_NASLOV ... naslov igre, pri čemu je ovaj atribut UNIQUE

Efektivno kada korisnik dodaje novu igru, prvo se pogleda da li naslov već postoji, pa se zapis dodaje samo ako ne postoji. U ostatku sustava se umjesto naslova igre koristi interni cjelobrojni id.

IGRA_TAG := ( IGRA_ID, TAG_ID )

Gornja tablica su relacije - koja igra ima koji tag.

Na ovakav način trošiš manje diskovnog prostora, a složene operacije usporedbe se obavljaju nad cijelim brojevima umjesto nad stringovima.

-3-

Ovo što si ti napravio nije nikakva razlika u odnosu na prethodno, već samo dodaje još jednu operaciju spajanja u upit.

Preporučam da se vratiš na staro.

-4-

Upit na koji te upućujem pod (1), prerađen za nove tablice:SELECT
*
FROM
igre
INNER JOIN
(
SELECT DISTINCT
T1.rijec as rijec
FROM
(
SELECT
rijec
, tagovi
FROM
tagovi
INNER JOIN
rijec
ON
tagovi.id = rijec.id
) T1
INNER JOIN
(
SELECT
tagovi
FROM
tagovi
INNER JOIN
rijec
ON
tagovi.id = rijec.id
WHERE
rijec.rijec = "naziv_igre2"
) T2
ON
T1.tagovi = T2.tagovi
) GORNJI_UPIT
ON
igre.rijec = GORNJI_UPIT.rijec
ORDER BY
datum DESC LIMIT 0, 6-5-

Upit na koji te upućujem pod (1) prema tablicama navedenima u (2).SELECT
*
FROM
IGRA
INNER JOIN
(
SELECT DISTINCT
T1.IGRA_ID as IGRA_ID
FROM
IGRA_TAG T1
INNER JOIN
(
SELECT
TAG_ID
FROM
IGRA_TAG T3
WHERE
T3.IGRA_ID = <<id igre dohvaćen ranije>>
) T2
ON
T1.TAG_ID = T2.TAG_ID
) GORNJI_UPIT
ON
IGRA.IGRA_ID = GORNJI_UPIT.IGRA_ID
ORDER BY
IGRA.datum DESC LIMIT 0, 6-6-

Postići dobre performanse upita je kompromis slijedećih elemenata:


fizičkog modela podataka (tablica), koji je napravljen tako da pojednostavnjuje/ubrzava upit,
prikladnog postavljanja indeksa tako da se kod spajanja ne mora vršiti "full table scan" koji je *glavni* uzrok značajnog usporenja upita,
eksperimentiranja i izabiranja onog SQL upita kojeg optimalizator pretvora u najefikasniji plan izvođenja.


Uoči da ja ne mogu iz glave postići sva tri faktora - toliki maher nisam. :)

Znači, ne garantiram da ti ovaj upit neće zagušiti server.

Ipak, u onoj prvoj, originalnoj izvedbi tablica pokušaj staviti indekse i na polje "rijec" i na polje "tagovi". U drugoj izvedbi pokušaj staviti indekse na sve što nije već indeksirano.

-7-

Uoči da su upiti napisani iz glave. Ako ne radi što treba, javi.

-8-

Pročitaj cijeli post do kraja. ;)

[mamica]

OT: @tsereg.... optimalizator? gdje se može naći i koliko košta? :D

Na što točno misliš: na mysql explain select... ili na neki softver?

[tsereg]

Vjerojatno na "explain select" - nisam radio s MySQL-om, ali svaki DBMS ima nešto čime se može pokazati plan izvođenja sql-upita - čak i se i u Jetu (Access) može u registryju postaviti neki flag, pa uvijek prije izvođenja upita ispiše plan u tekstualnu datoteku.

Optimalizator: to mi je ostalo s predavanja iz baza podataka :) Nešto kao "dretva" i sl.

[Mid]

Mislio sam ovo:

TAG := ( TAG_ID, TAG_TXT ), gdje je

TAG_ID ... primarni ključ, neka cjelobrojna auto-increment vrijednost
TAG_TXT ... konkretni tag, pri čemu je ovaj atribut UNIQUE

Efektivno, kada korisnik dodaje novi tag prvo se pogleda da li tag postoji, pa se dodaje samo ako ne postoji. U ostatku sustava se umjesto _teksta_ taga uvijek koristi njegov interni cjelobrojni id.

IGRA := ( IGRA_ID, IGRA_NASLOV ), gdje je

IGRA_ID .. primarni ključ, neka cjelobrojna auto-increment vrijednost
IGRA_NASLOV ... naslov igre, pri čemu je ovaj atribut UNIQUE

Efektivno kada korisnik dodaje novu igru, prvo se pogleda da li naslov već postoji, pa se zapis dodaje samo ako ne postoji. U ostatku sustava se umjesto naslova igre koristi interni cjelobrojni id.

IGRA_TAG := ( IGRA_ID, TAG_ID )

Gornja tablica su relacije - koja igra ima koji tag.

Na ovakav način trošiš manje diskovnog prostora, a složene operacije usporedbe se obavljaju nad cijelim brojevima umjesto nad stringovima.

Ipak, u onoj prvoj, originalnoj izvedbi tablica pokušaj staviti indekse i na polje "rijec" i na polje "tagovi". U drugoj izvedbi pokušaj staviti indekse na sve što nije već indeksirano.


Ne znam da li ja nisam shvatio ili što već...

Ovdje si mi napisao da napravim 3 tablice, TAG (s poljima TAG_ID i TAG_TXT ), IGRA (s poljima IGRA_ID, IGRA_NASLOV ) i IGRA_TAG (s poljima IGRA_ID, TAG_ID ).Nadam se da je to točno,tj. da sam to shvatio?

Boldao sam dvije rečenice koje ne razumijem.Naime,ja upisujem tagove tako da ih najprije iz textboxa razdvojim na zasebne riječi (broj tagova nije fiksan) a nakon toga svakom tagu pridružim naziv igre i upisujem jedan po jedan u tablicu "tagovi".Znači tablica "tagovi" mi je u mom prvom pitanju izgledala ovako (konkretan primjer s pravim nazivima):

id | naziv | tagovi
1 | 3dlogic | boje
2 | 3dlogic | spajanje
3 | 3dlogic | kocka
4 | ablast | pucanje
5 | ablast | boje
6 | ablast | brod
7 | ball2 | loptica

I tako dalje 700 recorda.
Sada si rekao da podijelim to na više tablica pa bi to izgledalo ovako nekako:

IGRA

IGRA_ID | IGRA_NASLOV
1 | 3dlogic
2 | 3dlogic
3 | 3dlogic
4 | ablast
5 | ablast
6 | ablast
7 | ball2

TAG

TAG_ID | TAG_TXT
1 | boje
2 | spajanje
3 | kocka
4 | pucanje
5 | boje
6 | brod
7 | loptica

Po toj logici bi treća tablica IGRA_TAG trebala izgledati ovako:

IGRA_TAG

IGRA_ID | TAG_ID
1 | 1
2 | 2
3 | 3
4 | 4
(...)

No ne čini mi se baš da ovakva tabela ima smisla (a možda i ima :hmmmmmm: )
Stoga ne razumijem kako redovi IGRA_NASLOV i TAG_TXT mogu biti "UNIQUE"?

Meni se čini da je i ovaj query napisan uz uvjet da za svaki IGRE_NASLOV postoji samo jedan TAG_TXT,no u mojem slučaju treba proći kroz cijelu bazu i naći sve tagove koji su isti kao barem jedan tag od zadanog naziva igre.

Tako mi se barem čini,a možda sam i ja glup i ne vidim nešto što je očito a ti mi tu svim silama pokušavaš objasnit :cyber: .

Za mene bi najslikovitije objašnjenje bilo da mi nacrtaš "model" tablice i onda napišeš query jer ćeš uštediti i meni i sebi vremena a i prostora na forumu.

Jer ako ovako nastavimo čini mi se da ćemo tek nakon 20-og posta shvatiti što je onaj drugi htio reći :)

Hvala na trudu!

[tsereg]

Za početak uoči da imaš gore dan upit za SVE TRI kombinacije. Dakle i za prvu i za drugu i za treću verziju tablica. Nigdje ne stoji da moraš napraviti kako sam ja rekao - to je samo ideja.

A što se moje ideje tiče, ona je IDENTIČNA kao i tvoj prvi, originalni slog tablica, samo što umjesto stringova koristimo brojeve.

id | naziv | tagovi
---+---------+----------
1 | 3dlogic | boje
2 | 3dlogic | spajanje
3 | 3dlogic | kocka
4 | ablast | pucanje
5 | ablast | boje
6 | ablast | brod
7 | ball2 | lopticaTvoj originalni upis se pretvara u ovo:

IGRA:IGRA_ID | IGRA_NASLOV
1 | 3dlogic
2 | ablast
3 | ball2TAG:TAG_ID | TAG_TXT
-------+----------
1 | boje
2 | spajanje
3 | kocka
4 | pucanje
6 | brod
7 | lopticaIGRA_TAG:IGRA_ID | TAG_ID
--------+-----
1 | 1
1 | 2
1 | 3
2 | 4
2 | 1
2 | 6
3 | 7
2 | 2Tablice IGRA i TAG su entiteti, a tablica IGRA_TAG je relacija između igara i tagova. Tablica IGRA_TAG je _posve identična_ kao tvoja originalna, samo što smo konkretne nazive igara i konkretne tagove *šifrirali*. Operacije usporedbe i pretraživanja su malo brže ako se izvode nad cijelim brojevima (načelno govoreći).

Kada dobiješ nove tagove onda ih dodaješ u tablicu TAG, ali prije dodavanja provjeriš da li isti tag već postoji u tablici. Ako ne postoji, onda dodaješ novi zapis i novom tagu se dodijeljuje novi TAG_ID. Ako postoji, onda samo pogledaš koji je TAG_ID već dodan tom tagu. Isto radiš i sa nazivima igrica u tablici IGRA.

Svakako bi trebao proučiti teorijeske osnove baza podataka (nabavi neku knjigu), jer ovako nećeš zapravo puno postići - npr. ne znaš kako u bazi označiti UNIQUE pravilo referencijalnog integriteta, pa bismo otišli u mikromanagement.

No kažem, ne moraš to napraviti - ja sam to samo spomenuo kao nešto ekvivalentno tvojoj originalnoj tablici.

[dee]

Svakako bi trebao proučiti teorijeske osnove baza podataka (nabavi neku knjigu), jer ovako nećeš zapravo puno postići - npr. ne znaš kako u bazi označiti UNIQUE pravilo referencijalnog integriteta, pa bismo otišli u mikromanagement.


ovo sam vec stavljao, odlicno za pocetak:

http://adria.fesb.hr/~vticinov/Baze1.doc

[tsereg]

Odličan tekst, snimio. :)

[Mid]

Hvala tsereg, hvala dee!

Možda sam si uzeo pretežak zadatak za koji ne znam dovoljno o bazama.

Ali za sve postoji prvi put. :)

[tsereg]

Svakako si snimi onaj gornji članak i prolistaj ga.

[dee]

Hvala tsereg, hvala dee!

Možda sam si uzeo pretežak zadatak za koji ne znam dovoljno o bazama.

Ali za sve postoji prvi put. :)


nema tu nikakve velike mudrosti i mistike. samo pokupis par osnovnih pojmova (s onog linka npr.), uhvatis osnovne principe i deres. nista strasno i nikakva atomska fizika ;)