You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Hugi Thordarson <hu...@karlmenn.is> on 2015/08/08 13:14:03 UTC
Prefetching all child nodes in a tree
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
Re: Prefetching all child nodes in a tree
Posted by Hugi Thordarson <hu...@karlmenn.is>.
I suspected as much, since it’s a rather specialized case that requires different dialects for most DBs.
> On 8. ágú. 2015, at 18:54, Mike Kienenberger <mk...@gmail.com> wrote:
>
> 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 <hu...@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 <mk...@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 <hu...@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
>>
Re: Prefetching all child nodes in a tree
Posted by Mike Kienenberger <mk...@gmail.com>.
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 <hu...@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 <mk...@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 <hu...@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
>
Re: Prefetching all child nodes in a tree
Posted by Hugi Thordarson <hu...@karlmenn.is>.
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 <mk...@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 <hu...@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
Re: Prefetching all child nodes in a tree
Posted by Mike Kienenberger <mk...@gmail.com>.
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 <hu...@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