There's nothing in Cayenne that supports hierarchical or recursive queries yet. I'm sure we'd accept patches to add a new Expression type to do so, although it'll probably only work for a select set of databases.
On Sat, Aug 8, 2015 at 2:05 PM, Hugi Thordarson <h...@karlmenn.is> wrote: > Thanks Mike. I’m currently using MySQL (and in the process of migrating to > PostgreSQL) and there are ways to do this with both DBs directly. Just though > it was worthwhile to check if there was something in Cayenne for it so I > could avoid using DB specific syntax :). > > Cheers, > - hugi > > // Hugi Thordarson > // http://www.loftfar.is/ <http://www.loftfar.is/> > // s. 895-6688 > > > >> On 8. ágú. 2015, at 14:43, Mike Kienenberger <mkien...@gmail.com> wrote: >> >> I'm pretty sure you said you were using Oracle so you might be better >> off using an SQLTemplate with oracle-specific code to pull in all of >> these objects in one query, particularly if there might be a large >> number of them. >> >> There's a bunch of different ways to do it, and I don't have direct >> access to how I've done it in the past (and it might have been under >> JPA in any case), but this link has one approach: >> >> http://stackoverflow.com/questions/17358109/how-to-retrieve-all-recursive-children-of-parent-row-in-oracle-sql >> >> Once you know what the SQL will look like, using SQLTemplate can >> generate a list of entities for you after executing the query. >> >> On Sat, Aug 8, 2015 at 7:14 AM, Hugi Thordarson <h...@karlmenn.is> wrote: >>> Hi all. >>> >>> Let’s say an entity has a self referencing relationship, resulting in two >>> modeled relationship definitions in the entity called “parent” and >>> “children” (hopefully pretty self-explanatory). >>> >>> Using Cayenne, is it possible, given a record (a node in the tree) to >>> perform a prefetch so that all child nodes are prefetched? >>> >>> I’m currently doing this (where I just go as deep as I think the tree will >>> be) which seems to work fine, but feels a little wonky :) >>> >>> query.addPrefetch( Item.CHILDREN.joint() ); >>> query.addPrefetch( Item.CHILDREN.dot( Item.CHILDREN ).joint() ); >>> query.addPrefetch( Item.CHILDREN.dot( Item.CHILDREN ).dot( Item.CHILDREN >>> ).joint() ); >>> query.addPrefetch( Item.CHILDREN.dot( Item.CHILDREN ).dot( Item.CHILDREN >>> ).dot( Item.CHILDREN ).joint() ); >>> >>> Cheers, >>> - hugi >>> >>> // Hugi Thordarson >>> // http://www.loftfar.is/ <http://www.loftfar.is/> >>> // s. 895-6688 >