db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thoralf Rickert" <thoralf.rick...@cadooz.de>
Subject AW: Aliases with Joins (was: Torque 4.0 plan)
Date Sun, 03 Dec 2006 11:45:53 GMT
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:tfischer@apache.org]
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: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org





Mime
View raw message