You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Tony Giaccone <tg...@gmail.com> on 2018/08/14 20:11:37 UTC
Clearing out many objects that are contained in a to many relationship
I have an object that contains a to many relationship to potentially 100's
of thousands of objects. I read in a file and generate this relationship
based on the contents of that file.
Imagine that after the relationship has been created, we realize that the
file contained errors and we want to recreate this relationship by reading
a new file that describes the complete set of entries in the relationship.
Note, I don't want to delete the objects that make up the toMany
relationship. I just want to break the relationship between the source and
the many destination objects.
My first thought is to null out the relationship, There's a foreign key in
the dependent object that describes its parent. So first step is set all
those values to null.
Then I'll iterate through the list, recreating the relationship based on
the contents of the file.
This is the brute force approach that solves the problem of what do I do
about entries in the old list that aren't in the new list.
My question is this. Is there an easy way to break that connection. I
looked didn't see anything.
Essentially want to do this:
Assume two tables t1,t2.
There is a to-Many relationship between t1 and t2. The foreign key column
in table t2 is fkt1.
update t2 set fkt1 = null where fkt1 = %A_VALUE;
This is kind of counter to the Cayenne way of doing things, but it's a
large number of objects and I can't see the point in iterating through them
on by one to delete the relationship.
Tony
Re: Clearing out many objects that are contained in a to many relationship
Posted by Tony <to...@giaccone.org>.
Andrus,
Perfect thank you
Tony Giaccone
> On Aug 15, 2018, at 9:16 AM, Andrus Adamchik <an...@objectstyle.org> wrote:
>
> Yeah. In the Modeler: "Project > Create Query > Raw SQL > Create"
>
> Usage per https://cayenne.apache.org/docs/4.0/cayenne-guide/#queries :
>
> List<Artist> results = MappedSelect
> .query("artistsByName", Artist.class)
>
> .param("name", "Picasso")
>
> .select(context);
>
> Andrus
>
>> On Aug 15, 2018, at 3:50 PM, Gmail <tg...@gmail.com> wrote:
>>
>> Follow up question. I see that it’s possible to embed names sql in the model file. How do you retrieve that named sql??
>>
>>
>> Tony Giaccone
>>
>>> On Aug 14, 2018, at 4:41 PM, Bob Schellink <sa...@gmail.com> wrote:
>>>
>>> Cayenne supports EJBQL[1] and SQLTemplate[2] for this sort of thing. Not
>>> sure if Cayenne is 100% EJBQL compliant though?
>>>
>>> But if all else fails fallback to SQLTemplate :)
>>>
>>> kind regards
>>>
>>> Bob
>>>
>>> [1]: https://cayenne.apache.org/docs/4.1/cayenne-guide/#ejbql
>>> [2]: https://cayenne.apache.org/docs/4.1/cayenne-guide/#sqltemplate
>>>
>>>> On Tue, Aug 14, 2018 at 10:11 PM Tony Giaccone <tg...@gmail.com> wrote:
>>>>
>>>> I have an object that contains a to many relationship to potentially 100's
>>>> of thousands of objects. I read in a file and generate this relationship
>>>> based on the contents of that file.
>>>>
>>>> Imagine that after the relationship has been created, we realize that the
>>>> file contained errors and we want to recreate this relationship by reading
>>>> a new file that describes the complete set of entries in the relationship.
>>>>
>>>> Note, I don't want to delete the objects that make up the toMany
>>>> relationship. I just want to break the relationship between the source and
>>>> the many destination objects.
>>>>
>>>> My first thought is to null out the relationship, There's a foreign key in
>>>> the dependent object that describes its parent. So first step is set all
>>>> those values to null.
>>>>
>>>> Then I'll iterate through the list, recreating the relationship based on
>>>> the contents of the file.
>>>>
>>>> This is the brute force approach that solves the problem of what do I do
>>>> about entries in the old list that aren't in the new list.
>>>>
>>>> My question is this. Is there an easy way to break that connection. I
>>>> looked didn't see anything.
>>>>
>>>> Essentially want to do this:
>>>>
>>>> Assume two tables t1,t2.
>>>> There is a to-Many relationship between t1 and t2. The foreign key column
>>>> in table t2 is fkt1.
>>>>
>>>> update t2 set fkt1 = null where fkt1 = %A_VALUE;
>>>>
>>>> This is kind of counter to the Cayenne way of doing things, but it's a
>>>> large number of objects and I can't see the point in iterating through them
>>>> on by one to delete the relationship.
>>>>
>>>>
>>>>
>>>>
>>>> Tony
>>>>
>
Re: Clearing out many objects that are contained in a to many
relationship
Posted by Andrus Adamchik <an...@objectstyle.org>.
Yeah. In the Modeler: "Project > Create Query > Raw SQL > Create"
Usage per https://cayenne.apache.org/docs/4.0/cayenne-guide/#queries :
List<Artist> results = MappedSelect
.query("artistsByName", Artist.class)
.param("name", "Picasso")
.select(context);
Andrus
> On Aug 15, 2018, at 3:50 PM, Gmail <tg...@gmail.com> wrote:
>
> Follow up question. I see that it’s possible to embed names sql in the model file. How do you retrieve that named sql??
>
>
> Tony Giaccone
>
>> On Aug 14, 2018, at 4:41 PM, Bob Schellink <sa...@gmail.com> wrote:
>>
>> Cayenne supports EJBQL[1] and SQLTemplate[2] for this sort of thing. Not
>> sure if Cayenne is 100% EJBQL compliant though?
>>
>> But if all else fails fallback to SQLTemplate :)
>>
>> kind regards
>>
>> Bob
>>
>> [1]: https://cayenne.apache.org/docs/4.1/cayenne-guide/#ejbql
>> [2]: https://cayenne.apache.org/docs/4.1/cayenne-guide/#sqltemplate
>>
>>> On Tue, Aug 14, 2018 at 10:11 PM Tony Giaccone <tg...@gmail.com> wrote:
>>>
>>> I have an object that contains a to many relationship to potentially 100's
>>> of thousands of objects. I read in a file and generate this relationship
>>> based on the contents of that file.
>>>
>>> Imagine that after the relationship has been created, we realize that the
>>> file contained errors and we want to recreate this relationship by reading
>>> a new file that describes the complete set of entries in the relationship.
>>>
>>> Note, I don't want to delete the objects that make up the toMany
>>> relationship. I just want to break the relationship between the source and
>>> the many destination objects.
>>>
>>> My first thought is to null out the relationship, There's a foreign key in
>>> the dependent object that describes its parent. So first step is set all
>>> those values to null.
>>>
>>> Then I'll iterate through the list, recreating the relationship based on
>>> the contents of the file.
>>>
>>> This is the brute force approach that solves the problem of what do I do
>>> about entries in the old list that aren't in the new list.
>>>
>>> My question is this. Is there an easy way to break that connection. I
>>> looked didn't see anything.
>>>
>>> Essentially want to do this:
>>>
>>> Assume two tables t1,t2.
>>> There is a to-Many relationship between t1 and t2. The foreign key column
>>> in table t2 is fkt1.
>>>
>>> update t2 set fkt1 = null where fkt1 = %A_VALUE;
>>>
>>> This is kind of counter to the Cayenne way of doing things, but it's a
>>> large number of objects and I can't see the point in iterating through them
>>> on by one to delete the relationship.
>>>
>>>
>>>
>>>
>>> Tony
>>>
Re: Clearing out many objects that are contained in a to many relationship
Posted by Gmail <tg...@gmail.com>.
Follow up question. I see that it’s possible to embed names sql in the model file. How do you retrieve that named sql??
Tony Giaccone
> On Aug 14, 2018, at 4:41 PM, Bob Schellink <sa...@gmail.com> wrote:
>
> Cayenne supports EJBQL[1] and SQLTemplate[2] for this sort of thing. Not
> sure if Cayenne is 100% EJBQL compliant though?
>
> But if all else fails fallback to SQLTemplate :)
>
> kind regards
>
> Bob
>
> [1]: https://cayenne.apache.org/docs/4.1/cayenne-guide/#ejbql
> [2]: https://cayenne.apache.org/docs/4.1/cayenne-guide/#sqltemplate
>
>> On Tue, Aug 14, 2018 at 10:11 PM Tony Giaccone <tg...@gmail.com> wrote:
>>
>> I have an object that contains a to many relationship to potentially 100's
>> of thousands of objects. I read in a file and generate this relationship
>> based on the contents of that file.
>>
>> Imagine that after the relationship has been created, we realize that the
>> file contained errors and we want to recreate this relationship by reading
>> a new file that describes the complete set of entries in the relationship.
>>
>> Note, I don't want to delete the objects that make up the toMany
>> relationship. I just want to break the relationship between the source and
>> the many destination objects.
>>
>> My first thought is to null out the relationship, There's a foreign key in
>> the dependent object that describes its parent. So first step is set all
>> those values to null.
>>
>> Then I'll iterate through the list, recreating the relationship based on
>> the contents of the file.
>>
>> This is the brute force approach that solves the problem of what do I do
>> about entries in the old list that aren't in the new list.
>>
>> My question is this. Is there an easy way to break that connection. I
>> looked didn't see anything.
>>
>> Essentially want to do this:
>>
>> Assume two tables t1,t2.
>> There is a to-Many relationship between t1 and t2. The foreign key column
>> in table t2 is fkt1.
>>
>> update t2 set fkt1 = null where fkt1 = %A_VALUE;
>>
>> This is kind of counter to the Cayenne way of doing things, but it's a
>> large number of objects and I can't see the point in iterating through them
>> on by one to delete the relationship.
>>
>>
>>
>>
>> Tony
>>
Re: Clearing out many objects that are contained in a to many relationship
Posted by Bob Schellink <sa...@gmail.com>.
Cayenne supports EJBQL[1] and SQLTemplate[2] for this sort of thing. Not
sure if Cayenne is 100% EJBQL compliant though?
But if all else fails fallback to SQLTemplate :)
kind regards
Bob
[1]: https://cayenne.apache.org/docs/4.1/cayenne-guide/#ejbql
[2]: https://cayenne.apache.org/docs/4.1/cayenne-guide/#sqltemplate
On Tue, Aug 14, 2018 at 10:11 PM Tony Giaccone <tg...@gmail.com> wrote:
> I have an object that contains a to many relationship to potentially 100's
> of thousands of objects. I read in a file and generate this relationship
> based on the contents of that file.
>
> Imagine that after the relationship has been created, we realize that the
> file contained errors and we want to recreate this relationship by reading
> a new file that describes the complete set of entries in the relationship.
>
> Note, I don't want to delete the objects that make up the toMany
> relationship. I just want to break the relationship between the source and
> the many destination objects.
>
> My first thought is to null out the relationship, There's a foreign key in
> the dependent object that describes its parent. So first step is set all
> those values to null.
>
> Then I'll iterate through the list, recreating the relationship based on
> the contents of the file.
>
> This is the brute force approach that solves the problem of what do I do
> about entries in the old list that aren't in the new list.
>
> My question is this. Is there an easy way to break that connection. I
> looked didn't see anything.
>
> Essentially want to do this:
>
> Assume two tables t1,t2.
> There is a to-Many relationship between t1 and t2. The foreign key column
> in table t2 is fkt1.
>
> update t2 set fkt1 = null where fkt1 = %A_VALUE;
>
> This is kind of counter to the Cayenne way of doing things, but it's a
> large number of objects and I can't see the point in iterating through them
> on by one to delete the relationship.
>
>
>
>
> Tony
>