Re: [eclipselink-users] Using EclilpseLink Expressions Tricky propblem?

[tbianchi <tbianchini@xxxxxxxxxxxxx>]

Thanks James.  
I needed to put this aside but I will get back to it in a couple days and
will follow up with my results. 


James Sutherland wrote:
> 
> Try,
> 
> ReadAllQuery rq = new ReadAllQuery(Calendar.class);
>            
> exp1 = rq.getExpressionBuilder().get("calendar_id").equal(11);
> exp2 =
> rq.getExpressionBuilder().anyOf("scheduledList").get("frequencyId").equal(5);
> 
> rq.setSelectionCriteria(exp1.and(exp2));
> rq.addJoinedAttribute(rq.getExpressionBuilder().anyOf("scheduledList"));
> 
> Note, that is will join scheduled twice, as the is required to build the
> schedules correctly.  If all of the schedules have the frequency you could
> avoid the double join by declaring a variable for the anyOf.
> 
> 
> 
> tbianchi wrote:
>> 
>> 
>> I have two tables, Calendar and Scheduled. I have two Entity classes by
>> the same names.
>> 
>> There is a one-to-many relationship from (one)Calendar to (many)Scheduled
>> defined
>> by: 
>> @OneToMany(mappedBy="calendar") //calendar is the inverse property on the
>> target entity
>> private List<Scheduled> scheduledList = new ArrayList<Scheduled>();
>> 
>> code:
>> 
>> ReadAllQuery rq = new ReadAllQuery(Calendar.class);
>>             
>> exp1 = rq.getExpressionBuilder().get("calendar_id").equal(11);
>> exp2 = rq.getExpressionBuilder().get("frequencyId").equal(5);
>> 
>> rq.setSelectionCriteria(exp1.and(exp2));
>> rq.addJoinedAttribute(rq.getExpressionBuilder().anyOf("scheduledList"));
>> 
>> Vector result = (Vector)session.executeQuery(rq);
>> 
>> Everything compiles and runs and produces an output and populates the
>> Calendar class 
>> with all its fields and populates the scheduledList with Scheduled objs
>> with all their fields, etc.
>> 
>> At the bottom is the generated SQL from the log file.
>> 
>> Here is the problem:
>> You'll notice in the sql that freqency_id is a field in both tables,
>> calendar t1 and scheduled t0.
>> I need the above Expression, exp2: get("frequencyId").equal(5), as
>> selection criteria 
>> on the scheduled (t0) table NOT on the calendar (t1) table.  
>> In other words, NOT (t1.frequency_id = ?) BUT (t0.frequency_id = ?)
>> 
>> How do I do this? 
>> Any help? Thanks in advance!
>> Ton
>> 
>> [EL Fine]: 2010-06-10
>> 13:01:37.278--ServerSession(30617157)--Connection(29819669)--Thread(Thread[main,5,main])--
>> SELECT DISTINCT t1.calendar_id, t1.div_sub, t1.frequency_id, 
>> t1.suppress_flag, t1.active_flag, t1.update_id, t1.plan_num, 
>> t0.sch_record_id, t0.active, t0.calendar_id, t0.frequency_id, 
>> t0.nonscheduled_only, t0.status, t0.trade_date, t0.scheduled_date, 
>> t0.run_date, t0.fileid_name 
>> FROM scheduled t0, calendar t1 
>> WHERE (((t1.calendar_id = ?) AND (t1.frequency_id = ?)) AND
>> (t0.calendar_id = t1.calendar_id))
>> 
>> 
>> 
>> 
> 
> 

-- 
View this message in context: http://old.nabble.com/Using-EclilpseLink-Expressions-Tricky-propblem--tp28847472p28892648.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.