PFZ.nl - PHP Community: PgSQL: char als array van ints verwerken? - Databases - Forum - PFZ.nl - PHP Community

Je kunt niet antwoorden op dit topic
#1 20-08-2012 17:37
  • Jasperdv
  • Groep: Forumleden
  • Posts: 13
  • Actief sinds: 20-02-2012
Hoi!

Ik ben bezig met een systeem waarmee mensen elkaar berichten kunnen sturen. Je kan naar meerdere mensen tegelijk een bericht sturen, en wanneer dat zo is, dan sla ik alle user-id's op waar het bericht heen wordt gestuurd.
In mijn tabel 'berichten' heb ik dan dus een kolom 'user_naar' waar het user-id van de geadresseerde in staat, en ik heb een kolom 'geadresseerden' waar alle geadresseerden in staan.
Als een bericht dus naar gebruiker 3 en 5 wordt gestuurd, dan heb ik dit in mijn database:
user_naar | geadresseerden
3 | 3,5
5 | 3,5

Die 3,5 is in mijn database een character-datatype.

Nu wil ik bij het ophalen van deze id's de gebruikersnamen erbij geven, dus user 3 is Herman, user 5 is Klaas, dan wil ik dat mijn query "Herman, Klaas" teruggeeft.

Ik heb daarvoor dit query-stukje gebakken (even overdreven uitgelijnd voor de overzichtelijkheid):
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
ARRAY_TO_STRING
(
    ARRAY
    (
        SELECT
        	l.gebruikersnaam
        FROM
        	leden l
        WHERE
        	l.id IN
                (
                   b.geadresseerden
                )
    )
), \', \' ) AS geadresseerden_naam


maar die geeft mij dit terug:

Quote

pg_prepare(): Query failed: ERROR: operator does not exist: integer = character
LINE 3: ...( SELECT l.gebruikersnaam FROM leden l WHERE l.id IN (b.gead...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Dat is natuurlijk logisch, want l.id is een integer, en b.geadresseerden is gewoon een character.
Ik moet dus op de een of andere manier b.geadresseerden als array van integers zien te casten. Maar hoe doe ik dat?

1
CAST(b.geadresseerden AS array)

werkt niet omdat 'array' geen geldig datatype is in PgSQL. Casten als integer werkt uiteraard ook niet, want 3,5 is geen integer, en dat zal '3' opleveren als je dat cast naar een integer.

Dit werkt helaas ook niet:
1
STRING_TO_ARRAY(b.geadresseerden, \',\')

met deze melding:

Quote

pg_prepare(): Query failed: ERROR: operator does not exist: integer = text[]
LINE 3: ...( SELECT l.gebruikersnaam FROM leden l WHERE l.id IN (string...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Hoe kan ik dit het beste doen?

Bovenstaand query-stukje is deel van een andere query, anders had ik alle usernames gewoon los kunnen selecten en ze in PHP omzetten naar een string met komma's ertussen om dat weer te geven, maar dat gaat nu dus niet.
Ik kan er evt een losse query van maken, maar het lijkt me zo handiger/efficiënter.

Alvast bedankt!
#2 20-08-2012 17:59
Heb je een bijzonder ontiegelijk bizar goede reden om dit niet gewoon genormaliseerd te doen met een aparte tabel met geadresseerden? En vooraf; dat je dan veel records krijgt is geen geldige reden. :-)
DELETE FROM world WHERE dbms='mysql';
http://www.yapf.net - http://yapf.blogspot.com/
#3 20-08-2012 18:02
  • Jasperdv
  • Groep: Forumleden
  • Posts: 13
  • Actief sinds: 20-02-2012

Bekijk Post Op 20-08-2012 18:59 schreef PgVincent www.yapf.net:

Heb je een bijzonder ontiegelijk bizar goede reden om dit niet gewoon genormaliseerd te doen met een aparte tabel met geadresseerden? En vooraf; dat je dan veel records krijgt is geen geldige reden. :-)
Nope, die heb ik niet. Gaat dat niet ten koste van de snelheid? Want nu moet ik sowieso onderdelen van het bericht selecten, dus is een character-rij met geadresseerden in no-time meegeselecteerd; als ik de geadresseerden in een aparte tabel doe dan lijkt me de kans groot dat de boel langzamer wordt?
#4 20-08-2012 18:19

Quote

Want nu moet ik sowieso onderdelen van het bericht selecten, dus is een character-rij met geadresseerden in no-time meegeselecteerd;
Klopt, en met die string kun je vervolgens niets, dus moet je hem converteren naar een array waar je vervolgens doorheen moet lopen om de echte gegevens van de personen erbij te halen, en dan heb ik het nog niet over wat er gebeurt als een van de geadresseerden wordt gewist, laat staan hoe je gaat zoeken naar berichten die aan iemand zijn gericht...

Quote

als ik de geadresseerden in een aparte tabel doe dan lijkt me de kans groot dat de boel langzamer wordt?
Meten is weten, ga niet afwijken van de standaard manieren omdat je *denkt* dat het te traag is, doe het omdat je *weet* dat jouw methode sneller is.
DELETE FROM world WHERE dbms='mysql';
http://www.yapf.net - http://yapf.blogspot.com/
#5 20-08-2012 19:54
  • Jasperdv
  • Groep: Forumleden
  • Posts: 13
  • Actief sinds: 20-02-2012

Bekijk Post Op 20-08-2012 19:19 schreef PgVincent www.yapf.net:

Meten is weten, ga niet afwijken van de standaard manieren omdat je *denkt* dat het te traag is, doe het omdat je *weet* dat jouw methode sneller is.
Om nou altijd per se de standaard-manieren te gebruiken 'omdat ze nu eenmaal de standaard zijn' dat vind ik wat bekrompen. Als iets duidelijk handiger werkt dan zie ik geen probleem om dan gewoon voor die optie te gaan.

Mijn optie die ik wil bereiken (nml. met 1 subquery toch alle namen gescheiden door een komma ophalen) kost me een stuk minder tijd om te maken; als ik bij alles wat ik nu maak uitgebreid ga zitten testen wat de snelste methode is, dan kost me dat bergen met tijd. Tijd die ik achteraf beter in het optimaliseren kan stoppen als ik wéét wat de knelpunten zijn, i.p.v. nu gokken wat de knelpunten gaan zijn.
Ik snap dat als ik bij alles wat ik maak uitgebreid test wat de snelste methode is, dat ik dan een razendsnelle site krijg. Maar ik ga liever voor de optie dat ik nu bedenk wat me het snelste líjkt, en als de site klaar is en uitgebreid met shitloads aan data en load getest kan en gaat worden, dat ik dan enkele delen van de site herschrijf en efficiënter maak.
Uiteraard worden de vaak ingeladen onderdelen van de site wél nu al goed getest en voorzien van de meest optimalie uitwerkingen. Dit (waar het topic over gaat/ging) is voor het versturen van privéberichten aan meerdere personen. Dat komt al weinig voor, dus om daar nu uren ontwikkeltijd extra in te stoppen terwijl de functie later als hij eenmaal op de productieserver staat misschien 20 keer per dag uitgevoerd wordt, dat zie ik niet zo zitten.

Bekijk Post Op 20-08-2012 19:19 schreef PgVincent www.yapf.net:

en dan heb ik het nog niet over wat er gebeurt als een van de geadresseerden wordt gewist
Het zijn gebruikersnamen, leden. Leden die ik nooit en te nimmer verwijder, hooguit een anonieme gebruikersnaam geef. (De site draait al een aantal jaren in huidige vorm, en nog nooit is een lid verwijderd.)

Bekijk Post Op 20-08-2012 19:19 schreef PgVincent www.yapf.net:

laat staan hoe je gaat zoeken naar berichten die aan iemand zijn gericht...
Dat doe ik niet via die reeks user-id's. Elk bericht is gericht aan een user_id, dus als een bericht naar 10 personen wordt gestuurd, dan staat dat bericht 10x met een verschillende user_id in de database. Dat klinkt misschien zinloos en dat is het misschien ook, maar anders ga ik tig tabellen erbij krijgen omdat elke user_id aparte handelingen met zijn bericht kan doen (bericht verwijderd, lezen, als ongelezen markeren, etc.), en daar komt dus ook veel extra uren ontwikkel-werk bij kijken.
Maargoed, daarmee kan ik dus simpelweg SELECT datum, bericht FROM berichten WHERE user_aan = 123 doen, en dan heb ik iedereen.

Als ik later dingen wil toevoegen waarmee je bijv. kan zoeken op 'welke mails zijn tegelijk naar user 123 en 234 maar niet naar user 198 gestuurd?' dan kan ik dan nog mijn data-model omgooien. Ik bewonder de gedachte om vooraf overal rekening mee te houden, maar als iets me nu beduidend meer ontwikkeltijd kost dan implementeer ik liever een eenvoudigere methode die óók goed werkt, zij het minder perfect en minder met het oog op de toekomst gericht.
#6 20-08-2012 22:33
  • Martin P
  • Groep: Moderators
  • Posts: 5284
  • Actief sinds: 19-04-2007

Bekijk Post Op 20-08-2012 21:54 schreef Jasperdv:

Om nou altijd per se de standaard-manieren te gebruiken 'omdat ze nu eenmaal de standaard zijn' dat vind ik wat bekrompen.
Klopt, mee eens, maar heb je een goede motivatie om van die standaard af te wijken? Die standaarden zijn er nl. niet voor niets. Als je weet hoe je met een genormaliseerd datamodel werkt, is het een fluitje van een cent om het op die manier te doen. In dit geval is het simpelweg het handigst om met een genormaliseerd model te werken.

Bekijk Post Op 20-08-2012 21:54 schreef Jasperdv:

Als iets duidelijk handiger werkt dan zie ik geen probleem om dan gewoon voor die optie te gaan.
Als het duidelijk handiger werkt, was dit topic er niet geweest, toch? :)

Bekijk Post Op 20-08-2012 21:54 schreef Jasperdv:

Mijn optie die ik wil bereiken (nml. met 1 subquery toch alle namen gescheiden door een komma ophalen) kost me een stuk minder tijd om te maken; als ik bij alles wat ik nu maak uitgebreid ga zitten testen wat de snelste methode is, dan kost me dat bergen met tijd. Tijd die ik achteraf beter in het optimaliseren kan stoppen als ik wéét wat de knelpunten zijn, i.p.v. nu gokken wat de knelpunten gaan zijn.
Alles op voorhand testen, doe ik ook niet. Je gaat uit van de standaardmanier en wijkt daar pas vanaf als dit niet handig blijkt te zijn. Jij doet precies het omgekeerde: je wijkt af van de standaardmanier voordat je überhaupt weet of het nodig is om van die standaardmanier af te wijken. En snelheid.. ach, we hebben het eigenlijk niet eens over snelheid, maar enkel over gebruiksgemak. Ik kan me niet voorstellen dat je de query in je openingspost heel veel makkelijker vindt dan een join.

Het blijft altijd lastig om mensen te overtuigen dat databasenormalisatie veel handiger is, maar ik hoop dat je de goedbedoelde adviezen ter harte neemt. Je hebt er alleen maar voordeel van.
Handleidingen zijn er niet voor niets, gebruik ze dus :)
HTML5 ~ CSS ~ PHP ~ MySQL ~ SQL-injectie bestaat al sinds 1998
#7 21-08-2012 07:29

Quote

Om nou altijd per se de standaard-manieren te gebruiken 'omdat ze nu eenmaal de standaard zijn' dat vind ik wat bekrompen. Als iets duidelijk handiger werkt dan zie ik geen probleem om dan gewoon voor die optie te gaan.
Daar heb ik twee antwoorden op: #1 het werkt niet duidelijk handiger, het is zelfs onwerkbaar omdat je geen referentiele integriteit kunt afdwingen. (en geef toe; hoe handig kan een oplossing zijn als je een post moet doen om uit te zoeken hoe je een bepaalde handeling moet uitvoeren?)
#2 databases worden al sinds de jaren 60 ontwikkeld en in al die tijd is de genormaliseerde oplossing altijd als beste uit de bus gekomen.Databases worden ook ontworpen om goed met genormaliseerde modellen te kunnen werken, dus de kans dat het voor jouw toepassing anders zal zijn is niet heel groot. Als je een minuutje googlet vind je daarentegen een hele berg informatie over waarom CSV data in een veld niet werkt.

Quote

Mijn optie die ik wil bereiken (nml. met 1 subquery toch alle namen gescheiden door een komma ophalen) kost me een stuk minder tijd om te maken;
Dat is niet waar, je hebt zelfs hulp van het forum nodig om hem uberhaupt werkend te maken, dat geeft al aan dat je iets doet wat niemand anders doet, en dus iets wat blijkbaar niet zoveel toepassingen kent.

Quote

als ik bij alles wat ik nu maak uitgebreid ga zitten testen wat de snelste methode is, dan kost me dat bergen met tijd.
Tijd die ik achteraf beter in het optimaliseren kan stoppen als ik wéét wat de knelpunten zijn, i.p.v. nu gokken wat de knelpunten gaan zijn.
Dus, waarom kies je dan niet voor de methode al sinds de kjare 60 prima werkt, en ga je die aanpassen als hij toch niet snel genoeg blijkt?

Quote

Maar ik ga liever voor de optie dat ik nu bedenk wat me het snelste líjkt, en als de site klaar is en uitgebreid met shitloads aan data en load getest kan en gaat worden, dat ik dan enkele delen van de site herschrijf en efficiënter maak.
jamaar, "wat jou het snelste lijkt" is puur natte vinger werk. Het lijkt jou sneller , dus verspil je liever je tijd aan het bouwen van een oplossing die je straks weer moet gaan ombouwen. Dat is 100% verspilde tijd, terwijl een simpele proof-of-concept je in een paar uur kan laten zien dat jouw idee helaas niet gaat werken.

Quote

Dat komt al weinig voor, dus om daar nu uren ontwikkeltijd extra in te stoppen terwijl de functie later als hij eenmaal op de productieserver staat misschien 20 keer per dag uitgevoerd wordt, dat zie ik niet zo zitten.
Eh? Je begint je verhaal met het idee dat jouw oplossing sneller zou moeten zijn dan de standaardoplossing, en nu blijt dat snelheid eigenljk helemaal niet zo belangrijk is. Als dat zo is dan kies je toch helemaal nooit voor een niet-standaard opplossing?

Als je de boel gewoon normaliseert dan kun je alles wat jij wilt doodeenvoudig doen, overzichtelijk, gebrijpelijk en kwalitatief goed vanaf het begin. Je hoeft er niets voor uit te zoeken, geen aparte casts te proberen,alles kan werken met de standaard opties van PHP en je database.

Quote

Het zijn gebruikersnamen, leden. Leden die ik nooit en te nimmer verwijder, hooguit een anonieme gebruikersnaam geef. (De site draait al een aantal jaren in huidige vorm, en nog nooit is een lid verwijderd.)
Ja die ken ik, "het gaat echt niet gebeuren". Meestal gebeurt het een week later stiekem toch.

Quote

Dat doe ik niet via die reeks user-id's. Elk bericht is gericht aan een user_id, dus als een bericht naar 10 personen wordt gestuurd, dan staat dat bericht 10x met een verschillende user_id in de database.
Eh... je slaat elk bericht apart op per geadresseerde, dus daarin heb je per bericht per record al één geadresseerde. Waarheb je dan die CSV string voor nodig?

Quote

Dat klinkt misschien zinloos en dat is het misschien ook, maar anders ga ik tig tabellen erbij krijgen omdat elke user_id aparte handelingen met zijn bericht kan doen (bericht verwijderd, lezen, als ongelezen markeren, etc.), en daar komt dus ook veel extra uren ontwikkel-werk bij kijken.
Wederom eh? Waarom zouden berichten dike worden verwijderd in een aparte tabel komen te staan? Daar maak je natuurlijk gewoon een kolom voor die aangeeft wat de handeling is voor het bericht in dat record.

Quote

Ik bewonder de gedachte om vooraf overal rekening mee te houden, maar als iets me nu beduidend meer ontwikkeltijd kost
Dat kost het niet. Je hebt duidelijk weinig ervaring met het ontwerpen van databases en de oplossingen zoals jij denkt dat ze uiteindeijk zouden moeten zijn zijn inderdaad een hoop werk, maar ze zijn ook fout.

Als je de standaard volgt en je database correct normaliseert dan krijg je een datamodel waarin je automatisch al zo'n beetje overal rekening mee hebt gehouden en waarin je heel snel en eenvoudig aanpassingen kunt maken om nieuwe functies te gaan ondersteunen.

Normalisatie kost geen tijd, het spaart je tijd, ook als jij vindt van niet. :)
DELETE FROM world WHERE dbms='mysql';
http://www.yapf.net - http://yapf.blogspot.com/

Nieuwe Topics

Scripts

Wiki's

Vacatures

Nieuwsberichten

Software Updates

Inloggen wachtwoord vergeten? Aanmelden