Hi Dan! It seems quite strange to me that relationship from PARTY_CASE_LINK to PARTY is called "parties". So seems it's to-many. But PARTY_CASE_LINK has "partyid", so no more than one PARTY can exist for PARTY_CASE_LINK. Also can't flattened relationships [1] be useful for you in this case?
[1] http://cayenne.apache.org/doc/flattened-relationships.html Andrey 2009/3/17 Dan <[email protected]> > Greets! Neat product. I'm having a newb issue which I'm hopeful someone can > help > with. Here is the relevant layout: I have 2 tables (PARTY and CASE) and a > third > lookup table which maps the many-to-many (PARTY_CASE_LINK) - since each > party > can belong to 0 or more cases and each case can include 1 or more parties. > > PARTY PARTY_CASE_LINK CASE > ____________________ ____________________ ____________________ > id party_id id > firm case_id foo > foo .. bar > > I want to start my lookup by loading a party by criteria and being able to > get > to all of the cases for that party. I have the relationships onships setup > in > the modeler correctly (me thinks) -- since i am able to get a List<Party> > and > from there I can use the relationships to get List<PartyCases> via > getPartyCases() and from there get List<Case> via getCases() -- works like > a > charm! > > I want to avoid an N+1 lookup, so I'm **attempting** to use addPrefetch to > my > inial PARTY lookup. That's where it croaks. > > I am also using FetchLimit and FetchOffset (not that I think it should > matter, > but in full discolsure) > > Expression qualifier = ExpressionFactory.matchExp(Party.FIRM_PROPERTY, > firm); > SelectQuery selectQuery = new SelectQuery(Party.class, qualifier); > > selectQuery.addOrdering(rs.getSort(), rs.isSortAscending()); > selectQuery.setFetchLimit(rs.getPageSize()); > selectQuery.setFetchOffset(rs.getStartIndex()); > > selectQuery.addPrefetch("partyCases"); > > paritesAttachedToFirm = (List<Party>) > dataContext.performQuery(selectQuery); > > 09:46:46,558 DEBUG BeforeAfterMethodInterceptor:183 - Calling @Before > method > 'fillData' at LifecycleStage 'BindingAndValidation' on ActionBean > 'JSONPartiesActionBean' > 09:46:46,577 INFO QueryLogger:473 - --- will run 2 queries. > 09:46:46,579 INFO QueryLogger:427 - --- transaction started. > 09:46:46,620 INFO QueryLogger:357 - SELECT t0.middle_name, t0.last_name, > t0.sex, t0.firm_id, t0.id, t0.official_id_type, t0.party_type_id, t0.race, > t0.address_append_status_id, t0.dob, t0.invite_code, t0.dod, t0.first_name, > t0.official_id FROM PARTY t0 WHERE t0.firm_id = ? ORDER BY t0.last_name > LIMIT 25 > OFFSET 0 [bind: 1->firm_id:2] - prepared in 40 ms. > 09:46:46,627 INFO QueryLogger:401 - === returned 25 rows. - took 48 ms. > 09:46:46,631 INFO QueryLogger:357 - SELECT DISTINCT t0.case_id, > t0.party_id > FROM PARTY_CASE_LINK t0 JOIN PARTY t1 ON (t0.party_id = t1.id) WHERE > t1.firm_id > = ? [bind: 1->firm_id:2] > 09:46:46,635 INFO QueryLogger:401 - === returned 110 rows. - took 8 ms. > 09:46:46,636 INFO QueryLogger:434 - +++ transaction committed. > java.lang.ClassCastException: org.apache.cayenne.access.ToManyList cannot > be > cast to org.apache.cayenne.Persistent at org.apache.cayenne.access. > ObjectTreeResolver$DisjointProcessor.startDisjointPrefetch > (ObjectTreeResolver.java:335) > at > org.apache.cayenne.query.PrefetchTreeNode.traverse(PrefetchTreeNode.java:165) > > What else can I tell you to help here: > > In the modeler I have the relationship from PARTY to PARTY_CASE_LINK called > "partyCases" and it's "To Many" The reverse relationship is called > "parties" > > Thanks for your thoughts and advise. > > Dan > > > > >
