[sql] verschachtelte queries zusammenfassbar?

[rasputin]

ich habe folgenden code, kann man die beiden queries zusammenfassen?

$projects = array();
$result = $db->query('SELECT * FROM projects ORDER BY project_state ASC, project_name ASC');
while ($row = $db->fetch_array($result)) {

	$assistant_names = $db->query_all('SELECT CONCAT(a.assistant_lastname, " ", a.assistant_firstname) as assistant_name FROM assistants a INNER JOIN assistant2project p USING (assistant_ID) WHERE p.project_ID = '.$row['project_ID'].' ORDER BY a.assistant_lastname ASC, a.assistant_firstname ASC', 'assistant_name');
	$row['assistants'] = implode(', ', $assistant_names);
	
	$projects[] = $row;
}

$tpl->assign('projects', $projects);

 

[hambam]

Gibt es denn gemeinsame Felder in der Tabelle im ersten Select und einer der beiden anderen Tabellen?

Grundsätzlich kannst Du "beliebig" viele Joins verwenden um Tabelleninhalte miteinander zu vermischen.

 

[Albu]

Mir ist kein Weg bekannt, mittels eines wie auch immer gearteten Selects die Childs zu einem Feld zusammen zu fassen..

Denkbar wäre aber eine Trigger-basierte Lösung, bei der jedes Insert, Update und Delete in der Child-Tabelle eine solche, künstliche Spalte im Master aktuell hält.

 

[skooli]

diese Lösung holt allerdings die Projektdaten redundant für jeden Assistenten, außerdem ist [assistants] am ende kein nach , implodierter string sondern ein array mit allen namen, was sich aber, ausgehend davon dass du smarty als template engine benutzt, mit dem schnipsel weiter unten wieder lösen lässt

$projects = array();
$result = $db->query('SELECT *, CONCAT(`a`.`assistant_lastname`, " ", `a`.`assistant_firstname`) as `assistant_name`
                        FROM `projects`
                        LEFT JOIN `assistant2project` USING(`project_ID`)
                        ORDER BY `project_state` ASC, `project_name` ASC, `assistant_name` ASC');

while($row = $db->fetch_array($result)) {
    if(!isset($projects[$row['project_ID']])) {
        $projects[$row['project_ID']] = array_merge($row, array('assistants' => array()));
    }
    array_push($projects[$row['project_ID']]['assistants'], $row['assistant_name']);
}

$tpl->assign('projects', $projects);

{foreach from=$projects item=project}
    {$project.project_name}, Assistenten: {','|implode:$project.assistants}
{/foreach}

deine Lösung dürfte aber insgesamt eleganter sein da keine daten redundant geholt werden (naja, dafür query in ner schleife :x )

 

[rasputin]

jetzt kommt der knüller: es gibt eine 1-query-lösung:

SELECT p.*, GROUP_CONCAT(CONCAT( a.assistant_firstname, ' ', a.assistant_lastname ) ORDER BY a.assistant_firstname ASC, a.assistant_lastname ASC SEPARATOR ', ') AS assistants
FROM projects p
INNER JOIN assistant2project a2p ON ( p.project_ID = a2p.project_ID )
INNER JOIN assistants a ON ( a2p.assistant_ID = a.assistant_ID )
GROUP BY p.project_ID
ORDER BY p.project_state ASC, p.project_name ASC

diese abfrage liefert allerdings alle projekte zurück. ich möchte nun alle projekte, bei denen ein bestimmter assistent involviert ist. irgendwie so:

SELECT p.project_ID, p.project_name , GROUP_CONCAT(CONCAT( a.assistant_firstname, ' ', a.assistant_lastname ) ORDER BY a.assistant_firstname ASC, a.assistant_lastname ASC SEPARATOR ', ') AS assistants
FROM projects p
INNER JOIN assistant2project a2p ON ( p.project_ID = a2p.project_ID )
INNER JOIN assistants a ON ( a2p.assistant_ID = a.assistant_ID )
[COLOR="Red"]WHERE a.assistant_ID = 7[/COLOR]
GROUP BY p.project_ID
ORDER BY p.project_state ASC, p.project_name ASC

dies liefert logischerweise immer nur noch den assistent mit der id 7 und nicht eine liste mit allen assistenten, von denen einer die id 7 hat.
für hinweise bin ich dankbar...

 

[Albu]

GROUP_CONCAT ist ne Spezialität von MySQL, die ich bislang auch noch nicht kannte. Damit geht das gewünschte natürlich.

[edit]eventuell läßt sich das mit HAVING erreichen[/edit]

 

[rasputin]

GROUP_CONCAT ist ne Spezialität von MySQL

yep, die funktion gibts auch erst seit version 4.1


---------------------------------EDIT---------------------------------

mein ansatz war nun über ein where in() konstrukt, aber mysql meckert Unknown column 'assistant_IDs' in 'where clause'. wieso kann die aliasbezeichnung nicht verwendet werden?

SELECT p.project_ID, 
p.project_name, 
GROUP_CONCAT(CONCAT( a.assistant_firstname, ' ', a.assistant_lastname ) ORDER BY a.assistant_firstname ASC, a.assistant_lastname ASC SEPARATOR ', ') AS assistants, 
[COLOR="Red"]GROUP_CONCAT(CAST(a.assistant_ID as CHAR) SEPARATOR ', ') AS assistant_IDs[/COLOR]
FROM projects p
INNER JOIN assistant2project a2p ON ( p.project_ID = a2p.project_ID )
INNER JOIN assistants a ON ( a2p.assistant_ID = a.assistant_ID )
[COLOR="Red"]WHERE 7 IN(assistant_IDs)[/COLOR]
GROUP BY p.project_ID
ORDER BY p.project_state ASC , p.project_name ASC

 

[Curtains]

Hallo rasputin,

vielleicht klappt dies:

...
HAVING FIND_IN_SET(7, GROUP_CONCAT(a.assistant_ID))

Nachtrag: 'HAVING' kommt nach dem 'GROUP BY' statement

 

[rasputin]

mit where gibt es einen error, mit having-in nicht, jedoch findet mysql keine resultate bei beiden varianten unten.

SELECT p.project_ID, p.project_name, GROUP_CONCAT(CONCAT( a.assistant_firstname, ' ', a.assistant_lastname ) ORDER BY a.assistant_firstname ASC, a.assistant_lastname ASC SEPARATOR ', ') AS assistants, GROUP_CONCAT(a.assistant_ID SEPARATOR ', ') AS assistant_IDs
FROM projects p
INNER JOIN assistant2project a2p ON ( p.project_ID = a2p.project_ID )
INNER JOIN assistants a ON ( a2p.assistant_ID = a.assistant_ID )
GROUP BY p.project_ID
HAVING 7 IN(assistant_IDs)
ORDER BY p.project_state ASC , p.project_name ASC

SELECT p.project_ID, p.project_name, GROUP_CONCAT(CONCAT( a.assistant_firstname, ' ', a.assistant_lastname ) ORDER BY a.assistant_firstname ASC, a.assistant_lastname ASC SEPARATOR ', ') AS assistants, GROUP_CONCAT(CAST(a.assistant_ID as CHAR) SEPARATOR ', ') AS assistant_IDs
FROM projects p
INNER JOIN assistant2project a2p ON ( p.project_ID = a2p.project_ID )
INNER JOIN assistants a ON ( a2p.assistant_ID = a.assistant_ID )
GROUP BY p.project_ID
HAVING CAST(7 as CHAR) IN(assistant_IDs)
ORDER BY p.project_state ASC , p.project_name ASC

@curtains: werde es ausprobieren.

 

[rasputin]

dank dem tipp von curtains habe ich nun die gewünschte abfrage:

SELECT p.project_ID, p.project_name, GROUP_CONCAT(CONCAT( a.assistant_firstname, ' ', a.assistant_lastname ) ORDER BY a.assistant_firstname ASC, a.assistant_lastname ASC SEPARATOR ', ') AS assistants
FROM projects p
INNER JOIN assistant2project a2p ON ( p.project_ID = a2p.project_ID )
INNER JOIN assistants a ON ( a2p.assistant_ID = a.assistant_ID )
GROUP BY p.project_ID
HAVING FIND_IN_SET(7, GROUP_CONCAT(a.assistant_ID))
ORDER BY p.project_state ASC , p.project_name ASC