db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Graham Leggett <minf...@sharp.fm>
Subject Re: Torque LEFT JOIN with multiple clauses
Date Mon, 04 Jun 2012 12:54:31 GMT
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 = &quot;PROJECT.PROJECT_ID&quot;
+     * right = &quot;FOO.PROJECT_ID&quot;
+     * 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: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org


Mime
View raw message