On 04 Jun 2012, at 2:50 PM, Graham Leggett wrote:
> The attached patch is what I came up with so far.
>
> As it ultimately turned out, my query eventually didn't need the join clause,
> so I haven't developed this further. Ideally it should be possible to support
> nested selects in the join clause as well, but I didn't get a chance to dig
> how to do that.
>
> This however was what I started with, which gives you a criterion to use:
Odd, seems the list strips patches. The patch looks like this:
Index: src/java/org/apache/torque/util/SqlExpression.java
===================================================================
--- src/java/org/apache/torque/util/SqlExpression.java (revision 1340512)
+++ src/java/org/apache/torque/util/SqlExpression.java (working copy)
@@ -249,9 +249,15 @@
}
else
{
- if (criteria instanceof String || criteria instanceof StringKey)
- {
- criteria = quoteAndEscapeText(criteria.toString(), db);
+ if (criteria instanceof String || criteria instanceof StringKey) {
+ if (comparison.equals(Criteria.JOIN)
+ || comparison.equals(Criteria.INNER_JOIN)
+ || comparison.equals(Criteria.LEFT_JOIN)
+ || comparison.equals(Criteria.RIGHT_JOIN)) {
+ criteria = criteria.toString();
+ } else {
+ criteria = quoteAndEscapeText(criteria.toString(), db);
+ }
}
else if (criteria instanceof Date)
{
@@ -293,7 +299,13 @@
}
}
- if (comparison.equals(Criteria.LIKE)
+ if (comparison.equals(Criteria.JOIN)
+ || comparison.equals(Criteria.INNER_JOIN)
+ || comparison.equals(Criteria.LEFT_JOIN)
+ || comparison.equals(Criteria.RIGHT_JOIN)) {
+ whereClause.append(buildInnerJoin(columnName, (String)
criteria));
+ }
+ else if (comparison.equals(Criteria.LIKE)
|| comparison.equals(Criteria.NOT_LIKE)
|| comparison.equals(Criteria.ILIKE)
|| comparison.equals(Criteria.NOT_ILIKE))
Index: src/java/org/apache/torque/util/Criteria.java
===================================================================
--- src/java/org/apache/torque/util/Criteria.java (revision 1340512)
+++ src/java/org/apache/torque/util/Criteria.java (working copy)
@@ -1345,12 +1345,39 @@
*/
public Criteria addJoin(String left, String right, SqlEnum operator)
{
- joins.add(new Join(left, right, operator));
+ joins.add(new Join(left, right, null, operator));
return this;
}
/**
+ * This is the way that you should add a join of two tables, with a
+ * customised ON clause. For
+ * example:
+ *
+ * <p>
+ * PROJECT LEFT JOIN FOO ON PROJECT.PROJECT_ID=FOO.PROJECT_ID AND
PROJECT.PROJECT_ID IS NULL
+ * <p>
+ *
+ * left = "PROJECT.PROJECT_ID"
+ * right = "FOO.PROJECT_ID"
+ * criterion = PROJECT.PROJECT_ID=FOO.PROJECT_ID AND PROJECT.PROJECT_ID IS
NULL
+ * operator = Criteria.LEFT_JOIN
+ *
+ * @param left A String with the left side of the join.
+ * @param right A String with the right side of the join.
+ * @param operator The operator used for the join: must be one of null,
+ * Criteria.LEFT_JOIN, Criteria.RIGHT_JOIN, Criteria.INNER_JOIN
+ * @return A modified Criteria object.
+ */
+ public Criteria addJoin(String left, String right, Criterion criterion,
SqlEnum operator)
+ {
+ joins.add(new Join(left, right, criterion, operator));
+
+ return this;
+ }
+
+ /**
* get the List of Joins. This method is meant to
* be called by BasePeer.
* @return a List which contains objects of type Join.
@@ -3750,6 +3777,9 @@
/** the type of the join (LEFT JOIN, ...), or null */
private SqlEnum joinType = null;
+ /** the criterion of the join (a=b, ...), or null */
+ private Criterion joinCriterion = null;
+
/**
* Constructor
* @param leftColumn the left column of the join condition;
@@ -3763,11 +3793,13 @@
public Join(
final String leftColumn,
final String rightColumn,
+ final Criterion joinCriterion,
final SqlEnum joinType)
{
this.leftColumn = leftColumn;
this.rightColumn = rightColumn;
this.joinType = joinType;
+ this.joinCriterion = joinCriterion;
}
/**
@@ -3796,6 +3828,14 @@
}
/**
+ * @return the optional criterion describing the join condition
+ */
+ public final Criterion getCriterion()
+ {
+ return joinCriterion;
+ }
+
+ /**
* returns a String representation of the class,
* mainly for debuggung purposes
* @return a String representation of the class
@@ -3808,10 +3848,20 @@
result.append(joinType)
.append(" : ");
}
+ if (joinCriterion == null) {
result.append(leftColumn)
.append("=")
.append(rightColumn)
.append(" (ignoreCase not considered)");
+ }
+ else {
+ result.append(leftColumn)
+ .append("/")
+ .append(rightColumn)
+ .append(" : ")
+ .append(joinCriterion.toString())
+ .append(" (ignoreCase not considered)");
+ }
return result.toString();
}
@@ -3836,7 +3886,8 @@
return ObjectUtils.equals(leftColumn, join.getLeftColumn())
&& ObjectUtils.equals(rightColumn, join.getRightColumn())
- && ObjectUtils.equals(joinType, join.getJoinType());
+ && ObjectUtils.equals(joinType, join.getJoinType())
+ && ObjectUtils.equals(joinCriterion, join.getCriterion());
}
/**
@@ -3850,6 +3901,7 @@
result = 37 * result + leftColumn.hashCode();
result = 37 * result + rightColumn.hashCode();
result = 37 * result + (null == joinType ? 0 :
joinType.hashCode());
+ result = 37 * result + (null == joinCriterion ? 0 :
joinCriterion.hashCode());
return result;
}
Index: src/java/org/apache/torque/util/JoinBuilder.java
===================================================================
--- src/java/org/apache/torque/util/JoinBuilder.java (revision 1340512)
+++ src/java/org/apache/torque/util/JoinBuilder.java (working copy)
@@ -24,6 +24,8 @@
import org.apache.torque.TorqueException;
import org.apache.torque.adapter.DB;
import org.apache.torque.map.DatabaseMap;
+import org.apache.torque.util.Criteria.Criterion;
+import org.apache.torque.util.SQLBuilder.QueryCallback;
/**
* Factored out code that is used to generate Join Code. This code comes
@@ -56,7 +58,9 @@
final DB db,
final DatabaseMap dbMap,
final Criteria criteria,
- final Query query)
+ final Query query,
+ final List params,
+ final QueryCallback qc)
throws TorqueException
{
List criteriaJoins = criteria.getJoins();
@@ -74,6 +78,7 @@
Criteria.Join join = (Criteria.Join) criteriaJoins.get(i);
String leftColumn = join.getLeftColumn();
String rightColumn = join.getRightColumn();
+ Criterion joinCriterion = join.getCriterion();
// check if the column names make sense
if (leftColumn.indexOf('.') == -1)
@@ -118,6 +123,19 @@
rightTableName = SQLBuilder.getTableNameForFromClause(
rightTableName, criteria);
+ // check the join criterion, if provided
+ if (joinCriterion != null) {
+ Criteria.Criterion[] someCriteria =
+ joinCriterion.getAttachedCriterion();
+
+ for (int j = 0; j < someCriteria.length; j++)
+ {
+ someCriteria[j].setIgnoreCase(ignoreCase);
+ }
+
+ joinCriterion.setDB(db);
+ }
+
// now check the join type and add the join to the
// appropriate places in the query
SqlEnum joinType = join.getJoinType();
@@ -144,9 +162,9 @@
rightTableName, null, null);
queryFromClause.add(fromElement);
}
- queryWhereClause.add(
- SqlExpression.buildInnerJoin(
- leftColumn, rightColumn, ignoreCase, db));
+ queryWhereClause.add(joinCriterion != null ?
qc.process(
+ joinCriterion, params) :
SqlExpression.buildInnerJoin(
+ leftColumn, rightColumn,
ignoreCase, db));
}
else
{
@@ -168,12 +186,11 @@
queryFromClause.add(fromElement);
}
- Query.FromElement fromElement
- = new Query.FromElement(
- rightTableName, joinType,
- SqlExpression.buildInnerJoin(
- leftColumn, rightColumn,
- ignoreCase, db));
+ Query.FromElement fromElement = new
Query.FromElement(
+ rightTableName,
joinType,
+ joinCriterion != null ?
qc.process(joinCriterion,
+ params)
: SqlExpression.buildInnerJoin(
+
leftColumn, rightColumn, ignoreCase, db));
queryFromClause.add(fromElement);
}
else
@@ -194,12 +211,11 @@
// now add the join in reverse order
// rightTableName must not be added
// because it is already present
- Query.FromElement fromElement
- = new Query.FromElement(
- leftTableName, reverseJoinType(joinType),
- SqlExpression.buildInnerJoin(
- rightColumn, leftColumn,
- ignoreCase, db));
+ Query.FromElement fromElement = new
Query.FromElement(
+ leftTableName,
reverseJoinType(joinType),
+ joinCriterion != null ?
qc.process(joinCriterion,
+ params)
: SqlExpression.buildInnerJoin(
+
rightColumn, leftColumn, ignoreCase, db));
queryFromClause.add(fromElement);
}
}
Index: src/java/org/apache/torque/util/SQLBuilder.java
===================================================================
--- src/java/org/apache/torque/util/SQLBuilder.java (revision 1340512)
+++ src/java/org/apache/torque/util/SQLBuilder.java (working copy)
@@ -292,11 +292,11 @@
final DB db = Torque.getDB(dbName);
final DatabaseMap dbMap = Torque.getDatabaseMap(dbName);
- JoinBuilder.processJoins(db, dbMap, crit, query);
+ JoinBuilder.processJoins(db, dbMap, crit, query, params, qc);
processModifiers(crit, query);
processSelectColumns(crit, query, dbName);
processAsColumns(crit, query);
- processCriterions(db, dbMap, dbName, crit, query, params, qc);
+ processCriterions(db, dbMap, dbName, crit, query, params, qc);
processGroupBy(crit, query);
processHaving(crit, query);
processOrderBy(db, dbMap, crit, query);
Regards,
Graham
--
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
