Re: [eclipselink-users] Should selection criteria be set after adding th

[Rohit Banga <rohit.banga@xxxxxxxxxx>]

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