[Laravel & SQL] Krijg query niet goed

[CH4OS]

Ik probeer een soort historie systeem te bouwen in een webapp die ik ontwikkel. Het gaat hierbij om afleveringen en de voortgang, een 1:N relatie dus. Nu wil ik een overzicht maken, van afleveringen, gesorteerd op / met behulp van die historie, waarbij alleen van de nieuwste historie wordt uitgegaan.

Ik heb dus twee tabellen, eentje met afleveringen en wat info, zoals seizoen, afleveringnummer binnen dat seizoen, noem maar op. De historie houdt dan enkel de voortgang bij en daarop moet het overzicht gesorteerd worden.

Nu heb ik als SQL-query het volgende:

SQL:

select
    `episodes`.`id`,
    `episodes`.`plot`,
    `episodes`.`serie_id`,
    `episodes`.`season`,
    `episodes`.`episode`,
    `episodes`.`name`,
    max(episode_histories.x) as x,
    max(episode_histories.y) as y,
    max(episode_histories.z) as z
from `episodes`
inner join `episode_histories` on `episodes`.`id` = `episode_histories`.`episode_id` 
where `completed` = '0' and `episodes`.`deleted_at` is null
group by `episodes`.`id`
order by `x` desc, `y` desc, `z` desc

Dit lijkt te werken. Lijkt, omdat deze query de hoogste waarde van drie kolommen selecteert die ooit ingevuld zijn en niet die van de laatste bijbehorende historie record. An sich kan het systeem hiermee verder, maar ik wil het graag goed doen, het liefst dus zonder die max() in de query te hoeven gebruiken en toch de info van de nieuwste record in de historie direct naar boven hebben.

Om het allemaal nog wat 'leuker' te maken, maak ik voor de webapp gebruik van Laravel en heb dus de beschikking over Eloquent. Ik heb diverse queries geprobeerd (en zit momenteel op deze query wat het dichtste bij het resultaat komt), maar tot nu toe nog niet echt de juiste query gevonden. Ook heb ik totaal geen idee wat ik over het hoofd zie, vandaar dat ik even bij jullie aanklop.

Mochten jullie informatie missen, schroom dan niet om de vraag te stellen!

[Voutloos]

Zoekterm: groupwise maximum. Het maximum is dus de laatste ep/date en met die term leer je hoe je daar op joint en zodoende alles van die episodes_history rij gebruikt.

[CH4OS]

Voutloos schreef op zaterdag 30 juli 2016 @ 08:39:

Maar met max() krijg je de hoogste waarde, niet de nieuwste, ook al is die waarde lager en dat is iets wat ik juist wel wil. Of ik begrijp het nog niet naar aanleiding van wat artikels over groupwise maximum op het internet.

[Feanathiel]

Zoals ik het een beetje lees is het een zelfde probleem als het selecteren van een nieuwste versienummer waarbij major, minor en revision gebruikt worden (vergelijkbaar probleem, kun je eventueel ook op zoeken). Wat je daarbij doet is het probleem opsplitsen in kleinere delen.

Om even een voorbeeld te geven op basis van jouw design: zoek eerst wat de hoogste 'x' is per 'episode_histories.episode_id'. Als je dat weet kun je vervolgens de hoogste 'y' per 'episode_histories.episode_id' met het hoogste 'x' selecteren, enzovoort. Uiteindelijk zul je dan op iets neer komen zoals hier beschreven, waarbij ik alleen de meest relevante informatie heb laten staan. Is dit ook wat jij verwacht als resultaat, dan weet ik dat we elkaar begrijpen?

[ Voor 3% gewijzigd door Feanathiel op 30-07-2016 14:25 ]

[CH4OS]

Feanathiel schreef op zaterdag 30 juli 2016 @ 14:23:

Het is een voortgangssysteem en in de historie wordt bijgehouden wat de voortgang / stand van zaken dan is. Ik wil voor de aflevering dus altijd de laatste historie hebben, niet de hoogste. Ik sla daarbij X, Y of Z niet apart op, maar in 1 record, als iets niet gewijzigd wordt, wordt de waarde desondanks wel meegenomen. De laatste stand van zaken wordt op die manier dus meegenomen.

Ik hoef dus enkel het nieuwste record te hebben van episode_historie, waarbij episode.id = episode_histories.episode_id.

[ Voor 62% gewijzigd door CH4OS op 30-07-2016 16:00 ]

[Voutloos]

CH40S schreef op zaterdag 30 juli 2016 @ 13:35:
[...]
Maar met max() krijg je de hoogste waarde, niet de nieuwste

Max, nieuwste, laatste, meest gemiddelde, saaiste, whatever. Je hebt een criterium voor de top 1 row die je erbij wil joinen. Dan moet je met http://jan.kneschke.de/projects/mysql/groupwise-max/ toch een heel eind komen.

[CH4OS]

Ik ben aan het denken gegaan en bedacht me dat het wellicht een idee is om het andersom aan te pakken; eerst de historie te selecteren en aan de hand van de info wat aan de hand van de join beschikbaar komt te filteren. Hierdoor is mijn query nu als volgt:

SQL:

select h.*, e.name, e.season, e.episode from episode_histories as h
left join episodes as e on e.id = h.episode_id
where e.completed = 0
group by e.id
order by h.x DESC, h.y DESC, h.z DESC

Ik heb het selecteren en filteren dus andersom gedaan, bij wijze van spreken.

EDIT: Hmmm, ook dat werkt nog niet helemaal, maar ik blijf puzzelen.

[ Voor 6% gewijzigd door CH4OS op 30-07-2016 16:36 ]

[Pinobigbird]

Wil je de 'laatste' episode_histories van een episode op basis van hoogste episode_histories.id?

SQL:

SELECT
     e_eh.id
     , e_eh.plot
     , e_eh.serie_id
     , e_eh.season
     , e_eh.episode
     , e_eh.name
     , eh1.x
     , eh1.y
     , eh1.z
FROM (
    SELECT
        e.*
        , (
            SELECT
                eh.id
            FROM episode_histories AS eh
            WHERE 1
            AND eh.episode_id = e.id
            ORDER BY
                eh.updated_at DESC
            LIMIT 1         
        ) AS eh_id
    FROM episodes AS e
    WHERE 1
    AND e.completed = 0
    AND e.deleted_at IS NULL
) AS e_eh
INNER JOIN episode_histories AS eh1 ON e_eh.eh_id = eh1.id

[ Voor 1% gewijzigd door Pinobigbird op 30-07-2016 19:48 . Reden: kommaatje te veel, eh.updated_at ]

[CH4OS]

Pinobigbird schreef op zaterdag 30 juli 2016 @ 17:01:
Wil je de 'laatste' episode_histories van een episode op basis van hoogste episode_histories.id?

Dat is inderdaad precies wat ik wil. Jouw voorbeeld query werkt bij mij echter niet. Intussen blijf ik zelf ook proberen, maar ik krijg het gewoonweg niet voor elkaar, omdat ik het hele 'groupwise maximum' gebeuren voor geen jota begrijp.

[ Voor 69% gewijzigd door CH4OS op 30-07-2016 17:55 ]

[Pinobigbird]

Geen MySQL dan? Of wat is de foutmelding dan?

De binnenste subquery bevat een limit 1 om 1 resultaat te krijgen, namelijk het hoogste eh.id, bij ieder e.id.
De buitenste subquery geeft je dan e.*, en het bijbehorende eh.id.
Buiten de subquery krijg je bovenop de gevonden e.* de eh.* o.b.v. de gevonden eh.id erbij.
Volgens mij is dit wel wat je zoekt.

[CH4OS]

Pinobigbird schreef op zaterdag 30 juli 2016 @ 18:02:
Geen MySQL dan? Of wat is de foutmelding dan?

De foutmelding is dat een fout in de regel zit, geeft niet echt exact aan wat er aan de hand is, wat het exact ook weer was, geen idee. Ik gebruik wel MySQL.

SQL:

SELECT h1.episode_id, h1.x, h1.y, h1.z
  FROM episode_histories AS h1,
       (SELECT *
          FROM episode_histories
         GROUP BY episode_id) AS h2
  WHERE h1.id = h2.id
  GROUP by episode_id

Geeft ook niet terug wat ik hebben wil... Kortom, ik begrijp er echt geen ene jota van, ik doe vast iets verkeerd, maar ik begrijp dat hele (sorry voor mijn taalgebruik) groupwise maximum principe niet en daar begin ik me intussen aan te ergeren ook.

De schema's van de tabellen zijn als volgt:

SQL:

CREATE TABLE `episodes` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `serie_id` INT(3) NOT NULL,
    `season` INT(3) NOT NULL,
    `episode` INT(3) NOT NULL,
    `name` TINYTEXT NOT NULL,
    `completed` INT(11) NOT NULL DEFAULT '0',
    `plot` MEDIUMTEXT NULL,
    `airdate` INT(11) NOT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `serie_id_season_episode` (`serie_id`, `season`, `episode`)
)

Bovenstaande code is de episodes tabel.

SQL:

CREATE TABLE `episode_histories` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `episode_id` INT(11) NOT NULL,
    `x` INT(3) NULL DEFAULT '0',
    `y` INT(3) NULL DEFAULT '0',
    `z` INT(3) NULL DEFAULT '0',
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)

Dit is de episode_histories tabel. Hopelijk geeft dit wat inzicht?

Eigenlijk wil ik dus informatie hebben uit episode_histories, die gejoined is met episodes, alwaar ik ook uit filter, bijvoorbeeld of hij compleet is of niet. Ik wil dus de naam, nummer, seizoen en of hij voltooid is (completed) weten uit episodes tezamen met de nieuwste record uit episodes_historie, alwaar ik z, y en z haal.

[ Voor 135% gewijzigd door CH4OS op 30-07-2016 18:48 ]

[Pinobigbird]

CH40S schreef op zaterdag 30 juli 2016 @ 18:04:
[...]

De foutmelding is dat een fout in de regel zit, geeft niet echt exact aan wat er aan de hand is, wat het exact ook weer was, geen idee. Ik gebruik wel MySQL.
[...]

Net even getest met de create table's en mijn query werkt zonder foutmeldingen in MySQL (5.5.47) en phpMyAdmin (4.4.15).
Overigens zou ik, nu ik de kolommen zie, in mijn query dan eh.id DESC vervangen door eh.updated_at DESC

[CH4OS]

Hmmm, geen idee wat ik net dan verkeerd had, hier werkt hij nu ook... Nu moet ik de query nog even ombouwen naar Eloquent... Dat moet ook wel gaan lukken, zodat ik middels eager loading niet al mijn templates bij langs hoef om een boel eruit te gaan halen omdat er geen models meer gekoppeld zijn.

En ik zie nu, dat ik vergeten ben het type topic om te zetten naar een vraag, zodat ik jouw bericht als beste antwoord kan markeren.

[ Voor 88% gewijzigd door CH4OS op 30-07-2016 19:37 ]