Mailing-List: contact torque-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Apache Torque Users List" <torque-user@db.apache.org>
Received-SPF: pass (asf.osuosl.org: local policy)
Subject: Using multiple joins to a table
Date: Mon, 26 Sep 2005 18:05:59 +0200
Message-ID: 
 <F1EF69E69C1EED4FA3BB295B07664EEE15DC7B@srv-master.cadooz-ag.intern>
Thread-Topic: Using multiple joins to a table
Thread-Index: AcXCtC9wTsJeExucSL2anjpvY0PntQ==
From: "Thoralf Rickert" <thoralf.rickert@cadooz.de>
To: <torque-user@db.apache.org>
MIME-Version: 1.0
Content-Type: multipart/signed; protocol="application/x-pkcs7-signature";
 micalg=sha1; boundary="----9730D90A07DC8E0570DAB08115E0F0C2"

------9730D90A07DC8E0570DAB08115E0F0C2
Content-class: urn:content-classes:message
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

Hiho!

I've little "freaky" question. There a two tables:

Table #1 (debitor) has following columns:
  id (primary)
  address_id (not null, foreign key to address)
  invoice_address_id (not null, foreign key to address)
  delivery_address_id (not null, foreign key to address)
  ...

Table #2 (address) has following columns
  id (primary)
  company
  lastname
  ...

Now I would join this tables to reduce database traffic in sql like
this:

SELECT
  debitor.ID, debitor.ADDRESS_ID, debitor.INVOICE_ADDRESS_ID,
debitor.DELIVERY_ADDRESS_ID,...,
  address.ID, address.COMPANY, address.LASTNAME,...,
  invoice_address.ID, invoice_address.COMPANY,
invoice_address.LASTNAME,...,
  delivery_address.ID, delivery_address.COMPANY,
delivery_address.LASTNAME,...
FROM
  debitor, address, address AS invoice_address, address AS
delivery_address
WHERE
  ...
  debitor.ADDRESS_ID=3Daddress.ID AND
  debitor.INVOICE_ADDRESS_ID=3Dinvoice_address.ID AND
  debitor.DELIVERY_ADDRESS_ID=3Ddelivery_address.ID

This works in MySQL. Now I would like to do the same with the Criteria
object in a separate doSelectJoinAllAddresses() method. The problem is,
that it isn't possible to make this:

    AddressPeer.addSelectColumns(c); // select fields for address_id
    AddressPeer.addSelectColumns(c); // select fields for
invoice_address_id
    AddressPeer.addSelectColumns(c); // select fields for
delivery_address_id

because the internal list of select fields is a unique list. I think I
need to rewrite the aliases in the whole criteria (because there could
be criterias which use the address table in the where clause (for
example WHERE address.COMPANY=3D"abc").=20

Is there anywhere a method that makes this alias rewriting or do I have
to do this the first time? Or is there an easier way for this problem.

Thanks
Thoralf


------9730D90A07DC8E0570DAB08115E0F0C2
Content-Type: application/x-pkcs7-signature; name="smime.p7s"
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename="smime.p7s"

MIIFwAYJKoZIhvcNAQcCoIIFsTCCBa0CAQExCzAJBgUrDgMCGgUAMAsGCSqGSIb3
DQEHAaCCA14wggNaMIICw6ADAgECAg5BcAABAAJ40QJx87czYzANBgkqhkiG9w0B
AQQFADCBvDELMAkGA1UEBhMCREUxEDAOBgNVBAgTB0hhbWJ1cmcxEDAOBgNVBAcT
B0hhbWJ1cmcxOjA4BgNVBAoTMVRDIFRydXN0Q2VudGVyIGZvciBTZWN1cml0eSBp
biBEYXRhIE5ldHdvcmtzIEdtYkgxIjAgBgNVBAsTGVRDIFRydXN0Q2VudGVyIENs
YXNzIDEgQ0ExKTAnBgkqhkiG9w0BCQEWGmNlcnRpZmljYXRlQHRydXN0Y2VudGVy
LmRlMB4XDTA1MDgzMTA3NTIyN1oXDTA2MDgzMTA3NTIyN1owUTELMAkGA1UEBhMC
REUxGDAWBgNVBAMTD1Rob3JhbGYgUmlja2VydDEoMCYGCSqGSIb3DQEJARYZdGhv
cmFsZi5yaWNrZXJ0QGNhZG9vei5kZTCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkC
gYEA3CtjpA9PcQWSARhVJIvXYYFksZ2tTap/poRNDv84/5SRns6qp+j057AkL3p5
VAoMq0zcba1m+ncphsG0WGhDsT3Jev/fI0bgvv2tzkf1R/6YE7NVpI3wr0H5oSpT
jNVqlX4Df6e31CNajScK+d7gNAJnhHV5dHkW1vY0+/iSiH0CAwEAAaOByDCBxTAM
BgNVHRMBAf8EAjAAMA4GA1UdDwEB/wQEAwIF4DAzBglghkgBhvhCAQgEJhYkaHR0
cDovL3d3dy50cnVzdGNlbnRlci5kZS9ndWlkZWxpbmVzMBEGCWCGSAGG+EIBAQQE
AwIFoDBdBglghkgBhvhCAQMEUBZOaHR0cHM6Ly93d3cudHJ1c3RjZW50ZXIuZGUv
Y2dpLWJpbi9jaGVjay1yZXYuY2dpLzQxNzAwMDAxMDAwMjc4RDEwMjcxRjNCNzMz
NjM/MA0GCSqGSIb3DQEBBAUAA4GBAKFKflksagTZcOnnzXUVjh6fkR4B16PK5vIJ
JbPNLiw9P6d7wcpWYxBJuTgd/UNCe0vxEM/uRFkZGLfCz6sIMgZysLeXu14fWia1
HStL8XOUDFchNSfnndZMyQNrMlzmdxxb6Fj/A3POXQNbhAqbxroqhdLHC+TZP/OO
g1Eyd71HMYICKjCCAiYCAQEwgc8wgbwxCzAJBgNVBAYTAkRFMRAwDgYDVQQIEwdI
YW1idXJnMRAwDgYDVQQHEwdIYW1idXJnMTowOAYDVQQKEzFUQyBUcnVzdENlbnRl
ciBmb3IgU2VjdXJpdHkgaW4gRGF0YSBOZXR3b3JrcyBHbWJIMSIwIAYDVQQLExlU
QyBUcnVzdENlbnRlciBDbGFzcyAxIENBMSkwJwYJKoZIhvcNAQkBFhpjZXJ0aWZp
Y2F0ZUB0cnVzdGNlbnRlci5kZQIOQXAAAQACeNECcfO3M2MwCQYFKw4DAhoFAKCB
sTAYBgkqhkiG9w0BCQMxCwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0wNTA5
MjYxNjA2MDlaMCMGCSqGSIb3DQEJBDEWBBTjjMiIpEQEot6Moyu6Tzniyy5JWTBS
BgkqhkiG9w0BCQ8xRTBDMAoGCCqGSIb3DQMHMA4GCCqGSIb3DQMCAgIAgDANBggq
hkiG9w0DAgIBQDAHBgUrDgMCBzANBggqhkiG9w0DAgIBKDANBgkqhkiG9w0BAQEF
AASBgJ0Qao1hJQEElybmm0Sixu1r3iM9z+pgSfIEpEYZEf0+/ZPkdX9tY8HSJMXD
dQ38LpJ8fd6K81jOu122dbF1CF7Ke12MsT9t35FZrkMG20fVnvhtSV5hEkayTiur
ZIuuCynIPt23zL49gDqJd5dgPURpWUZGFzQjk3zJNY2vxAY5

------9730D90A07DC8E0570DAB08115E0F0C2--