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

[James Sutherland <jamesssss@xxxxxxxxx>]

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))
> 
> 
> 
> 


-----
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland 
http://www.eclipse.org/eclipselink/
 EclipseLink ,  http://www.oracle.com/technology/products/ias/toplink/
TopLink 
Wiki:  http://wiki.eclipse.org/EclipseLink EclipseLink , 
http://wiki.oracle.com/page/TopLink TopLink 
Forums:  http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink , 
http://www.nabble.com/EclipseLink-f26430.html EclipseLink 
Book:  http://en.wikibooks.org/wiki/Java_Persistence Java Persistence 
-- 
View this message in context: http://old.nabble.com/Using-EclilpseLink-Expressions-Tricky-propblem--tp28847472p28891941.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.