[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] Should selection criteria be set after adding the join attributes?

Thanks for clarifying.
Bug number is 332994.

On 12/21/2010 12:50 AM, Tom Ware wrote:
Hi Rohit,

 It is a bug that the behavior is different depending on the order of invocation. Please feel free to file a bug.

 Writing your code like this may help:

query.addJoinedAttribute((new ExpressionBuilder()).anyOf("empinfo.phone.empid"));

query.setSelectionCriteria((new ExpressionBuilder().anyOf("empinfo.phone.empid").get("phone").equal("11"));

 When our queries run, they will fix the ExpressionBuilders to be clean.

 If that does not help (please include that information in the bug), the following should work.

query.addJoinedAttribute((new ExpressionBuilder(empType)).anyOf("empinfo.phone.empid"));

query.setSelectionCriteria((new ExpressionBuilder(empType).anyOf("empinfo.phone.empid").get("phone").equal("11"));

-Tom


Rohit Banga wrote:
 Hi Tom,

No this does not work. Since we are reusing the /join/ _expression_ the query generated is

select * from empinfo t0, phone t1 where t0.empid=t1.empid and t1.phone='11'

in place of the following correct one

select * from empinfo t0, phone t1, phone t2, where t0.empid=t1.empid and t1.phone='11' and t2.empid=t0.empid

However instead of reusing the /join/ variable if I use the following code:

query.addJoinedAttribute(query.getExpressionBuilder().anyOf("empinfo.phone.empid"));
query.setSelectionCriteria(query.getExpressionBuilder().anyOf("empinfo.phone.empid").get("phone").equal("11"));

the query is generated correctly.

If I set the selection criteria before adding the join attribute, then the incorrect query is generated. *Is this expected by design? Why is it important to add the join attribute before setting the selection criteria?*

Thanks

On 12/20/2010 7:58 PM, Tom Ware wrote:
Hi Rohit,

 Does it help if you do something like this?

_expression_ join = query.getExpressionBuilder().anyOf("empinfo.phone.empid");
query.addJoinedAttribute(join);
query.setSelectionCriteria(join.get("phone").equal("11"));

-Tom

Rohit Banga wrote:
 Hi Tom

It seems the context of the question was not very clear from the mail. Sorry for that!
/"empinfo.phone.empid"/ is actually a join attribute for joining employee and phone tables.

Here is some sample code:


ÂÂÂÂÂÂÂ Class<?> emptype = dcl.createDynamicClass("jpatest.empinfo");
ÂÂÂÂÂÂÂ Class<?> phonetype = dcl.createDynamicClass("jpatest.phone");

ÂÂÂÂÂÂÂ DynamicTypeBuilder empBuilder = new JPADynamicTypeBuilder(primaryType, null, "empinfo");
ÂÂÂÂÂÂÂ DynamicTypeBuilder phoneBuilder = new JPADynamicTypeBuilder(secondaryType, null, "phone");

ÂÂÂÂÂÂÂ /**
ÂÂÂÂÂÂÂÂÂ ... add the direct mappings here
ÂÂÂÂÂÂÂÂ */

ÂÂÂÂÂÂÂ empBuilder.addOneToManyMapping("empinfo.phone.empid", phoneBuilder.getType(), "phone.empid");
ÂÂÂÂÂÂÂ phoneBuilder.addOneToOneMapping("empinfo.phone.empid", empBuilder.getType(), "phone.empid");

ÂÂÂÂÂÂÂ helper.addTypes(false, true, empBuilder.getType(), phoneBuilder.getType());


ÂÂÂÂÂÂÂ ReadAllQuery query = new DynamicHelper(session).newReadAllQuery(empBuilder.getType().getDescriptor().getAlias());

ÂÂÂÂÂÂÂ // if I reverse the order of the two, then the query is not generated properly as explained in the mail below
ÂÂÂÂÂÂÂ query.addJoinedAttribute(query.getExpressionBuilder().anyOf("empinfo.phone.empid"));
ÂÂÂÂÂÂÂ query.setSelectionCriteria(query.getExpressionBuilder().anyOf("empinfo.phone.empid").get("phone").equal("11"));



Thanks

Hi Rohit,

 I am a bit surprised ".anyOf("empinfo.phone.empid")" works at all. I would expect you to have to write something like: "get("empinfo").anyOf("phone").get("empid") - i.e. When using the _expression_ API, you should not use the "." notation within the method calls.

-Tom

Rohit Banga wrote:

Hello All

I have a phone table and an employee table. The phone table has a foreign key constraint on the employee id. An employee can have multiple phones.

Now when I query for a employee having phone number say '11' I set the selection criteria as:


query.setSelectionCriteria( query.getExpressionBuilder().anyOf("empinfo.phone.empid").get("phone").equal("11"));

I add the following join attribute:

query.addJoinAttribute(query.getExpressionBuilder().anyOf("empinfo.phone.empid"));

_Case 1:_ If I set the selection criteria before adding the join attribute the query generated is:

select * from empinfo t0, phone t1 where t0.empid=t1.empid and t1.phone='11'

which does not return all the phone numbers of the employee having phone number '11'.

_Case 2_: If I set the selection criteria after adding the join attribute the query generated is:

select * from empinfo t0, phone t1, phone t2, where t0.empid=t1.empid and t1.phone='11' and t2.empid=t0.empid

which returns all the phone numbers of the employee having phone number '11'.

Also in case 1 if I generate the query for the second time with the same session, the query of case 2 is generated, however again not all phone numbers of the employee having phone number '11' are generated (*perhaps due to caching?*)

If I do not set any selection criteria the first time I query, then all the information is returned the first time as expected. If I now use the sequence of Case 1 for creating a query in the same session then the correct result is obtained. *How to explain this?*

*Is this behavior expected? Why is it important to add the join attribute before setting the selection criteria?*
*Could you please point me to the relevant documentation?*

Thanks in Advance!

--Â
Thanks and Regards
Rohit Banga
Member Technical Staff
Oracle Server Technologies


------------------------------------------------------------------------

_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

--Â
Thanks and Regards
Rohit Banga
Member Technical Staff
Oracle Server Technologies

--Â
Thanks and Regards
Rohit Banga
Member Technical Staff
Oracle Server Technologies

--
Thanks and Regards
Rohit Banga
Member Technical Staff
Oracle Server Technologies