This discussion is archived
2 Replies Latest reply: Feb 27, 2013 12:35 PM by 991433 RSS

Using Outer joins with an inheritance policy inside a ReadAllQuery

991433 Newbie
Currently Being Moderated
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");

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points