Wygląda to mniej więcej tak:
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") )
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 )
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) )
Chciałbym teraz za pomocą Propela wydobyć w jednym wyniku (w jednym zapytaniu) dane o kliencie, firmie i adresie.
Na chwilę obecną mam:
daje mi to:
<?php $criteria = new Criteria(); $criteria->add(ClientPeer::ID, $clientId); $users = ClientPeer::doSelectJoinAllExceptClientType($criteria); ?>
czyli nie to czego oczekuję.
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
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:
SELECT * FROM client JOIN company ON client.company_id = company.id JOIN address ON company.address_id = address.id WHERE client.id = 11