Eclipselink left outer join dynamic query malformed

Question

There are two entities Person and Address. There is a 1:M relationship from Person to Address. (A Person is assumed to have temporary and permanent address).

The key properties of Person class are:

1. personId(pk)
2. gender

The key properties of Address class are:

1. addressId(pk)
2. personId(fk)
3. gender

The following is the descriptor code snippet for Person and Address classes:

public RelationalDescriptor buildPersonDescriptor() {
RelationalDescriptor descriptor = new RelationalDescriptor();
descriptor.setJavaClass(Person.class);
descriptor.addTableName("PERSON");
descriptor.addPrimaryKeyFieldName("PERSON.PID");

// RelationalDescriptor properties.
descriptor.useSoftCacheWeakIdentityMap();
descriptor.setIdentityMapSize(100);
descriptor.useRemoteSoftCacheWeakIdentityMap();
descriptor.setRemoteIdentityMapSize(100);
descriptor.setSequenceNumberFieldName("PERSON.PID");
descriptor.setSequenceNumberName("PERSON_SEQ");
descriptor.setAlias("person");

// Query manager.
descriptor.getDescriptorQueryManager().checkCacheForDoesExist();
descriptor.getDescriptorQueryManager().setAdditionalJoinExpression(new ExpressionBuilder().get("gender").equal('N'));


// Query manager.

// Mappings.

DirectToFieldMapping pIDMapping = new DirectToFieldMapping();
pIDMapping.setAttributeName("personId");
pIDMapping.setFieldName("PERSON.PID");
descriptor.addMapping(pIDMapping);

DirectToFieldMapping genderMapping = new DirectToFieldMapping();
genderMapping.setAttributeName("gender");
genderMapping.setFieldName("PERSON.GENDER");
descriptor.addMapping(genderMapping);

OneToManyMapping addressMapping = new OneToManyMapping();
addressMapping.setAttributeName("address");
addressMapping.setReferenceClass(Address.class);
addressMapping.useTransparentCollection();
addressMapping.useCollectionClass(IndirectList.class);
addressMapping.addTargetForeignKeyFieldName("ADDRESS.PID", "PERSON.PID");
descriptor.addMapping(addressMapping);

return descriptor;
}


public RelationalDescriptor buildAddressDescriptor() {

RelationalDescriptor descriptor = new RelationalDescriptor();
  descriptor.setJavaClass(com.tropics.application.products.domain.costingandpricing.SellingPriceAddOn.class);
  descriptor.addTableName("ADDRESS");
  descriptor.addPrimaryKeyFieldName("ADDRESS.AID");

  // Descriptor properties.
  descriptor.useSoftCacheWeakIdentityMap();
  descriptor.setIdentityMapSize(100);
  descriptor.useRemoteSoftCacheWeakIdentityMap();
  descriptor.setRemoteIdentityMapSize(100);
  descriptor.setSequenceNumberFieldName("ADDRESS.AID");
  descriptor.setSequenceNumberName("ADDRESS_SEQ");
  descriptor.setAlias("address");

  // Query manager.
  descriptor.getDescriptorQueryManager().checkCacheForDoesExist();

  //Mappings
  DirectToFieldMapping genderMapping = new DirectToFieldMapping();
  genderMapping.setAttributeName("gender");
  genderMapping.setFieldName("ADDRESS.GENDER");
  descriptor.addMapping(genderMapping); 

  DirectToFieldMapping personIDMapping = new DirectToFieldMapping();
  personIDMapping.setAttributeName("personId");
  personIDMapping.setFieldName("ADDRESS.PID");
  descriptor.addMapping(personIDMapping);

  DirectToFieldMapping addressIDMapping = new DirectToFieldMapping();
  addressIDMapping.setAttributeName("addressId");
  addressIDMapping.setFieldName("ADDRESS.AID");
  descriptor.addMapping(addressIDMapping);  

}

Following is the code snippet for generating the dynamic query:

        ExpressionBuilder expBuilder = new ExpressionBuilder();
        ReportQuery query = new ReportQuery(Person.class, expBuilder);

        //Getting the MVSelling DetailsID and the number of Selling price add ons for each of them
        query.addAttribute("personId", expBuilder.get("personId"));
        query.addAttribute
        ("addressCounter", expBuilder.anyOfAllowingNone("address").get("addressId").count());
        Expression addressExp = expBuilder.anyOfAllowingNone("address");
        expBuilder.leftJoin(addressExp, addressExp.get("gender").equal('M'));
        query.addNonFetchJoin(addressExp);
        query.addGrouping("personId");
        resultCollection = (Vector)clientSessionHolder.eclipselinkClientSession().executeQuery(query);

On running this program, the query which is generated as per logs:

SELECT t0.PID, COUNT(t1.AID)
FROM PERSON t0 LEFT OUTER JOIN ADDRESS t1
ON (t1.PID = t0.PID)
LEFT OUTER JOIN ADDRESS t2
ON ((t2.PID  = t0.PID)
AND (t2.gender = 'M'))
WHERE (t0.gender = 'M')) GROUP BY t0.PID ;

How can I write the expression to add the gender condition(char data type in db) in the first join clause itself and get rid off of second join clause?

The expected query is: SELECT t0.PID, COUNT(t1.AID) FROM PERSON t0 LEFT OUTER JOIN ADDRESS t1 ON (t1.PID = t0.PID AND (t2.gender = 'M')) WHERE t0.gender = 'M' GROUP BY t0.PID

Answer 1

You have two separate joins because you are calling and using expBuilder.anyOfAllowingNone("address") twice in your expressions. anyOfAllowingNone tells EclipseLink to create an outer join over the relationship and to use it as the base of expressions built from this one.

Try

    Expression addressExp = expBuilder.anyOfAllowingNone("address");
    query.addAttribute("addressCounter", addressExp.get("addressId").count());
    expBuilder.leftJoin(addressExp, addressExp.get("gender").equal('M'));
    query.addNonFetchJoin(addressExp);

Reusing the addressExp will cause the join to be created only once and other paths built off of it instead of a new one.