[sql] Inner where date closest to now and next
Het is de bedoeling dat een 'closest event' en de daarop volgende worden weergeven.
Ik heb 2 tabellen. Één moet gewoon 'gejoint' zijn en de andere bevat de datums datum_start en datum_end.
Nou wil ik de data die het dichtst bij nu ligt (of een eigen datum) of wat in ieder geval tussen start en end valt. Maar ook de daarop volgende.
Dus nu en volgende. Zoiets als Nu&Straks van een tvgids.
Lastig uitgelegd, maar kan iemand me op weg helpen?
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
$sql = "
SELECT
ch.id,
ch.name,
ch.name_short,
pr.channel_id,
pr.db_id,
pr.titel,
pr.datum_start,
pr.datum_end
FROM
" . TABLE_PREFIX . "channels AS ch
LEFT JOIN
" . TABLE_PREFIX . "programs AS pr
ON ch.id = pr.channel_id
WHERE
pr.titel IS NOT NULL
AND (
CURRENT_DATE BETWEEN
pr.datum_start
AND
pr.datum_end
OR
pr.datum_start =
(
SELECT
MIN(pr.datum_start)
FROM
" . TABLE_PREFIX . "programs
WHERE
pr.datum_start > CURRENT_DATE
)
)
";
SELECT
ch.id,
ch.name,
ch.name_short,
pr.channel_id,
pr.db_id,
pr.titel,
pr.datum_start,
pr.datum_end
FROM
" . TABLE_PREFIX . "channels AS ch
LEFT JOIN
" . TABLE_PREFIX . "programs AS pr
ON ch.id = pr.channel_id
WHERE
pr.titel IS NOT NULL
AND (
CURRENT_DATE BETWEEN
pr.datum_start
AND
pr.datum_end
OR
pr.datum_start =
(
SELECT
MIN(pr.datum_start)
FROM
" . TABLE_PREFIX . "programs
WHERE
pr.datum_start > CURRENT_DATE
)
)
";
Geeft error: SQLSTATE[HY000]: General error: 1111 Invalid use of group function
Bvd
In dit geval zou je met een ORDER BY pr.datum_start ASC LIMIT 1 moeten krijgen wat hebben wilt. En MIN() weglaten natuurlijk.
Gewijzigd op 14/05/2014 09:01:21 door Dos Moonen
Er is natuurlijk ook data wat in het verleden ligt en data dat in de toekomst ligt. Dus sorteren zou mijn alleen de eerste datum geven, niet de datum die hoort bij 14 mei 2014 10 over 9.
Daarnaast werkt LIMIT 1 ook niet, want ik wil van elke ch.id de tijd die bij nu hoort. En de daarop volgende.
Sorry voor de onduidelijke uitleg.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
...
pr.datum_start =
(
SELECT
pr.datum_start
FROM
" . TABLE_PREFIX . "programs
WHERE
pr.datum_start > CURRENT_DATE
ORDER BY
pr.datum_start ASC
LIMIT 1
)
...
pr.datum_start =
(
SELECT
pr.datum_start
FROM
" . TABLE_PREFIX . "programs
WHERE
pr.datum_start > CURRENT_DATE
ORDER BY
pr.datum_start ASC
LIMIT 1
)
...
Dat is wat ik bedoelde.
Een subquery in de where achter = kan nooit meer dan één waarde teruggeven.
@Michael
Waarom gebruik je een LEFT JOIN en controleer je daarna een kolom van de gejoinde tabel op IS NOT NULL?
Ger van Steenderen op 14/05/2014 10:26:39:
>> En LIMIT 2 als je de eerstvolgende en de daarop volgende wil hebben...
Een subquery in de where achter = kan nooit meer dan één waarde teruggeven.
Een subquery in de where achter = kan nooit meer dan één waarde teruggeven.
Helemaal gelijk natuurlijk, ik had niet in de gaten dat die subquery op die manier gebruikt werd....
Omdat er data doorheen kwam die niet compleet was. Als de titel null is heb ik er niks aan.
Iemand nog een idee waarom de query blijft laden of error lost connection gooit?
Dat begrijp ik, maar waarom dan een LEFT JOIN, een INNER JOIN heeft hetzelfde effect.
Welke query heb je nu?
tot nu toe
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
$sql = "
SELECT
ch.id,
ch.name,
ch.name_short,
pr.channel_id,
pr.db_id,
pr.datum_start,
pr.datum_end
FROM
" . TABLE_PREFIX . "channels AS ch
LEFT JOIN
" . TABLE_PREFIX . "programs AS pr
ON ch.id = pr.channel_id
WHERE
pr.datum_start =
(
SELECT
pr.datum_start
FROM
" . TABLE_PREFIX . "programs
WHERE
pr.datum_start > CURRENT_DATE
ORDER BY
pr.datum_start ASC
LIMIT 1
)
";
SELECT
ch.id,
ch.name,
ch.name_short,
pr.channel_id,
pr.db_id,
pr.datum_start,
pr.datum_end
FROM
" . TABLE_PREFIX . "channels AS ch
LEFT JOIN
" . TABLE_PREFIX . "programs AS pr
ON ch.id = pr.channel_id
WHERE
pr.datum_start =
(
SELECT
pr.datum_start
FROM
" . TABLE_PREFIX . "programs
WHERE
pr.datum_start > CURRENT_DATE
ORDER BY
pr.datum_start ASC
LIMIT 1
)
";
Dit levert oneindig laden op of soms een 'lost connection'
Hm, als die die select op elke rij doet dan is ie er wel druk mee ja :)
Is er een andere manier?
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
$sql = "
SELECT
pr.titel,
pr.datum_start,
pr.datum_end
FROM
" . TABLE_PREFIX . "programs AS pr
WHERE
pr.datum_start > CURRENT_DATE
ORDER BY
pr.datum_start ASC
LIMIT 1
";
SELECT
pr.titel,
pr.datum_start,
pr.datum_end
FROM
" . TABLE_PREFIX . "programs AS pr
WHERE
pr.datum_start > CURRENT_DATE
ORDER BY
pr.datum_start ASC
LIMIT 1
";
Geeft als output:
2014-05-14 00:01:00-2014-05-14 03:14:00
Ook niet helemaal wat het zijn moet dus.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT
ch.id,
ch.name,
ch.name_short,
np.titel np_titel,
np.datum_start np_start,
np.datum_end np_end,
ne.titel ne_titel,
ne.datum_start ne_start,
ne.datum_end ne_end
FROM
channels ch
INNER JOIN
programs np
ON ch.id = np.channel_id AND NOW() BETWEEN np.datum_start AND np.datum_end
INNER JOIN
programs ne
ON ch.id = ne.channel_id AND np.datum_end = ne.datum_start
ch.id,
ch.name,
ch.name_short,
np.titel np_titel,
np.datum_start np_start,
np.datum_end np_end,
ne.titel ne_titel,
ne.datum_start ne_start,
ne.datum_end ne_end
FROM
channels ch
INNER JOIN
programs np
ON ch.id = np.channel_id AND NOW() BETWEEN np.datum_start AND np.datum_end
INNER JOIN
programs ne
ON ch.id = ne.channel_id AND np.datum_end = ne.datum_start
Gewijzigd op 14/05/2014 11:52:14 door Ger van Steenderen
Code (php)
1
2
2
WHERE
pr.datum_start >= NOW()
pr.datum_start >= NOW()
Nu: 11:51
2014-05-14 11:52:00-2014-05-14 11:54:00
2014-05-14 11:53:00-2014-05-14 12:01:00
Nu: 11:52
2014-05-14 11:53:00-2014-05-14 12:01:00
2014-05-14 11:54:00-2014-05-14 12:30:00
Wat je wellicht kan doen is die subquery ombouwen tot een join en aan de hand daarvan filteren. Dan doe je de berekening maar 1 keer in elk geval. Overigens of het op die manier in een subquery echt voor elke rij gebeurt weet ik niet, maar dat is wel mijn vermoeden.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SELECT
ch.id,
ch.name,
ch.name_short,
pr.channel_id,
pr.db_id,
pr.datum_start,
pr.datum_end
FROM
" . TABLE_PREFIX . "channels AS ch
LEFT JOIN
" . TABLE_PREFIX . "programs AS pr
ON ch.id = pr.channel_id
INNER JOIN (
SELECT
datum_start
FROM
" . TABLE_PREFIX . "programs
WHERE
datum_start > CURRENT_DATE
ORDER BY
datum_start ASC
LIMIT 1
) a ON pr.datum_start = a.datum_start;
ch.id,
ch.name,
ch.name_short,
pr.channel_id,
pr.db_id,
pr.datum_start,
pr.datum_end
FROM
" . TABLE_PREFIX . "channels AS ch
LEFT JOIN
" . TABLE_PREFIX . "programs AS pr
ON ch.id = pr.channel_id
INNER JOIN (
SELECT
datum_start
FROM
" . TABLE_PREFIX . "programs
WHERE
datum_start > CURRENT_DATE
ORDER BY
datum_start ASC
LIMIT 1
) a ON pr.datum_start = a.datum_start;
Snelle ombouw, zo'n omzetting van subquery naar join kan nogal eens wat anders zijn dan je in eerste instantie denkt, maar volgens mij zou dit moeten kunnen.
Toevoeging op 14/05/2014 11:56:28:
Alweer te laat :-)
Quote:
Nu: 11:52
Het is bijna nooit 11:52:00, maar bijna altijd een paar seconden meer ;-)
ha ha ;) bedankt voor je voorbeeld!
@Ger; Ja voorbeeld lijkt helemaal te werken! Maar heb je nou eigenlijk gedaan?
Daarna join ik programs (ne=next) nog eens met als extra voorwaarde dat de eindtijd van now_playing overkomt met de starttijd van next.
Je hebt alleen een 'probleem' als er op dit moment geen programma bezig is.
Dit betekent wel dat ik alle velden dubbel moet ophalen (Er zijn er nog meer)? Eenmaal voor np en eenmaal voor ne. Maar dat lijkt me niet zo'n performance probleem.
> Je hebt alleen een 'probleem' als er op dit moment geen programma bezig is.
Dat heb ik nu nog niet, maar zou natuurlijk ooit kunnen voorkomen. Ik kan natuurlijk in PHP even controleren of ie is geset en niet leeg is.
Als er voor één van beide geen programma is, wordt er niks weergegeven?
Het aantal kolommen in de SELECT heeft geen noemenswaardige invloed op de performance
>>Als er voor één van beide geen programma is, wordt er niks weergegeven?
Klopt.
Het kan wel met een left join, maar dan moet de tweede join met een subquery.