[prev in list] [next in list] [prev in thread] [next in thread] 

List:       turbine-torque-user
Subject:    RE: Using multiple joins to a table
From:       Thomas Fischer <fischer () seitenbau ! net>
Date:       2005-10-02 14:34:29
Message-ID: OFC6746E20.CD6E1FA8-ONC125708E.004FE91D-C125708E.00501272 () seitenbau ! net
[Download RAW message or body]





You can use the methods Criteria.addAlias() and Criteria.addAsColumn().
However, they are a table / a column at a time, and ther is no predefined
methods which creates aliasses for all columns in a data object, as far as
I know.

     Thomas

"Thoralf Rickert" <thoralf.rickert@cadooz.de> schrieb am 26.09.2005
18:05:59:

> 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
>


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org

[prev in list] [next in list] [prev in thread] [next in thread]