Hi!
Some time ago we had a problem to map a very complicated SQL query to the
Criteria object. In that query we made a join with one table twice - for
example a foreignkey called "invoice_address" and a foreignkey
"delivery_address" and both are mapped to the table "address". The query needs
to distinguish between invoice_address and delivery_address. So we need to use
an alias in the join and selection part of the query.
The problem was, that criteria or the doSelect() throw an exception if you use
an alias name instead of a normal Torque generated column name with a dot
(TABLENAME.COLUMN_NAME) and we didn't understood what means Criteria.addAlias()
and Criteria.getTableForAlias(). So, what we did, was to create an
"ExtendedCriteria" that takes the Torque column name, removes the table name
and replaces it with the alias name. Actually this isn't really something
complicated but it helps us very much.
public class EnhancedCriteria extends Criteria {
....
public String getAliasedName(String name, String alias) {
String tableName = extractTableName(name);
String columnName = name.substring(tableName.length() + 1, name.length());
return alias + "." + columnName;
}
public String extractTableName(String name) {
String result = null;
int index = name.lastIndexOf(".");
if (index > 0) result = name.substring(0, index);
return result;
}
To use this you can use something like this:
criteria.addJoin(
criteria.getAliasedName(AddressPeer.ID, "delivery_address"),
ReceiptPeer.DELIVERY_ADDRESS_ID);
criteria.addJoin(
criteria.getAliasedName(AddressPeer.ID, "invoice_address"),
ReceiptPeer.INVOICE_ADDRESS_ID);
...
criteria.add(criteria.getAliasedName(AddressPeer.LASTNAME,"delivery_address",deliveredToLastname);
...
ReceiptPeer.addSelectColumn(criteria,"invoice_address");
The addSelectColumn(Criteria criteria, String alias) extends the standard
addSelectColumn() method and calls the getAliasedName() for every existing
column (template extension). Of course this is just an easy example of our real
SQL query.
But if you think about redesigning the way to hold the column name in Torque
4.0, you should also think about a way to use aliases in queries and think
about Criteria methods that expect an alias name instead of a column name. I
hope, our extension is just the first step for a change in the Torque API.
Thoralf
________________________________
Von: Thomas Fischer [mailto:[EMAIL PROTECTED]
Gesendet: Sa 02.12.2006 11:21
An: Apache Torque Developers List
Betreff: Aliases with Joins (was: Torque 4.0 plan)
Hi Thoralf,
When you have time, could you describe what you did there ?
Thomas
On Fri, 1 Dec 2006, Thoralf Rickert wrote:
> I forgot something: We've implemented a "working" and easy way to use Aliases
> with JOINs. This is necessary if you join a table twice.
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
