[Propel] Tabela i dwa razy join Opcje

[mike]

Mam taką sytuację: tabela Client zawierająca pole company_id, jest to klucz obcy z tabeli Company, która to z kolei zawiera pole address_id będące kluczem do tabeli Address.
Wygląda to mniej więcej tak:

[SQL] pobierz, plaintext 
CREATE TABLE client
(
  id serial NOT NULL,
  "login" character varying(50) NOT NULL,
  "password" character(40) NOT NULL,
  first_name character varying(50),
  last_name character varying(50),
  max_receivers integer NOT NULL,
  max_cargos integer NOT NULL,
  invoices_avaliable integer NOT NULL,
  balance integer,
  client_type_id integer,
  company_id integer,
  CONSTRAINT client_pkey PRIMARY KEY (id),
  CONSTRAINT client_client_type_id_fkey FOREIGN KEY (client_type_id)
      REFERENCES client_type (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT client_company_id_fkey FOREIGN KEY (company_id)
      REFERENCES company (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT client_login_key UNIQUE ("login")
)
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
CREATE TABLE company
(
  id serial NOT NULL,
  name character varying(100) NOT NULL,
  nip character varying(13) NOT NULL,
  phone character varying(50),
  regon character varying(15),
  address_id integer,
  CONSTRAINT company_pkey PRIMARY KEY (id),
  CONSTRAINT company_address_id_fkey FOREIGN KEY (address_id)
      REFERENCES address (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
CREATE TABLE address
(
  id serial NOT NULL,
  "local" character varying(10) NOT NULL,
  building character varying(10) NOT NULL,
  street character varying(100) NOT NULL,
  city character varying(50) NOT NULL,
  post_code character varying(6) NOT NULL,
  country character varying(50) NOT NULL,
  CONSTRAINT address_pkey PRIMARY KEY (id)
)
[SQL] pobierz, plaintext 

Chciałbym teraz za pomocą Propela wydobyć w jednym wyniku (w jednym zapytaniu) dane o kliencie, firmie i adresie.
Na chwilę obecną mam:

[PHP] pobierz, plaintext 
<?php
 
$criteria = new Criteria();
$criteria->add(ClientPeer::ID, $clientId);
$users = ClientPeer::doSelectJoinAllExceptClientType($criteria);
 
?>
[PHP] pobierz, plaintext 

daje mi to:

[SQL] pobierz, plaintext 
SELECT client.ID, client.LOGIN, client.PASSWORD, client.FIRST_NAME, client.LAST_NAME, client.MAX_RECEIVERS, client.MAX_CARGOS, client.INVOICES_AVALIABLE, client.BALANCE, client.CLIENT_TYPE_ID, client.COMPANY_ID, company.ID, company.NAME, company.NIP, company.PHONE, company.REGON, company.ADDRESS_ID FROM client, company WHERE client.ID=11 AND client.COMPANY_ID=company.ID
[SQL] pobierz, plaintext 

czyli nie to czego oczekuję.
Próbowałem już chyba wszystkie kombinacje zarówno z Criteria::add() jak i z Criteria::addJoin()

Jeżeli ktoś wie jak to zrobić będę wdzięczny za informację.

Dla ścisłości chciałbym mieć to:

[SQL] pobierz, plaintext 
SELECT * FROM client JOIN company ON client.company_id = company.id 
    JOIN address ON company.address_id = address.id 
WHERE client.id = 11
[SQL] pobierz, plaintext 

Ten post edytował mike_mech 18.03.2007, 20:31:57