2 Replies Latest reply: Feb 27, 2013 2:35 PM by 991433 RSS

    Using Outer joins with an inheritance policy inside a ReadAllQuery

    991433
      I have an issue with applying ordering to a ReadAllQuery where an outer join is created by using getAllowingNull() and the object referenced has an inheritance policy.

      Currently the query produced looks like:

      SELECT t0.OBJECTID FROM OBJECT t0, USER t2, ENTITY t1 WHERE ((((t0.DELETED = 'F')) AND ((t1.ENTITYID (+) = t0.RECEIVEDBY) AND ((t2.ENTITYID (+) = t1.ENTITYID) AND (t1.CLASSINDICATOR = 1)))) ORDER BY DECODE(t1.ENTITYID, 1234, 'Last, First','') DESC, t0.OBJECTID DESC

      This does not include results where t0.RECEIVEDBY is null.
      The documentation for oracle states ( Joins ): "or apply the outer join operator (+) to all columns of B in the join condition in the WHERE clause."
      I have experimented with the query against our oracle database and found that the following query returns the results I want:

      SELECT t0.OBJECTID FROM OBJECT t0, USER t2, ENTITY t1 WHERE ((((t0.DELETED = 'F')) AND ((t1.ENTITYID (+) = t0.RECEIVEDBY) AND ((t2.ENTITYID (+) = t1.ENTITYID) AND (t1.CLASSINDICATOR *(+)* = 1)))) ORDER BY DECODE(t1.ENTITYID, 1234, 'Last, First','') DESC, t0.OBJECTID DESC

      So my question is, how can I tell toplink that I need the CLASSINDICATOR column to participate in the left join?

      Here are the relevant portions of my descriptors.

      InheritancePolicy inheritancePolicy = descriptor.getInheritancePolicy();
      inheritancePolicy.setClassIndicatorFieldName("ENTITY.CLASSINDICATOR");
      inheritancePolicy.addClassIndicator(User.class, Integer.valueOf(1));
      inheritancePolicy.addClassIndicator(Company.class, Integer.valueOf(2));

      My attempt to force the left join is done via:

      builder.getAllowingNull("receivedUser");