MYSQL: JOIN-trubbel + optimeringstips

Tråden skapades och har fått 6 svar. Det senaste inlägget skrevs .
1
  • Medlem
  • Höganäs
  • 2004-03-18 14:16

Förutsättningar:

Två tabeller
"timmar"...
+---------+------+
| timme_id | timme |
+---------+------+
| 1 | 7
| 2 | 8
| 3 | 9
| 4 | 10
etc
| 17 | 23
+---------+------+------+------+------+

... och "bokningar"
+---------+------+------+------+------+
| id | start | slut | datum | aktivitet |
+---------+------+------+------+------+
| 2101 | 9:00:00 | 10:30:00 | 2004-03-11 | Testar |
| 2102 | 12:00:00 | 13:30:00 | 2004-03-18 | Testar | (obs datum!)
| 2103 | 14:30:00 | 15:45:00 | 2004-03-18 | Testar | (obs datum!)
| 2104 | 12:00:00 | 15:30:00 | 2004-03-28 | Testar |
| 2105 | 9:00:00 | 12:30:00 | 2004-03-29 | Testar |
+---------+------+------+------+------+
(innehåller ca 3000+ rader.)

Med sökningen:

$sql = ("
SELECT timmar.timme, 
	bokningar.Datum, 
	bokningar.Aktivitet, 
	TIME_FORMAT( AktStart, '%H.%i' ) AS aktst, 
	TIME_FORMAT( AktSlut, '%H.%i' ) AS aktsl
FROM timmar 
LEFT JOIN bokningar ON timmar.timme = TIME_FORMAT( AktStart, '%k' ) 
WHERE bokningar.datum = '2004-03-18'
ORDER BY timmar.timme, bokningar.AktStart
");

Hittas följande:
+---------+------+------+------+------+
| timme | Datum | Aktivitet | aktst | aktsl |
+---------+------+------+------+------+

| 8 | 2004-03-18 | Testar| 12.00 | 13.30 |
| 9 | 2004-03-18 | Testar | 14.00 | 15.45 |
+---------+------+------+------+------+

Jag vill ju att ALLA timmar visas, även om de saknar motsvarighet i "bokningar"...

Det, upptäckte jag, ordnas till viss del genom att lägga till

OR bokningar.Datum IS NULL

i WHERE - satsen.

Ger :

+---------+------+------+------+------+
| timme | Datum | Aktivitet | aktst | aktsl |
+---------+------+------+------+------+
| 7 | NULL | NULL | NULL | NULL |
| 12 | 2004-03-18 | Testar| 12.00 | 13.30 |
| 14 | 2004-03-18 | Testar | 14.00 | 15.45 |
| 21 | NULL | NULL | NULL | NULL |
| 22 | NULL | NULL | NULL | NULL |
etc
+---------+------+------+------+------+
[B|Notera att "timme" inte visas 7-23![/B]

Det jag VILL visa är ju:

+---------+------+------+------+------+
| timme | Datum | Aktivitet | aktst | aktsl |
+---------+------+------+------+------+
| 7 | NULL | NULL | NULL | NULL |
| 8 | NULL | NULL | NULL | NULL |
| 9 | NULL | NULL | NULL | NULL |
| 10 | NULL | NULL | NULL | NULL |
| 11 | NULL | NULL | NULL | NULL |
| 12 | 2004-03-18 | Testar| 12.00 | 13.30 |
| 13 | NULL | NULL | NULL | NULL |
| 14 | 2004-03-18 | Testar | 14.00 | 15.45 |
| 15 | NULL | NULL | NULL | NULL |
| 16 | NULL | NULL | NULL | NULL |
| 17 | NULL | NULL | NULL | NULL |
etc
| 23 | NULL | NULL | NULL | NULL |
+---------+------+------+------+------+

Om du orkat läsa så här långt:
Hur kan jag åstadkomma detta?

Fråga 2:
Om du detta går att ordna, hur optimerar jag JOIN-funktionen så att sökningen går så snabbt som möjligt?

Tacksam för hjälp...

/M

Vill du ha en väl fungerande join så måste du ha en referens (foreign key) i bokningar tabellen till timmar tabellen.

Ett annat problem är ju att timmar och bokningar inte har med varandra att göra. Timmar verkar ju bara vara en uppläsning av hela klockslag som jag inte ser nyttan med.

Antingen skippar du timmar tabellen helt och kombinerar det du får ut med sql frågan med php-kod för att få en komplett tabell

elller så ser du till att ha samma skalning av tiden i din timmar tabell som i bokningar. Enligt exemplen verkar det gå att skapa bokningstider på kvartstimmar (10:00, 10:15, 10:30,...) och då borde du ha motsvarande poster i timmar tabellen.

En annan möjlighet är ju att bokningar innehåller en referens timmar tabellen samt en deltatid, t.ex att 10:30 skrivs som två fält: 4 (referens till timme_id) i det första fältet och 0:30 (antal minuter) i det andra.

Joakim

  • Medlem
  • Höganäs
  • 2004-03-18 17:09
Ursprungligen av jocked:

Vill du ha en väl fungerande join så måste du ha en referens (foreign key) i bokningar tabellen till timmar tabellen.

Ett annat problem är ju att timmar och bokningar inte har med varandra att göra. Timmar verkar ju bara vara en uppläsning av hela klockslag som jag inte ser nyttan med.

timmar.timme = TIME_FORMAT( AktStart, '%k' ) gör ju att timme paras mot heltimmen i klockslaget i AktStart.
Är det en dålig lösning?

Ursprungligen av jocked:

Antingen skippar du timmar tabellen helt och kombinerar det du får ut med sql frågan med php-kod för att få en komplett tabell

Tidigare gjorde jag en for-loop med klockslag där jag gjorde en ny sökning i "bokningar" för varje klockslag.
Min tanke med ovan beskrivna förfarande är att göra en (1) sökning istället för 17...

Hmm. Det borde väl gå att fixa detta på något smidigt sätt!?

Ursprungligen av Micke F:

timmar.timme = TIME_FORMAT( AktStart, '%k' ) gör ju att timme paras mot heltimmen i klockslaget i AktStart.
Är det en dålig lösning?

Strikt sett ja, om du ska ha en referens från en tabell till en annan så id:t från den ena tabellen (timmar) lagras i den andra (bokningar) och därför vara av samma typ.

Det egentliga problemet som jag ser det är ju att timmar är inga variabler utan bara ett gäng konstanter du lagrar i databasen. Dessa konstanter kan lika gärna deklareras i php-koden eller en konfigureringsfil.

Ursprungligen av Micke F:

Tidigare gjorde jag en for-loop med klockslag där jag gjorde en ny sökning i "bokningar" för varje klockslag.
Min tanke med ovan beskrivna förfarande är att göra en (1) sökning istället för 17...

Hmm. Det borde väl gå att fixa detta på något smidigt sätt!?

Skapa en array i php som är av samma storlek som antal poster du nu har i tabellen timmar.

SELECT TIME_FORMAT( AktStart, '%k' ) AS timme,
....bokningar.Datum,
....bokningar.Aktivitet,
....TIME_FORMAT( AktStart, '%H.%i' ) AS aktst,
....TIME_FORMAT( AktSlut, '%H.%i' ) AS aktsl
FROM timmar WHERE bokningar.datum = '2004-03-18'
ORDER BY bokningar.AktStart

och sedan lägger in varje post i array:en med timme som index korrigerat med vilken timme som är den första, t.ex om 07:00 är starttiden som motsvarar index 0 i tabellen så ska varje post in i array:en på index [timme - 7="7"].

Joakim

  • Medlem
  • Höganäs
  • 2004-03-19 00:53

Jag skapar nu värdet "startTimme" i bokningar via PHP. Samma datatyp som timme i "timmar" (INT)

En Array kan vara en lösning, men då vissa klockslag har flera träffar, kan det bli lite knöligt. Tycker jag.

Har för övrigt hittat rätt söksträng:

SELECT
  timmar.timme,
  bokningar.Datum, 
  bokningar.Aktivitet, 
  bokningar.StartTimme,
  TIME_FORMAT( AktStart, '%H.%i' ) AS aktst, 
  TIME_FORMAT( AktSlut, '%H.%i' ) AS aktsl  
FROM
  timmar 
LEFT JOIN 
  bokningar 
ON 
  (timmar.timme = bokningar.StartTimme
AND 
  bokningar.Datum = '$datum')
WHERE 
  ( timmar.timme >= '$start' 
AND
 timmar.timme  <= '$slut')

Visar:
+---------+------+------+------+------+
| timme | Datum | Aktivitet | aktst | aktsl |
+---------+------+------+------+------+
| 7 | NULL | NULL | NULL | NULL |
| 8 | NULL | NULL | NULL | NULL |
| 9 | NULL | NULL | NULL | NULL |
| 10 | NULL | NULL | NULL | NULL |
| 11 | NULL | NULL | NULL | NULL |
| 12 | 2004-03-18 | Testar| 12.00 | 13.30 |
| 13 | NULL | NULL | NULL | NULL |
| 14 | 2004-03-18 | Testar | 14.00 | 15.45 |
| 15 | NULL | NULL | NULL | NULL |
| 16 | NULL | NULL | NULL | NULL |
| 17 | NULL | NULL | NULL | NULL |
etc
| 23 | NULL | NULL | NULL | NULL |
+---------+------+------+------+------+

Jag missade att det kunde vara flera bokningar per timme. Nu verkar du fått till det bra.

Glöm inte bort 'ORDER BY' bara, men den kanske du bara missade när du klippte in koden.

Joakim

  • Medlem
  • Höganäs
  • 2004-03-19 08:45

Just det ja. Tack för "bollplankshjälpen" !

1
Bevaka tråden