MySQL - Att söka efter taggar

Trädvy Permalänk
Medlem
Plats
Göteborg
Registrerad
Maj 2008

MySQL - Att söka efter taggar

Hej!

Jag försöker få ihop en liten sida för mig själv där jag ska kunna lägga in saker som jag sedan kan gruppera och tagga.

Jag har tre tabeller:

  • objekt

  • grupper

  • taggar

objekt innehåller alla mina objekt
grupper låter mig gruppera mina objekt
taggar låter mig tagga mina objekt

Låt oss fylla tabellerna med innehåll:

objekt:
[id] [namn]
0 fotboll
1 tärning
2 basketboll
3 låda
4 kuvert
5 vindruva

grupper:
[id] [id_grupp] [id_objekt]
0 0 0
1 0 2
2 0 3
3 0 4
4 1 0
5 1 1
6 1 3
7 1 4
8 1 5
(grupp noll innehåller alltså fotboll, basketboll, låda och kuvert, grupp ett innehåller fotboll, tärning, basketboll, kuvert och vindruva)

taggar:
[id] [id_objekt] [id_tagg]
0 0 rund
1 0 sport
3 0 vit
4 1 fyrkantig
5 1 vit
6 2 rund
7 2 sport
8 3 fyrkantig
9 4 fyrkantig
10 5 rund

vi har alltså taggat
fotboll: rund, sport, vit
tärning: fyrkantig, vit
basketboll: rund, sport
låda: fyrkantig
kuvert: fyrkantig
vindruva: rund

Nu vill jag alltså kunna sortera och söka bland all den här datan

Om vi börjar med att printa ut allt i grupp noll:

SELECT objekt.namn FROM objekt JOIN grupper ON(objekt.id=grupper.id_objekt) WHERE grupper.id_grupp = '0';

> fotboll
> basketboll
> låda
> kuvert

snyggt! Nu vill jag kunna söka bland den här gruppen också, låt mig söka efter "sport":

SELECT objekt.namn FROM objekt JOIN grupper ON(objekt.id=grupper.id_objekt) JOIN taggar ON(objekt.id=taggar.id_objekt) WHERE (grupper.id_grupp = '0') AND (tags.tag LIKE '%sport%');

> fotboll
> basketboll

Perfekt! Men det är nu jag stöter på problem,
för jag vet inte hur jag ska göra för att kunna söka på flera taggar samtidigt.
Jag vill t.ex. kunna söka på "fyrkantig & vit" i grupp 1 för att kunna plocka fram enbart tärningen, utan att få med fotbollen (vit) eller låda och kuvert (fyrkantig)

Är det någon som har en idé? Jag är inte speciellt grym på hur join fungerar och känner mig ganska ringrostig på MySQL i övrigt, det här är vad jag har fått ihop under natten

Andra lösningar jag tänkt på är att göra två eller flera sökningar i MySQL och sedan jämföra resultaten med php, för att bara plocka ut de fält som är gemensamma för sökningarna -- men att lösa det med sql känns ju som den bästa lösningen

Tacksam för hjälp!

Canon 5D Mark III | Canon EF 24-70/2,8L II USM | Canon EF 100mm f/2.8L IS USM Macro | Canon EF 135/2,0L USM | Canon TS-E 24mm f/3.5L II | Canon TS-E 17mm f/4L | Canon EF 40mm f/2.8 STM Pancake | Sigma 35mm F1.4 DG HSM Art | Sigma 50mm F1.4 DG HSM Art | Sigma 8mm F3.5 EX DG Circular Fisheye | Canon SpeedLite 600EX-RT | Canon 50D | GoPro HERO3+ Black Edition

Trädvy Permalänk
Medlem
Plats
Göteborg
Registrerad
Maj 2007

Jag tycker du har en denormaliserad databas, t.ex. kommer du få en massa duplicerad data i din tagg-tabell. Jag finner det bättre att lägga upp det med en relationstabell mellan objekt och taggar, så att en tagg existerar en (1) gång i tagg-tabellen, och varje (objekt,tagg)-par existerar en (1) gång i relationstabellen. Hur du menar med grupperna förstår jag inte riktigt...

Så här hade jag lagt upp det:
-----------

create table `objekt` ( `id` int unsigned not null auto_increment, `namn` varchar(35) not null, primary key(`id`) ); create table `taggar` ( `id` int unsigned not null auto_increment, `tagg` varchar(25) not null, primary key(`id`) ); create table `taggadeObjekt` ( `objektId` int unsigned not null, `taggId` int unsigned not null, primary key(`objektId`, `taggId`) );

Nu kan t.ex. ett objekt endast taggas med samma tag en gång (p.g.a. att både objektId och taggId är primärnycklar i relationstabellen).

Som sagt, jag förstår inte riktigt syftet med grupperings-tabellen, då det inte finns något gruppnamn ens? Hursomhelst hade jag lagt upp den på samma sätt - med en relationstabell istället för att duplicera grupp-data.

Kanske inte så mycket till hjälp, jag brukar vara bra på att svamla tidigt på morgonen...

Trädvy Permalänk
Medlem
Plats
Göteborg
Registrerad
Maj 2008

gruppnamn kan jag ju fixa lätt med något liknande:

create table `gruppnamn` ( `id` int unsigned not null auto_increment, `id_grupp` int unsigned not null, `namn` varchar(35) not null, primary key(`id`) );

Det hade jag inte med i exemplet bara, syftet med grupperna är att kunna gruppera mina objekt helt enkelt, utan att behöva tagga dem för det.

Att dela upp det ytterligare i en tabell så som du gjort ser jag vitsen med, det skapar mycket mindre duplicerad data, skulle du kunna slänga med ett exempel på hur jag gör en sökning utifrån det? Som sagt jag är ny på hur JOIN fungerar. Strunta i grupperna om du inte ser logiken i det, jag har en tanke bakom grupperna i mitt huvud

Tack för hjälpen!

Canon 5D Mark III | Canon EF 24-70/2,8L II USM | Canon EF 100mm f/2.8L IS USM Macro | Canon EF 135/2,0L USM | Canon TS-E 24mm f/3.5L II | Canon TS-E 17mm f/4L | Canon EF 40mm f/2.8 STM Pancake | Sigma 35mm F1.4 DG HSM Art | Sigma 50mm F1.4 DG HSM Art | Sigma 8mm F3.5 EX DG Circular Fisheye | Canon SpeedLite 600EX-RT | Canon 50D | GoPro HERO3+ Black Edition

Trädvy Permalänk
Medlem
Plats
Göteborg
Registrerad
Maj 2007

Ja, visst kan du få ett exempel:

/* Hämta ut alla objekt som matchar ett tagg-namn */ select o.id as objektId, o.namn as objektNamn, t.tagg as taggNamn from objekt as o inner join taggadeObjekt as tao on tao.objektId=o.id inner join taggar as t on t.id=tao.taggId where t.tagg='Din tagg'

Jag vet inte om det räcker? Det är ivf inga problem att joina relationstabellen som mellantabell mellan objekt och taggar.

För övrigt tycker ju jag att taggar fungerar som grupperare, det var därför jag undrade lite över den tabellen.

Trädvy Permalänk
Medlem
Plats
Borlänge
Registrerad
Mar 2005

Tabellen "grupper" behöver inte innehålla kolumnen [id]. Eftersom ett objekt sannolikt inte ingår i en grupp mer än en gång kan du låta kolumnerna [id_grupp] och [id_objekt] vara nyckeln.

En snabbt ihophackad query:

SELECT o.id, o.namn, go.id_grupp, g.namn, ot.id_objekt, t.namn FROM objekt o LEFT JOIN grupper_objekt go ON o.id = go.id_objekt LEFT JOIN grupper g ON go.id_grupp = g.id LEFT JOIN objekt_taggar ot ON o.id = ot.id_objekt LEFT join taggar t ON ot.id_tag = t.id WHERE go.id_grupp = 1 AND t.id IN ( SELECT id FROM taggar WHERE t.namn LIKE '%sport%' OR t.namn LIKE '%fyrkantig%' );

Resultat:
1, 'fotboll', 1, 'grupp 1', 1, 'sport'
3, 'basketboll', 1, 'grupp 1', 3, 'sport'
4, 'låda', 1, 'grupp 1', 4, 'fyrkantig'
5, 'kuvert', 1, 'grupp 1', 5, 'fyrkantig'

Så här ser mina tabeller ut:

objekt
[id] [namn]

taggar
[id] [namn]

grupper
[id] [namn]

grupper_objekt
[id_grupp] [id_objekt]

objekt_taggar
[id_objekt] [id_tag]

Bra, snabbt, billigt; välj två.

Ljud
PC → ODAC → Objective2 → Sennheiser HD650/Ultrasone PRO 900
Portabelt → Sennheiser Momentum/Sennheiser Urbanite XL/Sennheiser Momentum In-Ear

Trädvy Permalänk
Medlem
Plats
Göteborg
Registrerad
Maj 2008

oh, grymt tack!
Ser att jag har lite att lära om hur man strukturerar upp requesten på ett snyggt sätt
Men hur skulle jag spinna vidare på detta om jag vill matcha mot t.ex. två eller tre taggar?

Taggar fungerar ju som grupperare på ett sätt, men inte på samma nivå, taggarna ska vara publika medan grupperna ska vara knytna till olika användare, säg att användare ett får tillgång till alla objekt grupp ett, osv.. - Varje användare får sitt "universum" att söka i

Edit: Phod - Tack! Ser skitsnyggt ut, när jag kommer hem ska jag sätta mig ner och gräva in mig ordentligt i det här!

Canon 5D Mark III | Canon EF 24-70/2,8L II USM | Canon EF 100mm f/2.8L IS USM Macro | Canon EF 135/2,0L USM | Canon TS-E 24mm f/3.5L II | Canon TS-E 17mm f/4L | Canon EF 40mm f/2.8 STM Pancake | Sigma 35mm F1.4 DG HSM Art | Sigma 50mm F1.4 DG HSM Art | Sigma 8mm F3.5 EX DG Circular Fisheye | Canon SpeedLite 600EX-RT | Canon 50D | GoPro HERO3+ Black Edition

Trädvy Permalänk
Medlem
Plats
Göteborg
Registrerad
Maj 2007
Citat:

Ursprungligen inskrivet av bjohansson
oh, grymt tack!
Ser att jag har lite att lära om hur man strukturerar upp requesten på ett snyggt sätt
Men hur skulle jag spinna vidare på detta om jag vill matcha mot t.ex. två eller tre taggar?

Bara utöka where-satsen ("where t.tagg='Tagg 1' OR t.tagg='Tagg2' ..."). Däremot kommer queryn att returnera samma objekt flera gånger om flera taggar är associerade med objektet, så för att slippa få samma objekt flera gånger får du gruppera resultatet på objektets id. Om du vill få ut alla taggar i resultatet har MySQL en fin funktion för det, group_concat(). Exempel på hur den används finns i select-satsen i frågan nedanför;

/* Hämta ut alla objekt som matchar ett tagg-namn */ select o.id as objektId, o.namn as objektNamn, group_concat( t.tagg separator ', ' ) as taggNamn from objekt as o inner join taggadeObjekt as tao on tao.objektId=o.id inner join taggar as t on t.id=tao.taggId where t.tagg='Din tagg' or t.tagg='Din andra tagg' or t.tagg='Din tredje tagg' group by o.id

Ger ett resultat liknande;
[1][1] ObjektID
[1][2] ObjektNamn
[1][3] Din tagg, Din tredje tagg
[2][1] ObjektID
[2][2] ObjektNamn
[2][3] Din andra tagg
...

Citat:

Ursprungligen inskrivet av bjohansson
Taggar fungerar ju som grupperare på ett sätt, men inte på samma nivå, taggarna ska vara publika medan grupperna ska vara knytna till olika användare, säg att användare ett får tillgång till alla objekt grupp ett, osv.. - Varje användare får sitt "universum" att söka i

Ah, makes sense.

Edit,
Bra svar av Phod också. Däremot att använda en subquery för att få ut resultat för flera olika taggnamn ställer jag mig undrande till, det ökar bara komplexiteten. Samma resultat går att uppnå i huvudfrågan, som jag visar i min fråga. Men det är möjligt att Phod hade en baktanke.

Trädvy Permalänk
Medlem
Plats
Göteborg
Registrerad
Maj 2008

Tack för den grymma hjälpen, inte greppat att det faktiskt går att strukturera upp sql-queryn sådär innan Riktigt nyttigt med information!

Men jag har en fråga, hur skulle man gå tillväga om man vill vända på mängden resultat? Dvs. att om man söker efter fler taggar så ska man få färre resultat

söker man efter "rund" ska man få fram fotboll, basketboll, vindruva - de objekten innehåller alla taggen rund
men söker man efter "rund", "vit" skulle jag vilja att man bara får fram fotboll, som innehåller båda taggarna, och inte basketboll, vindruva, tärning - som innehåller någon av taggarna men inte alla

Någon idé?

Canon 5D Mark III | Canon EF 24-70/2,8L II USM | Canon EF 100mm f/2.8L IS USM Macro | Canon EF 135/2,0L USM | Canon TS-E 24mm f/3.5L II | Canon TS-E 17mm f/4L | Canon EF 40mm f/2.8 STM Pancake | Sigma 35mm F1.4 DG HSM Art | Sigma 50mm F1.4 DG HSM Art | Sigma 8mm F3.5 EX DG Circular Fisheye | Canon SpeedLite 600EX-RT | Canon 50D | GoPro HERO3+ Black Edition

Trädvy Permalänk
Medlem
Plats
Göteborg
Registrerad
Maj 2007

Det är inte svårare än att ansluta frågan mot ett having-klausul (hoppas jag );

/* Hämta ut alla objekt som matchar ett tagg-namn */ select o.id as objektId, o.namn as objektNamn, group_concat( t.tagg separator ', ' ) as taggNamn from objekt as o inner join taggadeObjekt as tao on tao.objektId=o.id inner join taggar as t on t.id=tao.taggId where t.tagg='Din tagg' or t.tagg='Din andra tagg' or t.tagg='Din tredje tagg' group by o.id having count(*) = 3

Räknaren ska likställas med antalet taggar du matchar mot. Är det två taggar, så ska det alltså vara: "having count(*) = 2".

Trädvy Permalänk
Medlem
Plats
Göteborg
Registrerad
Maj 2008

Det där fungerade hur underbart som helst! Men nu har jag stött på nya problem, det slog mig att det är ju inte säkert att man söker efter hela taggar, säg att man söker efter "din" och "tredje" - båda sökorden kommer hitta samma tagg, "din tredje tagg", count-tillägget kommer ju kräva att resultatet har två unika taggar

where t.tagg like '%din%' or t.tagg like '%tredje%' group by o.id having count(*) = 2

hur löser man detta?

Har slitit en del med php, en lösning jag kom på var att leta upp vilka taggar användaren kan tänkas söka efter, plocka fram dem och sedan söka på enbart existerande taggar, men det var ingen bra idé, då söker man på tecken användaren inte har matat in och filtrerar bort objekt som inte ska filtreras bort.

function hittaTaggar($arrSokord) { /* makeTags tar en array av ord och ger tillbaks en array av taggar som finns knutna till orden */ $arrTaggar = array(); // Den här arrayen kommer innehålla alla taggar // som finns knutna till de ord användaren har matat in foreach ($arrSokord as $sokord) { // Loopia igenom sökorden $sql = "SELECT tagg FROM taggar WHERE tagg LIKE '%$sokord%' "; $resultat=mysql_query($sql) or die("SQL: $sql <br>".mysql_error()); // Gå igenom taggarna sökordet hittade // Och spara i arrayen while ($rad = mysql_fetch_assoc($resultat)) { $arrTaggar[] = htmlspecialchars($rad['tagg']); } } $arrTaggar=array_unique($arrTaggar); // Ta bort dubletter return $arrTaggar; }

Det står verkligen still i huvudet, någon idé?

Canon 5D Mark III | Canon EF 24-70/2,8L II USM | Canon EF 100mm f/2.8L IS USM Macro | Canon EF 135/2,0L USM | Canon TS-E 24mm f/3.5L II | Canon TS-E 17mm f/4L | Canon EF 40mm f/2.8 STM Pancake | Sigma 35mm F1.4 DG HSM Art | Sigma 50mm F1.4 DG HSM Art | Sigma 8mm F3.5 EX DG Circular Fisheye | Canon SpeedLite 600EX-RT | Canon 50D | GoPro HERO3+ Black Edition

Trädvy Permalänk
Medlem
Plats
Göteborg
Registrerad
Maj 2007
Citat:

Ursprungligen inskrivet av bjohansson
Det där fungerade hur underbart som helst! Men nu har jag stött på nya problem, det slog mig att det är ju inte säkert att man söker efter hela taggar, säg att man söker efter "din" och "tredje" - båda sökorden kommer hitta samma tagg, "din tredje tagg", count-tillägget kommer ju kräva att resultatet har två unika taggar

...

hur löser man detta?

Nu kommer vi tillbaka till Phods svar.
Man blir tvungen att använda sig av subselects, så vitt jag vet;

select o.id as objektId, o.namn as objektNamn, group_concat( t.tagg separator ', ' ) as taggNamn from objekt as o inner join taggadeObjekt as tao on tao.objektId=o.id inner join taggar as t on t.id=tao.taggId where t.id in ( select id from taggar where tagg like '%din%' or tagg like '%tredje%' ) group by o.id having count(*)=( select count(id) from taggar where tagg like '%din%' or tagg like '%tredje%' )

Genast blir frågan mycket mer komplex, och kommer ta längre tid att exekvera, bara så att du är på det klara med det. Jag vet inte riktigt hur man skulle kunna optimera den heller...