Aliases with Joins (was: Torque 4.0 plan)

[Thomas Fischer <tf...@apache.org>]

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

AW: Aliases with Joins (was: Torque 4.0 plan)

[Thoralf Rickert <th...@cadooz.de>]

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