[Laravel] Bulk create or update

[Schonhose]

Via een formulier krijg ik grote hoeveelheden data binnen die ik wil opslaan. Met de kennis die ik heb van Laravel heb ik verschillende methoden gevonden, maar deze methoden vuren allemaal veel queries af op de database. Ik ben bang dat ik door de beperkte kennis van Laravel iets simpels over het hoofd zie.

Case
Met Laravel maak ik een website waar ik wedstrijdstatistieken kan opslaan van een team. Een team bestaat uit maximaal 12 spelers en elke speler heeft 12 verschillende statistieken die worden opgeslagen. De opslag van de data vindt als volgt plaats:

game_id	player_id	stat_id	value
1	1	1	100
1	1	2	340
..	..	..	..

De invoer van de data aan de gebruikerskant vindt plaats via een formulier. Deze is zo opgebouwd dat er per speler een rij aan data is, bestaande uit 12 verschillende invoervelden. Het verzamelen van deze data is geen probleem, maar ik loop tegen bepaalde dingen aan bij het verwerken ervan.

Verwerken data
Er zijn twee verschillende bewerkingen mogelijk: a) aanmaken van een nieuwe game en de daaraangekoppelde spelersstatistieken en b) het bewerken van een eerder opgeslagen game en de daaraan gekoppelde statistieken. Het verwerken van bewerking a is simpel, dit zijn allemaal inserts. Het verwerken van bewerking b ligt wat ingewikkelder. Dit kan namelijk een combinatie zijn van update of een insert (in het geval van niet bestaande data in de database)

Oplossingen
Op dit moment heb ik twee oplossingen:

Oplossing 1:

Aan de hand van een foreach loop verwerk ik per speler de 12 statistieken. Hierbij maak ik gebruik van Modelnaam::firstOrNew om te kijken of er al een record is of dat er een nieuwe aangemaakt moet worden. In beide gevallen krijg ik een 'collection' terug waarvan ik de waarden kan vervangen door de ingegeven waarden en via ->save() kan opslaan in de database.

Voordeel Ongeacht of het een nieuw of een bestaand record is werkt deze methode altijd dankzij ::firstOrNew. In het geval van bestaande records is het een update, in het geval van een nieuwe een insert.
Nadeel: Deze methode vuurt elke keer twee queries af: een om de data op te halen en een om de data te bewaren. Voor 12 spelers met 12 statistieken zijn dit al 2*(12*12) = 288 queries.

Oplossing 2:

Oplossing 2 gaat nog steeds uit van de foreach loop, maar inplaats van eerst de data op te halen en dan te verwerken doen we dit in een keer met ::updateOrCreate:

PHP:

Model::updateOrCreate(
   ['game_id' => 1, 'player_id' => 1, 'stat_id' => 1],
   ['value' => 'value']
);

Voor zover ik begrepen heb probeert deze functie de data te updaten en als dat niet lukt wordt het een insert. Waarschijnlijk is dit voor MySQL 1 query waardoor het aantal queries ten opzichte van oplossing 1 met een factor 2 verminderd wordt. Voor zover ik begrepen heb kan ik niet een array met meerdere rijen tegelijk aanbieden, iets wat met ::insert wel kan.

Variant op oplossingen 1 en 2
Deze oplossing brengt me op een variant voor zowel oplossing 1 als oplossing 2: indien er een nieuwe game aangemaakt wordt kan ik de statistieken altijd via een ::insert doen. Hierbij kan ik een array meegeven met meerdere records. Analoog hieraan kan ik bij het bewerken het aantal update queries terugbrengen aan de hand van de volgende procedure. In de foreach loop wordt een collection opgevraagd of aangemaakt. In het laatste geval heeft de collection nog geen unieke id omdat dit record nog niet bestaat. Bij het bewerken van een bestaande record heeft de collection wel een unieke id. In dat geval kan ik checken of de waarde uit het formulier anders is dan de waarde in de database. Als dat niet het geval is, is het opslaan van de collection via ->save() dus niet nodig. Hiermee beperk ik het aantal update queries tot het bewerken van alleen de gewijzigde waarden. Nadeel is dan nog steeds wel dat je voor elke parameter eerst de data moet opvragen en je zo dus nog steeds 144 queries + eventuele updates uitvoert. In dat geval kan ik nog beter ::updateOrCreate doen, hiermee is het aantal queries altijd 144.

Bovenstaande variant vereist natuurlijk dat ik wel wat logic in mijn controller ga programmeren en voordat ik dit ga doen wil ik wel graag weten of er geen andere mogelijkheden zijn.

[HyperioN]

Klopt je database-model wel?

Er zijn dus altijd precies 12 "stats" per player per game? Dat staat dus vast?
In dat geval kan je tabel ook zo, lijkt me:

code:

game_id | player_id | stat1 | stat2 | stat3 | ... | stat11 | stat12

In de kolommen stat1 t/m stat12 zet je dan de values van die bepaalde "stat" bij die player en game.
game_id + player_id zijn samen je primary key (PK). Dus dan kun je het nog op basis van "INSERT OR UPDATE" blijven doen.

In dat geval beperk je je al tot (max) 12 queries per game.

Overigens: als de verschillende stats toch vaststaan; zou ik ook aanraden om je kolomnamen dan wel wat beschrijvender te maken, bijv:

code:

game_id | player_id | goals | assists | fouls | ... | etc

[Merethil]

HyperioN schreef op donderdag 11 februari 2016 @ 21:37:
Klopt je database-model wel?

Er zijn dus altijd precies 12 "stats" per player per game? Dat staat dus vast?
In dat geval kan je tabel ook zo, lijkt me:

code:

game_id | player_id | stat1 | stat2 | stat3 | ... | stat11 | stat12

In de kolommen stat1 t/m stat12 zet je dan de values van die bepaalde "stat" bij die player en game.
game_id + player_id zijn samen je primary key (PK). Dus dan kun je het nog op basis van "INSERT OR UPDATE" blijven doen.

In dat geval beperk je je al tot (max) 12 queries per game.

Overigens: als de verschillende stats toch vaststaan; zou ik ook aanraden om je kolomnamen dan wel wat beschrijvender te maken, bijv:

code:

game_id | player_id | goals | assists | fouls | ... | etc

Dat is pas een manier om je databasemodel onbruikbaar te maken op lange termijn. Het is niets meer dan een koppeltabel die hij wil aanleggen. Stel hij wilt graag een extra (optionele) stat toevoegen, vind jij dan dat hij zijn model en database moet aanpassen, of dat hij toch gewoon via een stamtabel een extra record kan invoeren?

Het is en blijft vrij makkelijk: je krijgt op die manier veel queries. Maar 144 queries is niet bijzonder veel. Indien je écht wilt kijken of je dat kan verminderen zou je altijd nog een transactie kunnen starten, daar een bulk insert/update in doen en dan weer sluiten; je database voert dat dan als één query uit (afhankelijk van je DB, maar MySQL en MSSQL wel iig).
Probleem is dan alleen dat je geen update kan doen, maar als je nou alles waar al een Primary in de model voor aanwezig is update in één transactie, en alles waar dat niet in zit insert in één transactie, dan heb je maar twee queries nodig


Edit: ik zie nu dat je dan een complexe primary key hebt. Waarom? Je kan toch gewoon een ID'tje eraan hangen die je als identifier voor de rij gebruikt? Die hoeft je niet te tonen in je UI maar is wel handig voor updaten/inserten.

[ Voor 6% gewijzigd door Merethil op 11-02-2016 22:03 ]

[HyperioN]

Merethil schreef op donderdag 11 februari 2016 @ 22:01:
[...]


Dat is pas een manier om je databasemodel onbruikbaar te maken op lange termijn. Het is niets meer dan een koppeltabel die hij wil aanleggen.

Ben ik niet met je eens. Als iets een vast aantal velden heeft, en altijd een 1:1 relatie, waarom zou je dan gaan normaliseren en een koppeltabel gaan gebruiken?
Als je bijvoorbeeld een simpel gastenboek schrijft:

code:

message_id | name | content | datetime

Met bijv. een row:

code:

243 | Sjaakie | Hoi dit is een bericht | 2016-02-11 22:08:00

Dan ga je toch ook niet normaliseren naar:

code:

message_id | field | value

met de rows:

code:

243 | name | Sjaakie
243 | content | Hoi dit is een bericht
243 | datetime | 2016-02-11 22:08:00

Of zie ik dat nou verkeerd?

[Merethil]

HyperioN schreef op donderdag 11 februari 2016 @ 22:13:
[...]

Ben ik niet met je eens. Als iets een vast aantal velden heeft, en altijd een 1:1 relatie, waarom zou je dan gaan normaliseren en een koppeltabel gaan gebruiken?
Als je bijvoorbeeld een simpel gastenboek schrijft:

code:

message_id | name | content | datetime

Met bijv. een row:

code:

243 | Sjaakie | Hoi dit is een bericht | 2016-02-11 22:08:00

Dan ga je toch ook niet normaliseren naar:

code:

message_id | field | value

met de rows:

code:

243 | name | Sjaakie
243 | content | Hoi dit is een bericht
243 | datetime | 2016-02-11 22:08:00

Of zie ik dat nou verkeerd?

Klopt, maar als je nou andere statistieken later wilt gaan bijhouden? Hoe weet je of dit alle velden die je ooit gaat gebruiken zijn?

Een gastenboek is voorgedefinieerd, je kan verwachten dat daar niet zomaar extra data in nodig gaat zijn. Statistieken daarentegen... Vooral als het gaat om statistieken om wedstrijden, of de teams zelf, of de behaalde score of....

Je weet gewoon niet van tevoren dat je niet toevallig ook het gemiddelde gewicht van team A of het gemiddelde inkomen van team Z nodig gaat hebben. Waarom dat niet normaliseren zodat ooit het toevoegen van dat veld een stuk makkelijker kan dan een deel van je logica omschrijven?

[Schonhose]

HyperioN schreef op donderdag 11 februari 2016 @ 22:13:
[...]
Als iets een vast aantal velden heeft, en altijd een 1:1 relatie, waarom zou je dan gaan normaliseren en een koppeltabel gaan gebruiken?

Ja, niet natuurlijk. Maar het is precies zoals Merethil het omschrijft: nu heb ik 12 stats, geen idee wat er nog bijkomt.

Vroeger had ik 5 stats en had ik mijn database model precies zoals jij in de bovenstaande posts aangeeft. Toen werden het er meer, en daarna nog meer en toen kwamen er nog twee bij.... Het gevolg was dat ik niet alleen mijn model elke keer moest aanpassen, maar ook de onderliggende queries uitbreiden. Hoe vaak ik wel niet een update query was vergeten ergens waardoor stats niet meer in mijn database kwamen.

Het database model geeft wat meer overhead, maar is makkelijker uit te breiden.

Merethil schreef op donderdag 11 februari 2016 @ 22:01:
Het is en blijft vrij makkelijk: je krijgt op die manier veel queries. Maar 144 queries is niet bijzonder veel. Indien je écht wilt kijken of je dat kan verminderen zou je altijd nog een transactie kunnen starten, daar een bulk insert/update in doen en dan weer sluiten; je database voert dat dan als één query uit (afhankelijk van je DB, maar MySQL en MSSQL wel iig).
Probleem is dan alleen dat je geen update kan doen, maar als je nou alles waar al een Primary in de model voor aanwezig is update in één transactie, en alles waar dat niet in zit insert in één transactie, dan heb je maar twee queries nodig

Dat is op zich wel een goed idee. Ware het niet dat ik dan nog steeds een request moet doen om te bepalen of ik iets in de update of in de insert transactie moet doen.

Edit: ik zie nu dat je dan een complexe primary key hebt. Waarom? Je kan toch gewoon een ID'tje eraan hangen die je als identifier voor de rij gebruikt? Die hoeft je niet te tonen in je UI maar is wel handig voor updaten/inserten.

Ik denk dat je dit haalt uit de updateOrCreate statement waar ik inderdaad een complexere key nodig ben. Dit is logisch, ik weet namelijk niet wat het id is van het record. Ik kan dit niet gebruiken voor het updaten. Om hierachter te komen moet ik eerst een request naar de database doen, en daarna kan ik pas mijn actie bepalen.

In het geval van de updateOrCreate statement heb ik een aantal variabelen waarvan de combinatie uniek is. Deze variabelen weet ik op het moment dat ik mijn form submit. UpdateOrCreate is juist zo handig omdat deze onder de motorkap volgens mij 1 query uitvoert die zowel kan toevoegen of updaten. Maar daarvoor heb ik wel een unieke sleutel nodig.

Ik heb vooralsnog gewoon oplossing 2 (updateOrCreate) toegepast en dat werkt wel ok. De wachttijd is acceptabel, mede omdat dit niet iets is wat uurlijks gedaan moet worden. Wat wel grappig is, is dat je het om meerdere manieren kunt doen, en oplossing 1 had meer overhead dan oplossing 2.

[Merethil]

Schonhose schreef op zaterdag 13 februari 2016 @ 23:13:

Dat is op zich wel een goed idee. Ware het niet dat ik dan nog steeds een request moet doen om te bepalen of ik iets in de update of in de insert transactie moet doen.


[...]

Daarom zou ik zelf dus ook een AI primary key toevoegen; komt op je regel die primary key al voor dan is het een update, anders een insert.
Dit werkt natuurlijk alleen als je al bestaande data reeds inlaadt in het invoerformulier, ik ben er nou niet zeker van of je dat doet of niet. Zo niet, dan is het inderdaad altijd een updateOrCreate, maar dan is er mijn inziens alsnog wat mis met je opbouw van je systeem.

De reden dat je die data reeds wilt hebben opgehaald voor speler X bij het invoeren is voornamelijk zodat mensen niet per ongeluk dingen overschrijven. Indien je dat niet doet... Tja, ik gok dat er veel fouten gaan voorkomen dan.

[Schonhose]

Merethil schreef op maandag 15 februari 2016 @ 08:24:
[...]
Daarom zou ik zelf dus ook een AI primary key toevoegen; komt op je regel die primary key al voor dan is het een update, anders een insert.
Dit werkt natuurlijk alleen als je al bestaande data reeds inlaadt in het invoerformulier, ik ben er nou niet zeker van of je dat doet of niet.

Aha, ik snap nu inderdaad wat je bedoeld. Ik kan inderdaad een unique id, wat overigens een inhoudloze sleutel is, ophalen en deze via HTML 5 data attribute aan mijn formulier hangen. Dan weet ik meteen bij welke id de waarde hoort. Is er een id, dan weet ik ook dat ik een update moet doen en anders een insert.

Zo niet, dan is het inderdaad altijd een updateOrCreate, maar dan is er mijn inziens alsnog wat mis met je opbouw van je systeem.

Vanuit het gezichtspunt dat jij blijkbaar de voorkeur geeft aan 'meaningless id's' als primary key voor updaten en insert heb je gelijk dat mijn opbouw in jouw ogen niet klopt. Overigens ben ik van mening dat jou redenering an sich correct is, maar ik ben geen voorstander van 'meaningless id's'. In dit geval is mijn primary key samengesteld uit de combinatie van game_id, player_id en stat_id. Deze combinatie is uniek.

De reden dat je die data reeds wilt hebben opgehaald voor speler X bij het invoeren is voornamelijk zodat mensen niet per ongeluk dingen overschrijven. Indien je dat niet doet... Tja, ik gok dat er veel fouten gaan voorkomen dan.

Geredeneerd vanuit de 'meaningless id's' heb je volkomen gelijk. Echter, de combinatie van bovengenoemde id's is uniek en daarmee automatisch een primary key. Door het gebruik van UpdateOrCreate waardoor er impliciet al een check zit of mijn gecombineerde primary key bestaat of niet, zie ik geen reden waarom er "veel fouten gaan voorkomen dan".

[Merethil]

Schonhose schreef op dinsdag 16 februari 2016 @ 06:57:
[...]


Aha, ik snap nu inderdaad wat je bedoeld. Ik kan inderdaad een unique id, wat overigens een inhoudloze sleutel is, ophalen en deze via HTML 5 data attribute aan mijn formulier hangen. Dan weet ik meteen bij welke id de waarde hoort. Is er een id, dan weet ik ook dat ik een update moet doen en anders een insert.


[...]


Vanuit het gezichtspunt dat jij blijkbaar de voorkeur geeft aan 'meaningless id's' als primary key voor updaten en insert heb je gelijk dat mijn opbouw in jouw ogen niet klopt. Overigens ben ik van mening dat jou redenering an sich correct is, maar ik ben geen voorstander van 'meaningless id's'. In dit geval is mijn primary key samengesteld uit de combinatie van game_id, player_id en stat_id. Deze combinatie is uniek.


[...]


Geredeneerd vanuit de 'meaningless id's' heb je volkomen gelijk. Echter, de combinatie van bovengenoemde id's is uniek en daarmee automatisch een primary key. Door het gebruik van UpdateOrCreate waardoor er impliciet al een check zit of mijn gecombineerde primary key bestaat of niet, zie ik geen reden waarom er "veel fouten gaan voorkomen dan".

Een meaningless ID is niet meaningless als hij er hier voor zorgt dat je je data consistent kan houden
Hoe dan ook: mijn laatste opmerking sloeg vooral op wat je doet als je de data niet eerst zou ophalen maar een gebruiker steeds zelf alles los laat invullen, dan zouden ze de boel per ongeluk kunnen overschrijven. In het geval van een meaningless ID zou het dan een insert worden ipv update, het is aan jou welke preferabel is.
Indien je je data wel gewoon ophaalt voor je de mensen toevoegingen/aanpassingen laat maken, dan is dat natuurlijk geen issue.

De enige reden waarom ik een meaningless ID prefereer is dan ook omdat het db-gegenereerd is, en je dus met zekerheid kan zeggen dat het een update of insert moet zijn, scheelt gewoon veel queries uiteindelijk