[sql] Inner where date closest to now and next

Overzicht Reageren

Sponsored by: Vacatures door Monsterboard

Pagina: 1 2 volgende »

Michael -

Michael -

14/05/2014 08:48:36
Quote Anchor link
Allen,

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)
PHP script in nieuw venster Selecteer het PHP script
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
    $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
            )
        )
    ";

Geeft error: SQLSTATE[HY000]: General error: 1111 Invalid use of group function

Bvd
 
PHP hulp

PHP hulp

11/04/2021 22:53:57
 
Dos Moonen

Dos Moonen

14/05/2014 09:00:44
Quote Anchor link
MIN(), MAX(), AVG() en anderen zijn functies die alleen gebruikt kunnen worden als je een GROUP BY clause hebt.

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
 
Michael -

Michael -

14/05/2014 09:10:38
Quote Anchor link
> In dit geval zou je met een ORDER BY pr.datum_start ASC LIMIT 1 moeten krijgen wat hebben wilt. En MIN() weglaten natuurlijk.

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.
 
Dos Moonen

Dos Moonen

14/05/2014 09:30:54
Quote Anchor link
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
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
            )
...

Dat is wat ik bedoelde.
 
Erwin H

Erwin H

14/05/2014 10:01:36
Quote Anchor link
En LIMIT 2 als je de eerstvolgende en de daarop volgende wil hebben...
 
Michael -

Michael -

14/05/2014 10:17:41
Quote Anchor link
Ah, dan begreep ik je verkeerd :) Bedankt voor t voorbeeld. Deze geeft alleen een time-out 'SQLSTATE[HY000]: General error: 2013 Lost connection to MySQL server during query'
 
Ger van Steenderen
Tutorial mod

Ger van Steenderen

14/05/2014 10:26:39
Quote Anchor link
>> 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.

@Michael
Waarom gebruik je een LEFT JOIN en controleer je daarna een kolom van de gejoinde tabel op IS NOT NULL?
 
Erwin H

Erwin H

14/05/2014 10:29:59
Quote Anchor link
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.

Helemaal gelijk natuurlijk, ik had niet in de gaten dat die subquery op die manier gebruikt werd....
 
Michael -

Michael -

14/05/2014 10:44:09
Quote Anchor link
> Waarom gebruik je een LEFT JOIN en controleer je daarna een kolom van de gejoinde tabel op IS NOT NULL?

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?
 
Ger van Steenderen
Tutorial mod

Ger van Steenderen

14/05/2014 11:22:55
Quote Anchor link
>> Omdat er data doorheen kwam die niet compleet was. Als de titel null is heb ik er niks aan.

Dat begrijp ik, maar waarom dan een LEFT JOIN, een INNER JOIN heeft hetzelfde effect.

Welke query heb je nu?
 
Michael -

Michael -

14/05/2014 11:32:01
Quote Anchor link
Ah oké. Moet toegeven dan JOIN's maar lastige dingen blijven al lijken ze zo makkelijk :)

tot nu toe
Code (php)
PHP script in nieuw venster Selecteer het PHP script
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
    $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
            )
    ";

Dit levert oneindig laden op of soms een 'lost connection'
 
Erwin H

Erwin H

14/05/2014 11:36:44
Quote Anchor link
Als je die subquery nu eens los uitvoert eenmalig voordat je deze query aanroept. Het resultaat ervan voeg je gewoon in. Werkt het dan wel goed? Ik kan me namelijk voorstellen dat die subquery veel te vaak aangeroepen gaat worden op deze manier (namelijk, voor elke rij).
 
Michael -

Michael -

14/05/2014 11:46:59
Quote Anchor link
> Ik kan me namelijk voorstellen dat die subquery veel te vaak aangeroepen gaat worden op deze manier (namelijk, voor elke rij).

Hm, als die die select op elke rij doet dan is ie er wel druk mee ja :)
Is er een andere manier?

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
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
    ";


Geeft als output:
2014-05-14 00:01:00-2014-05-14 03:14:00

Ook niet helemaal wat het zijn moet dus.
 
Ger van Steenderen
Tutorial mod

Ger van Steenderen

14/05/2014 11:51:50
Quote Anchor link
Als de programma's opeenvolgend zijn:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
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
Gewijzigd op 14/05/2014 11:52:14 door Ger van Steenderen
 
Michael -

Michael -

14/05/2014 11:54:02
Quote Anchor link
is CURRENT_DATE niet alleen een datum? Met NOW() gaat 't ietsjes beter, maar hij gaat eentje te ver..
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
WHERE
            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
 
Erwin H

Erwin H

14/05/2014 11:55:22
Quote Anchor link
Waarom is dat niet wat het moet zijn? Merk op dat CURRENT_DATE alleen de datum teruggeeft, niet de tijd!

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)
PHP script in nieuw venster Selecteer het PHP script
1
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;

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 ;-)
 
Michael -

Michael -

14/05/2014 11:59:47
Quote Anchor link
> 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?
 
Ger van Steenderen
Tutorial mod

Ger van Steenderen

14/05/2014 12:13:31
Quote Anchor link
Ik join eerst programs met als extra voorwaarde dat het nu bezig moet zijn (np = now_playing)
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.
 
Michael -

Michael -

14/05/2014 12:21:28
Quote Anchor link
Nice :) Bedankt!
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?
 
Ger van Steenderen
Tutorial mod

Ger van Steenderen

14/05/2014 12:35:05
Quote Anchor link
>>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.

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.
 
Michael -

Michael -

14/05/2014 13:04:23
Quote Anchor link
Bedankt. We laten het voorlopig zo :) Mocht het gebeuren dat het problemen oplevert zie ik dat tegen die tijd wel.
 

Pagina: 1 2 volgende »



Overzicht Reageren

 
 

Om de gebruiksvriendelijkheid van onze website en diensten te optimaliseren maken wij gebruik van cookies. Deze cookies gebruiken wij voor functionaliteiten, analytische gegevens en marketing doeleinden. U vindt meer informatie in onze privacy statement.