db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thoralf Rickert" <thoralf.rick...@cadooz.de>
Subject Using multiple joins to a table
Date Mon, 26 Sep 2005 16:05:59 GMT
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=address.ID AND
  debitor.INVOICE_ADDRESS_ID=invoice_address.ID AND
  debitor.DELIVERY_ADDRESS_ID=delivery_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="abc"). 

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


Mime
View raw message