wyciaganie id w drzewkach Opcje

[Jabol]

najpierw struktura[sql:1:eac870bfa9]DROP SCHEMA adam CASCADE;
CREATE SCHEMA adam AUTHORIZATION adam;
CREATE SEQUENCE contents_increment
START 1
INCREMENT 1
MAXVALUE 9223372036854775807
MINVALUE 1
CACHE 1;
CREATE SEQUENCE nodes_increment
START 1
INCREMENT 1
MAXVALUE 9223372036854775807
MINVALUE 1
CACHE 1;
CREATE TABLE contents (
id bigint DEFAULT nextval('contents_increment'::text) NOT NULL,
content text
);
CREATE TABLE nodes (
id bigint DEFAULT nextval('nodes_increment'::text) NOT NULL,
parent bigint,
name character varying(100)
);
CREATE TABLE asociations (
first_id bigint DEFAULT 0 NOT NULL,
second_id bigint DEFAULT 0 NOT NULL,
depth bigint DEFAULT 0 NOT NULL
);
CREATE TABLE binds (
node_id bigint DEFAULT 0 NOT NULL,
content_id bigint DEFAULT 0 NOT NULL
);
CREATE INDEX nodes_parent_ind ON nodes USING btree (parent);
CREATE INDEX asociations_second_id_depth_ind ON asociations USING btree (second_id, depth);
CREATE INDEX asociations_first_id_depth_ind ON asociations USING btree (first_id, depth);
CREATE INDEX asociations_first_id_ind ON asociations USING btree (first_id);
CREATE INDEX asociations_second_id_ind ON asociations USING btree (second_id);
ALTER TABLE ONLY nodes
ADD CONSTRAINT nodes_parent_name_key UNIQUE (parent, name);
ALTER TABLE ONLY nodes
ADD CONSTRAINT nodes_pkey PRIMARY KEY (id);
ALTER TABLE ONLY nodes
ADD CONSTRAINT "$1" FOREIGN KEY (parent) REFERENCES nodes(id) ON UPDATE NO ACTION ON DELETE CASCADE;
ALTER TABLE ONLY contents
ADD CONSTRAINT contents_pkey PRIMARY KEY (id);
ALTER TABLE ONLY binds
ADD CONSTRAINT binds_node_id_content_id_key UNIQUE (node_id, content_id);
ALTER TABLE ONLY binds
ADD CONSTRAINT binds_pkey PRIMARY KEY (node_id, content_id);
ALTER TABLE ONLY binds
ADD CONSTRAINT "$1" FOREIGN KEY (content_id) REFERENCES contents(id) ON UPDATE NO ACTION ON DELETE CASCADE;
ALTER TABLE ONLY binds
ADD CONSTRAINT "$2" FOREIGN KEY (node_id) REFERENCES nodes(id) ON UPDATE NO ACTION ON DELETE CASCADE;
ALTER TABLE ONLY asociations
ADD CONSTRAINT asociations_pkey PRIMARY KEY (first_id, second_id);
ALTER TABLE ONLY asociations
ADD CONSTRAINT "$1" FOREIGN KEY (second_id) REFERENCES nodes(id) ON UPDATE NO ACTION ON DELETE CASCADE;
ALTER TABLE ONLY asociations
ADD CONSTRAINT "$2" FOREIGN KEY (first_id) REFERENCES nodes(id) ON UPDATE NO ACTION ON DELETE CASCADE;
SELECT pg_catalog.setval ('nodes_increment', 1, true);
SELECT pg_catalog.setval ('contents_increment', 1, true);
INSERT INTO nodes (id, parent, name) VALUES (1, NULL, 'main');
INSERT INTO asociations (first_id, second_id, depth) VALUES (1, 1, 0);
CREATE VIEW paths AS SELECT id, get_path(id) AS path FROM nodes;[/sql:1:eac870bfa9]Teraz mam taki problem. Nie mam zielonego pojęcia jak wyciągnąć id po ścieżce. W odwrotną strone jest bardzo łatwo, ale tak jakoś mi się wydaje to dość trudne :| ... Jedyny sposób jaki dotąd wymyśliłem to zabawić się w prawdziwego shella i po prostu np. w php brać i wyciągać po koleji każdy kawałek, ale czy to byłoby wydajne ( zaraz się za to zabiore ).
Tak czy tak. Mam pytanie, czy macie może jakieś wyobrażenie jakby to zrobić jednym zapytanie ( używając podselectów czy tam czegokolwiek ).
Może Wy coś wymyślicie,
pozdrawiam, Jabol

ps. w razie wątpliwości patrzcie podpis.

[Jabol]

w php wygląda to tak ( może wam cosik pomoże ):[php:1:a04fe0df25]<?php

function get_time()
{
list($_1, $_2)=explode(" ", microtime());
return((double)$_1+(double)$_2);
}

header("Content-type: text/plain");
$path="main/tix-8.1.4/html/gif/tix/filesel/dirlist.gif";
$patharr=explode("/", $path);
print_r($patharr);
$parent=NULL;
$parts=array();
if(!($conn=pg_connect("dbname=adam user=adam password=qazsxdc")))
die(sprintf("CONN ERROR: %sn", pg_last_error($conn)));
$timewhole1=get_time();printf("DEBUG: Accessing Microtime: %fn", $timewhole1);
foreach($patharr as $cat)
{
if($parent===NULL)
$rest="parent IS NULL";
else
$rest=sprintf("parent = %f", $parent);
$time1=get_time();
$result=pg_query($conn, ($query=sprintf("SELECT id, name FROM nodes WHERE name = '%s' AND %s;", $cat, $rest)));
$time2=get_time();
printf("DEBUG: Executing Query: '%s' in %fn", $query, ($time2-$time1));
if(pg_num_rows($result)!=1)
break;
else
{
$parts[]=$temp=pg_fetch_assoc($result);
$parent=$temp["id"];
}
}
$timewhole2=get_time();printf("DEBUG: Accessing Microtime: %fn", $timewhole2);
printf("DEBUG: Execution Time: %fn", ($timewhole2-$timewhole1));
print_r($parts);
?>[/php:1:a04fe0df25]