Die lieben JOINs - was mach ich falsch?

[Falke]

Also

ich habe eine Tabelle invPartCache

typeID, partName, partCount, childID, entrydate

in diese Werden Baumaterialien eingetragen.
Nun brauche ich evtl für ein Teil, wieder ein Teil, sprich die Mauer besteht aus Mörtel und Steinen
Mörtel aus Sand, Zement, Wasser
Steine aus Lehm… kann man immer weiter spielen.

Also versuche ich einen Join mit sich selbst, leider kommt dabei nicht das gewünschte raus

Wenn ich Mauer abrufe, möchte ich nicht nur
300 Steine, 50 Mörtel, sondern auch woraus Mörtel und Steine bestehen sehen. Wenn ein Element kein Unterelement hat soll es trotzdem angezeigt werden.

Dazu:

SELECT invB.partName AS name, invB.partCount AS count, invA.partName AS childname, invA.partCount AS childcount FROM invPartCache AS invB LEFT JOIN invPartCache AS invA ON (invB.childID=invA.typeID AND invA.typeID=„.$typeID.“ AND invA.childID IS NOT NULL)

liefert es mir folgendes:

//Diese Teile sind richtig
Capital Sensor Cluster -
4 -
Capital Capacitor Battery -
9 -
Capital Cargo Bay -
37 -
Capital Computer System -
7 -
Capital Construction Parts -
15 -
Capital Ship Maintenance Bay -
7 -
Capital Corporate Hangar Bay -
4 -
Waste Factor -
10 -
//Ab hier falsch, denn dies sind die Teile für „Capital Sensor Cluster“
Tritanium -
439155 -
Pyerite -
100015 -
Mexallon -
40468 -
Isogen -
6592 -
Nocxium -
1786 -
Zydrine -
330 -
Megacyte -
147 -
Waste Factor -
10 -

Ich hab bisher nur das Produkt „Orca“ und „Capital Sensor Cluster“ in der Datenbank…

(28606, ‚Capital Sensor Cluster‘, 4, 21013, 1252842827);
(28606, ‚Capital Capacitor Battery‘, 9, 21019, 1252842827);
(28606, ‚Capital Cargo Bay‘, 37, 21027, 1252842827);
(28606, ‚Capital Computer System‘, 7, 21035, 1252842827);
(28606, ‚Capital Construction Parts‘, 15, 21037, 1252842827);
(28606, ‚Capital Ship Maintenance Bay‘, 7, 24558, 1252842827);
(28606, ‚Capital Corporate Hangar Bay‘, 4, 24560, 1252842827);
(28606, ‚Waste Factor‘, 10, NULL, 1252842827);
(21013, ‚Tritanium‘, 439155, 34, 1252842859);
(21013, ‚Pyerite‘, 100015, 35, 1252842859);
(21013, ‚Mexallon‘, 40468, 36, 1252842859);
(21013, ‚Isogen‘, 6592, 37, 1252842859);
(21013, ‚Nocxium‘, 1786, 38, 1252842859);
(21013, ‚Zydrine‘, 330, 39, 1252842859);
(21013, ‚Megacyte‘, 147, 40, 1252842859);
(21013, ‚Waste Factor‘, 10, NULL, 1252842859);

Was mach ich falsch? Bzw. jemand einen Denkanstoß für mich?

[mermshaus]

Klingt für mich, als würdest du da Rekursion benötigen. Was ich spontan gefunden habe: Creating Recursive SQL Calls for Tables with Parent-Child Relationships | Webinade - Web Development & Programming Blog (WITH statements scheint es in MySQL nicht zu geben.)

Bin da selbst noch am Grübeln, aber ein Ansatz sollte es sein.

Edit: Ein brauchbarer (also importierbarer) Dump wäre übrigens schön.

Edit 2: Anderer Ansatz vielleicht nested sets, damit habe ich aber keine Erfahrung. (Die könnten aber rausfallen, weil hier wahrscheinlich keine eindeutige Baumstruktur vorliegt (jedes Child-Element hat nur einen Parent).)

[Falke]

Hallo mermshaus,

danke für den Tip. Genau sowas suchte ich, denn es bleibt nicht nur bei Teilen, sondern da soll auch bald ein Routenplaner dazu, wäre das gleiche System, eine Tabelle, aktueller Ort, alle Nachbarn und dann recursiv a la try and error von a nach b…

Ja ich hab ja sonst keine Hobbies ^^

Ein Dump, mh, das Problem ist, es geht wie man sicher merkt um ein Spiel, der Dump den der Hersteller für uns süchtige Freaks bereitstellt ist leider 141 MB groß.

Ich denke dich interessiert nur die Tabelle invPartCache? Die ist noch ziemlich leer…

Ich werd das Ergebnis wie ich es gemacht habe hier posten…

[mermshaus]

Ich komme mit der Tabellenstruktur nicht klar. Wo ist da in der Hierarchie „oben“?

Die Query

SELECT DISTINCT `typeID` FROM `invPartCache` WHERE `typeID` NOT IN (SELECT childID FROM `invPartCache`)

gibt eine leere Menge zurück. Das bedeutet, jede typeID ist irgendwo als childID eingetragen. Umgekehrt gibt es aber 41 childIDs die auf keine in der Tabelle vorhandene typeID zeigen.

Und hast du die Tabellenstruktur so vorgegeben bekommen oder hast du dir die selbst ausgedacht? Die ist nämlich auch reichlich redundant.

[Falke]

Sie ist selbst ausgedacht.

Jetzt wo du es sagst.

type ID ist die Mauer, partName ist der Stein, partCount die Zahl der Steine, childID entspricht der typeID des Steins…

EDITH:

jetzt bin ich selbst durcheinander gekommen…

28606, ‚Capital Sensor Cluster‘, 4, 21013,

28606 ist ein Orca (Schiff)

man braucht 4 Captial Sensor Cluster für den bau

21013 ist der Capital Sensor, für den braucht man

‚Tritanium‘, 439155, 34, ;
‚Pyerite‘, 100015, 35,
‚Mexallon‘, 40468, 36,
‚Isogen‘, 6592, 37,
‚Nocxium‘, 1786, 38,
‚Zydrine‘, 330, 39,
‚Megacyte‘, 147, 40,
‚Waste Factor‘, 10, NULL,

tritanium ist die 34, dort ist die spalte childID NULL, weil es rohstoff ist, also nicht zusammengesetzt

isogen ist ebenfalls rohstoff → typeID 37.

Meine tabelle ist mittlerweile weiter gefüllt, hänge da gleich noch ein volleres dump dran

was ich gerne hätte wäre ein tabellenaufbau:

orca:

capital sensor cluster | 4 | ‚Tritanium‘ | 439155
| |‚Pyerite‘ | 100015
| |‚Mexallon‘ | 40468
| | ‚Isogen‘ | 6592
| | ‚Nocxium‘ | 1786
| |‚Zydrine‘ | 330
| |‚Megacyte‘ | 147
| |‚Waste Factor‘ | 10
Capital Capacitor Battery | 8| Tritanium …

[mermshaus]

Es wäre günstiger, die Geschichte auf zwei Tabellen aufzuteilen und in einer nur die „Materialien“ zu speichern und in der anderen die Verknüpfungen.

parts:

[code]-- phpMyAdmin SQL Dump
– version 3.1.2deb1ubuntu0.1
– http://www.phpmyadmin.net

– Host: localhost
– Generation Time: Sep 14, 2009 at 03:16 PM
– Server version: 5.0.75
– PHP Version: 5.2.6-3ubuntu4.2

SET SQL_MODE=„NO_AUTO_VALUE_ON_ZERO“;

–
– Database: test

---

–
– Table structure for table parts

CREATE TABLE IF NOT EXISTS parts (
id int(10) unsigned NOT NULL,
name varchar(255) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

–
– Dumping data for table parts

INSERT INTO parts (id, name) VALUES
(34, ‚Tritanium‘),
(35, ‚Pyerite‘),
(36, ‚Mexallon‘),
(37, ‚Isogen‘),
(38, ‚Nocxium‘),
(39, ‚Zydrine‘),
(40, ‚Megacyte‘),
(21013, ‚Capital Sensor Cluster‘),
(28606, ‚Orca‘);[/code]

parts_tree:

[code]-- phpMyAdmin SQL Dump
– version 3.1.2deb1ubuntu0.1
– http://www.phpmyadmin.net

– Host: localhost
– Generation Time: Sep 14, 2009 at 03:16 PM
– Server version: 5.0.75
– PHP Version: 5.2.6-3ubuntu4.2

SET SQL_MODE=„NO_AUTO_VALUE_ON_ZERO“;

–
– Database: test

---

–
– Table structure for table parts_tree

CREATE TABLE IF NOT EXISTS parts_tree (
parts_id int(10) unsigned NOT NULL,
parent_id int(10) unsigned default NULL,
amount int(10) unsigned default NULL,
KEY parts_id (parts_id),
KEY parent_id (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

–
– Dumping data for table parts_tree

INSERT INTO parts_tree (parts_id, parent_id, amount) VALUES
(21013, 28606, 4),
(28606, NULL, NULL),
(34, 21013, 439155),
(35, 21013, 100015),
(36, 21013, 40468),
(37, 21013, 6592),
(38, 21013, 1786),
(39, 21013, 330),
(40, 21013, 147);

–
– Constraints for dumped tables

–
– Constraints for table parts_tree

ALTER TABLE parts_tree
ADD CONSTRAINT parts_tree_ibfk_4 FOREIGN KEY (parent_id) REFERENCES parts (id) ON DELETE CASCADE,
ADD CONSTRAINT parts_tree_ibfk_3 FOREIGN KEY (parts_id) REFERENCES parts (id) ON DELETE CASCADE;[/code]

Steht für diese Struktur:

Orca (x) Capital Sensor Cluster (4x) Tritanium (439155x) Pyerite (100015x) Mexallon (40468x) Isogen (6592x) Nocxium (1786x) Zydrine (330x) Megacyte (147x)

Erzeugt durch dieses Skript:

[php]<?php

// Entsprechende Werte für databasename, user und password eintragen
$dsn = ‚mysql:dbname=databasename;host=localhost‘;
$user = ‚user‘;
$password = ‚password‘;

header(‚Content-Type: text/plain; charset=UTF-8‘);

try {
$dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
echo 'Connection failed: ’ . $e->getMessage();
exit;
}

$dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

$dbh->beginTransaction();

$queryBase = "SELECT
p.id, p.name, pt.amount
FROM
parts_tree pt
JOIN
parts p
ON
p.id = pt.parts_id
WHERE ";

$sthNull = $dbh->prepare($queryBase . „parent_id IS NULL“);
$sth = $dbh->prepare($queryBase . „parent_id = :parentId“);

function rec(PDOStatement $sth, array $rs, $depth = 0)
{
if (count($rs) > 0) {
foreach ($rs as $r) {
echo str_repeat(’ ‚, $depth) . $r[‚name‘]
. ’ (‘ . $r[‚amount‘] . „x)\n“;
$sth->execute(array(‚parentId‘ => $r[‚id‘]));
rec($sth, $sth->fetchAll(), $depth + 1);
}
}
}

$sthNull->execute();
rec($sth, $sthNull->fetchAll());

$dbh->commit();[/php]

[Falke]

kommt aus dem staunen nicht mehr raus

DPO() ist mir neu, gerade schon ein wenig geschmökert im php.net

Das hat mich jetzt um Lichtjahre nach vorne gebracht, allerdings auch aufgezeigt, dass ich einen noch etwas alten Stand habe. Positiv finde ich, dass es mein Provider php5.0+ hat, hatte Sorge, dass der Server zu alt sein könnte. Aber funktioniert.

Danke!