db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Fischer <fisc...@seitenbau.net>
Subject RE: Using multiple joins to a table
Date Sun, 02 Oct 2005 14:34:29 GMT




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


Mime
View raw message