[SQL/PHP] 2 joins op zelfde tabel => huge alias lijst

[Optix]

Hi,

Ik heb een query die joint 2 keer op de zelfde tabel. Alles prima so far, maar het irritante hiervan is dat hierdoor velden worden overschreven. Beetje verwarrend... ik leg het uit met een voorbeeld, ik heb:

code:

object_id   object_name lastopen_user_id    lastedit_user_id
1           bla         100              103

Velden lastopen_user_id en lastedit_user_id joinen allebei op dezelfde users tabel.

SQL:

SELECT * 
FROM objects o
JOIN users AS lo ON lo.user_id = o.lastopen_user_id
JOIN users AS le ON le.user_id = o.lastedit_user_id

In SQL gaat dit natuurlijk prima en velden in `lo` kan je prima benaderen via lo.user_name. Maarrr als ik dit in PHP terugkrijg als een array zullen alle velden van de gejoinde users tabel worden overschreven omdat ze 2 keer bestaan in het result object.

Nou kan ik natuurlijk alles gaan aliassen

SQL:

SELECT 
    o.*,
    lo.user_name lastopen_user_name,
    le.user_name lastedit_user_name
FROM objects o
JOIN users AS lo ON lo.user_id = o.lastopen_user_id
JOIN users AS le ON le.user_id = o.lastedit_user_id

Maar dat is allemaal handmatig werk, en tevens veel, gezien de daadwerkelijke tabellen heeeel wat meer velden hebben.

Is er dus een manier om dit automatisch te doen of dat ik de resultset van de joins in een nested array terugkrijg ofzo?

[RobIII]

Optix schreef op vrijdag 18 mei 2012 @ 19:50:
Maar dat is allemaal handmatig werk, en tevens veel, gezien de daadwerkelijke tabellen heeeel wat meer velden hebben.

Jammer Jos. Handmatig werk it is.

Optix schreef op vrijdag 18 mei 2012 @ 19:50:
Is er dus een manier om dit automatisch te doen

Niet dat ik weet (en dan wil je 't ook niet; je kunt beter expliciet aangeven wat je hoe wil hebben i.p.v. afgaan op een-of-ander ding dat automatisch namen gaat genereren (foo1, foo2, ...) waarna je bij een upgrade opeens op je bek gaat omdat 't ding dan foo_1, foo_2 doet of foo_a, foo_b ofzo)

Optix schreef op vrijdag 18 mei 2012 @ 19:50:
of dat ik de resultset van de joins in een nested array terugkrijg ofzo?

Nope.

[ Voor 20% gewijzigd door RobIII op 18-05-2012 21:05 ]

[jbdeiman]

SQL betekend ook Structured Query Language, een gestructureerde vraagtaal. Dus als jij je vraag op een goede en gestructureerde manier stelt krijg je het antwoord op je vraag terug. Daar zit nu eenmaal altijd wat handwerk aan vast. In principe hoef je voor de kolommen, als de namen in elke tabel uniek zijn, geen tabel, of alias prefix te gebruiken, maar het maakt onderhoud op de lange termijn wel handiger als je dit wel doet.

Automatisch doen kan opzich wel, maar dat wil je ook niet doen, je zal PHP of een andere scripttaal moeten gebruiken om de query op te bouwen. Dat is niet de meest handige manier.

En zoals mijn voorganger al aangeeft is het beter expliciet aan te geven uit welke tabellen de kolommen komen, dat maakt de logica van de query ook een stuk beter.

Technisch gezien zou je een array op kunnen bouwen:

code:

<?php
array('tablename', => array(0 => 'kolom1', 1 => 'kolom2')
'tablename2' => array('alias' => 'tn', 'joincolum' => 'kolom1', 0 => 'kolom1', 1 => 'kolom2') )
?>

Enzovoorts... Het kan wel op deze manier, maar dan laat je je code je query opbouwen. Als je dat zelf doet, dan is het veel beter te onderhouden en te begrijpen wat er gebeurt.

[ Voor 33% gewijzigd door jbdeiman op 18-05-2012 21:15 ]

[Optix]

RobIII schreef op vrijdag 18 mei 2012 @ 21:03:
Niet dat ik weet (en dan wil je 't ook niet; je kunt beter expliciet aangeven wat je hoe wil hebben i.p.v. afgaan op een-of-ander ding dat automatisch namen gaat genereren (foo1, foo2, ...) waarna je bij een upgrade opeens op je bek gaat omdat 't ding dan foo_1, foo_2 doet of foo_a, foo_b ofzo)

Nou nee, ik zat meer te denken aan "{$alias}_{$veldnaam}". Zou niet weten hoe dit op z'n bek zou moeten gaan. Maar goed het is zoals ik al verwachtte niet mogelijk dus, voor zover bekend.

[Arjen42]

Een relationele database is geen echte relationele database als niet ook de meta data opvraagbaar is. Zou kun je met een relatief eenvoudige query alle kolommen van een tabel opvragen. Met wat extra gesleutel kun je dan ook nog het grootste gedeelte van je query genereren. Dan hoef je verder alleen nog de puntjes op de i te zetten.

Dit kan met een query zoals deze:

SQL:

DECLARE @table1 varchar(64);
DECLARE @table2 varchar(64);
DECLARE @table3 varchar(64);

DECLARE @column1 varchar(64);
DECLARE @column2 varchar(64);
DECLARE @column3 varchar(64);

SET @table1 = 'objects';
SET @table2 = 'users';
SET @table3 = 'users';

SET @column1 = 'user_id';
SET @column2 = 'lastopen_user_id';
SET @column3 = 'lastedit_user_id';

SELECT 'SELECT '
UNION ALL
SELECT 
    't1."' + c.COLUMN_NAME + '" AS ' + c.COLUMN_NAME + '_t1, '
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = @table1
UNION ALL
SELECT 
    't2."' + c.COLUMN_NAME + '" AS ' + c.COLUMN_NAME + '_t2, '
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = @table2
UNION ALL
SELECT 
    't3."' + c.COLUMN_NAME + '" AS ' + c.COLUMN_NAME + '_t3, '
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = @table3
UNION ALL
SELECT 'NULL AS __NULL FROM "' + @table1 + '" t1 JOIN "' + @table2 + '" t2 ON t1."' + @column2 + '" = t2."' + @column1 + '" JOIN "' + @table3 + '" t3 ON t1."' + @column3 + '" = t3."' + @column1 + '"'

[Optix]

jbdeiman schreef op vrijdag 18 mei 2012 @ 21:09:
SQL betekend ook Structured Query Language, een gestructureerde vraagtaal. Dus als jij je vraag op een goede en gestructureerde manier stelt krijg je het antwoord op je vraag terug. Daar zit nu eenmaal altijd wat handwerk aan vast. In principe hoef je voor de kolommen, als de namen in elke tabel uniek zijn, geen tabel, of alias prefix te gebruiken, maar het maakt onderhoud op de lange termijn wel handiger als je dit wel doet.

Of de veldnamen nou uniek zijn of niet maakt niet zoveel uit natuurlijk als je 2 keer joint op de zelfde tabel en indd ik probeer zoveel mogelijk zelfde veldnamen aan te houden, is wel zo duidelijk op de lange termijn (ik zie nog wel eens in andermans projecten alle tabellen primairy key `id` hebben *zucht*).

Arjen42 schreef op vrijdag 18 mei 2012 @ 21:26:
Een relationele database is geen echte relationele database als niet ook de meta data opvraagbaar is. Zou kun je met een relatief eenvoudige query alle kolommen van een tabel opvragen. Met wat extra gesleutel kun je dan ook nog het grootste gedeelte van je query genereren. Dan hoef je verder alleen nog de puntjes op de i te zetten.

Uiteraard is de meta data opvraagbaar en dat zal dan ook de enige "oplossing" zijn, maar dat is me te veel performance verlies voor een klein dingetje als dit.

[Arjen42]

Optix schreef op vrijdag 18 mei 2012 @ 21:45:
Uiteraard is de meta data opvraagbaar en dat zal dan ook de enige "oplossing" zijn, maar dat is me te veel performance verlies voor een klein dingetje als dit.

Ik bedoel dat je dit tijdens development gebruikt om je query te bakken. Niet tijdens runtime.

Op z'n hoogst zou je dit in een DDL trigger kunnen plaatsen zodat als een tabel gewijzigd wordt de bijbehorende views en/of stored procedures aangepast zouden worden. Handig als je vaak je tabellen wijzigt.