Eclipselink left outer join dynamic query malformed

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:

personId(pk)

gender

The key properties of Address class are:

addressId(pk)

personId(fk)

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

edited Dec 29 '18 at 11:59

halfer

14.3k758109

asked Nov 19 '18 at 17:07

anup nair

why is gender in your address table?
– Gratus D.
Nov 19 '18 at 17:54

What are the results of the two queries? They seem like they would give the same results, so what exactly are you looking for? If you look at your expressions, you are building two separate joins by calling expBuilder.anyOfAllowingNone("address") twice.
– Chris
Nov 19 '18 at 22:17

@Chris The results are identical for both the queries.I am calling anyOfAllowingNone("address") twice since addressId whose count() is required is part of Address table in 1:M relationship. However, i need both the conditions to be part of one join only viz. PERSON t0 LEFT OUTER JOIN ADDRESS t1 ON (t1.PID = t0.PID and (t2.gender = 'M')). Could you please suggest the expression to add gender condition to one join?
– anup nair
Nov 20 '18 at 8:07

@Chris If I introduce new entity Employee which is subclass of Person. The code for same in buildEmployeeDescriptor() method is: descriptor.getDescriptorInheritancePolicy().setParentClass(Person.class); I have the following code snippet: ExpressionBuilder expBuilder = new ExpressionBuilder(); ReportQuery query = new ReportQuery(Employee.class, expBuilder); Expression employeeExp = expBuilder.getAllowingNull("address"); This expression should outer join Employee and Address table but it is outer joining Address with Person. How to skip Person table?
– anup nair
Nov 21 '18 at 13:20

The address relationship is defined on the Person class, so it is a Person->Address relationship and has nothing to do with Employee. I assume you use join inheritance, but the fact Address may have a foreign key value that matches the Employee ID (which may also match the Person ID) isn't a given, and EclipseLink is forced to use the relationships it has and filter based on the inheritance setup. I can't say more on how to get what you want without knowing the full example - write a new question.
– Chris
Nov 21 '18 at 14:56

|
show 1 more comment

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:

personId(pk)

gender

The key properties of Address class are:

addressId(pk)

personId(fk)

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

edited Dec 29 '18 at 11:59

halfer

14.3k758109

asked Nov 19 '18 at 17:07

anup nair

why is gender in your address table?
– Gratus D.
Nov 19 '18 at 17:54

What are the results of the two queries? They seem like they would give the same results, so what exactly are you looking for? If you look at your expressions, you are building two separate joins by calling expBuilder.anyOfAllowingNone("address") twice.
– Chris
Nov 19 '18 at 22:17

@Chris The results are identical for both the queries.I am calling anyOfAllowingNone("address") twice since addressId whose count() is required is part of Address table in 1:M relationship. However, i need both the conditions to be part of one join only viz. PERSON t0 LEFT OUTER JOIN ADDRESS t1 ON (t1.PID = t0.PID and (t2.gender = 'M')). Could you please suggest the expression to add gender condition to one join?
– anup nair
Nov 20 '18 at 8:07

@Chris If I introduce new entity Employee which is subclass of Person. The code for same in buildEmployeeDescriptor() method is: descriptor.getDescriptorInheritancePolicy().setParentClass(Person.class); I have the following code snippet: ExpressionBuilder expBuilder = new ExpressionBuilder(); ReportQuery query = new ReportQuery(Employee.class, expBuilder); Expression employeeExp = expBuilder.getAllowingNull("address"); This expression should outer join Employee and Address table but it is outer joining Address with Person. How to skip Person table?
– anup nair
Nov 21 '18 at 13:20

The address relationship is defined on the Person class, so it is a Person->Address relationship and has nothing to do with Employee. I assume you use join inheritance, but the fact Address may have a foreign key value that matches the Employee ID (which may also match the Person ID) isn't a given, and EclipseLink is forced to use the relationships it has and filter based on the inheritance setup. I can't say more on how to get what you want without knowing the full example - write a new question.
– Chris
Nov 21 '18 at 14:56

|
show 1 more comment

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:

personId(pk)

gender

The key properties of Address class are:

addressId(pk)

personId(fk)

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

edited Dec 29 '18 at 11:59

halfer

14.3k758109

asked Nov 19 '18 at 17:07

anup nair

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:

personId(pk)

gender

The key properties of Address class are:

addressId(pk)

personId(fk)

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

eclipselink

edited Dec 29 '18 at 11:59

halfer

14.3k758109

asked Nov 19 '18 at 17:07

anup nair

edited Dec 29 '18 at 11:59

halfer

14.3k758109

asked Nov 19 '18 at 17:07

anup nair

edited Dec 29 '18 at 11:59

halfer

14.3k758109

edited Dec 29 '18 at 11:59

halfer

14.3k758109

edited Dec 29 '18 at 11:59

halfer

14.3k758109

asked Nov 19 '18 at 17:07

anup nair

asked Nov 19 '18 at 17:07

anup nair

asked Nov 19 '18 at 17:07

anup nair

why is gender in your address table?
– Gratus D.
Nov 19 '18 at 17:54

What are the results of the two queries? They seem like they would give the same results, so what exactly are you looking for? If you look at your expressions, you are building two separate joins by calling expBuilder.anyOfAllowingNone("address") twice.
– Chris
Nov 19 '18 at 22:17

@Chris The results are identical for both the queries.I am calling anyOfAllowingNone("address") twice since addressId whose count() is required is part of Address table in 1:M relationship. However, i need both the conditions to be part of one join only viz. PERSON t0 LEFT OUTER JOIN ADDRESS t1 ON (t1.PID = t0.PID and (t2.gender = 'M')). Could you please suggest the expression to add gender condition to one join?
– anup nair
Nov 20 '18 at 8:07

@Chris If I introduce new entity Employee which is subclass of Person. The code for same in buildEmployeeDescriptor() method is: descriptor.getDescriptorInheritancePolicy().setParentClass(Person.class); I have the following code snippet: ExpressionBuilder expBuilder = new ExpressionBuilder(); ReportQuery query = new ReportQuery(Employee.class, expBuilder); Expression employeeExp = expBuilder.getAllowingNull("address"); This expression should outer join Employee and Address table but it is outer joining Address with Person. How to skip Person table?
– anup nair
Nov 21 '18 at 13:20

The address relationship is defined on the Person class, so it is a Person->Address relationship and has nothing to do with Employee. I assume you use join inheritance, but the fact Address may have a foreign key value that matches the Employee ID (which may also match the Person ID) isn't a given, and EclipseLink is forced to use the relationships it has and filter based on the inheritance setup. I can't say more on how to get what you want without knowing the full example - write a new question.
– Chris
Nov 21 '18 at 14:56

|
show 1 more comment

why is gender in your address table?
– Gratus D.
Nov 19 '18 at 17:54

What are the results of the two queries? They seem like they would give the same results, so what exactly are you looking for? If you look at your expressions, you are building two separate joins by calling expBuilder.anyOfAllowingNone("address") twice.
– Chris
Nov 19 '18 at 22:17

@Chris The results are identical for both the queries.I am calling anyOfAllowingNone("address") twice since addressId whose count() is required is part of Address table in 1:M relationship. However, i need both the conditions to be part of one join only viz. PERSON t0 LEFT OUTER JOIN ADDRESS t1 ON (t1.PID = t0.PID and (t2.gender = 'M')). Could you please suggest the expression to add gender condition to one join?
– anup nair
Nov 20 '18 at 8:07

@Chris If I introduce new entity Employee which is subclass of Person. The code for same in buildEmployeeDescriptor() method is: descriptor.getDescriptorInheritancePolicy().setParentClass(Person.class); I have the following code snippet: ExpressionBuilder expBuilder = new ExpressionBuilder(); ReportQuery query = new ReportQuery(Employee.class, expBuilder); Expression employeeExp = expBuilder.getAllowingNull("address"); This expression should outer join Employee and Address table but it is outer joining Address with Person. How to skip Person table?
– anup nair
Nov 21 '18 at 13:20

The address relationship is defined on the Person class, so it is a Person->Address relationship and has nothing to do with Employee. I assume you use join inheritance, but the fact Address may have a foreign key value that matches the Employee ID (which may also match the Person ID) isn't a given, and EclipseLink is forced to use the relationships it has and filter based on the inheritance setup. I can't say more on how to get what you want without knowing the full example - write a new question.
– Chris
Nov 21 '18 at 14:56

why is gender in your address table?
– Gratus D.
Nov 19 '18 at 17:54

What are the results of the two queries? They seem like they would give the same results, so what exactly are you looking for? If you look at your expressions, you are building two separate joins by calling expBuilder.anyOfAllowingNone("address") twice.
– Chris
Nov 19 '18 at 22:17

@Chris The results are identical for both the queries.I am calling anyOfAllowingNone("address") twice since addressId whose count() is required is part of Address table in 1:M relationship. However, i need both the conditions to be part of one join only viz. PERSON t0 LEFT OUTER JOIN ADDRESS t1 ON (t1.PID = t0.PID and (t2.gender = 'M')). Could you please suggest the expression to add gender condition to one join?
– anup nair
Nov 20 '18 at 8:07

@Chris If I introduce new entity Employee which is subclass of Person. The code for same in buildEmployeeDescriptor() method is: descriptor.getDescriptorInheritancePolicy().setParentClass(Person.class); I have the following code snippet: ExpressionBuilder expBuilder = new ExpressionBuilder(); ReportQuery query = new ReportQuery(Employee.class, expBuilder); Expression employeeExp = expBuilder.getAllowingNull("address"); This expression should outer join Employee and Address table but it is outer joining Address with Person. How to skip Person table?
– anup nair
Nov 21 '18 at 13:20

The address relationship is defined on the Person class, so it is a Person->Address relationship and has nothing to do with Employee. I assume you use join inheritance, but the fact Address may have a foreign key value that matches the Employee ID (which may also match the Person ID) isn't a given, and EclipseLink is forced to use the relationships it has and filter based on the inheritance setup. I can't say more on how to get what you want without knowing the full example - write a new question.
– Chris
Nov 21 '18 at 14:56

|
show 1 more comment

1 Answer
1

active

oldest

votes

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.

answered Nov 20 '18 at 14:08

Chris

15.3k12235

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53379524%2feclipselink-left-outer-join-dynamic-query-malformed%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

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.

answered Nov 20 '18 at 14:08

Chris

15.3k12235

add a comment |

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.

answered Nov 20 '18 at 14:08

Chris

15.3k12235

add a comment |

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.

answered Nov 20 '18 at 14:08

Chris

15.3k12235

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.

answered Nov 20 '18 at 14:08

Chris

15.3k12235

answered Nov 20 '18 at 14:08

Chris

15.3k12235

answered Nov 20 '18 at 14:08

Chris

15.3k12235

answered Nov 20 '18 at 14:08

Chris

15.3k12235

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu