m2m, addFoo drops all related records

[Raymond Camden]

Is this expected?

I've got a group entity with this property:

property name="members" fieldType="many-to-many"
cfc="root.model.user.user" linktable="group_member"
fkcolumn="groupidfk" inversejoincolumn="useridfk" lazy="true"
singularname="member";

When I run addMember to a group, I've noticed that the SQL actually
deletes all records in the link table that belong to the group and
than re-adds every single member.

--
===========================================================================
Raymond Camden, ColdFusion Jedi Master

Email    : r...@camdenfamily.com
Blog      : www.coldfusionjedi.com
AOL IM : cfjedimaster

Keep up to date with Android news: http://www.androidgator.com

[bin...@gmail.com]

Did you have one side set to inverse="true".  I have seen similar.

Sent from my Verizon Wireless Phone

--
You received this message because you are subscribed to the Google Groups "cf-orm-dev" group.
To post to this group, send email to cf-or...@googlegroups.com.
To unsubscribe from this group, send email to cf-orm-dev+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/cf-orm-dev?hl=en.

[Raymond Camden]

This is the other side:

property name="groups" fieldType="many-to-many"
cfc="root.model.group.group" linktable="group_member"
fkcolumn="useridfk" inversejoincolumn="groupidfk" lazy="true";

No inverse=true.

[Bob Silverberg]

I am pretty sure that that is the expected behaviour, which is one of the reasons you cannot store additional data in the join table for a many-to-many.

Sent from my iPhone

[Matt Quackenbush]

+1

[Brian Kotek]

Yes, that's the expected behavior.

[Raymond Camden]

Is there a good reason why? I mean shoot - I've got 800 members in my
group. Adding a user is horribly expensive then.

Should I switch to using vanilla SQL then?

[Dan Vega]

I would think the latter is more expensive no? 

- drop all add all members

- trying to to figure out which ones exists and adding the difference 

[Bob Silverberg]

Exactly. It's not doing 800 deletes and inserts, is it? If it's just
doing one delete and one insert then that is likely less processing
than calculating the difference, as Dan suggests.

--
Bob Silverberg
www.silverwareconsulting.com

[Raymond Camden]

No, it's doing one delete and 800 inserts. I'm serious. I couldn't
believe it when I saw it myself.

[Glen Dunlop]

I am going to have to agree Ray, I am still trying to understand why it
deletes them and rebuilds them too.

My logic would be that the values would only get changed if one was to
change the primary key, surely it would be far better to do an update than
do a delete, and rebuild it.

Is this a hibernate problem or a ColdFusion problem?

[Sam Farmer]

From reading your property definitions it looks like you have inverse=false on both sides. Have you tried adding inverse=true on one side? On the group side I _think_.

Cheers,

Sam

[Bob Silverberg]

It is definitely Hibernate and not CF. Good point about the inserts though. My bad thinking that could be done in one insert.

Sent from my iPhone

[Raymond Camden]

It didn't seem to work. I'll be honest and say I'm not sure what that
is supposed to do, but when I made the change, users were not able to
be added. I didn't get an error, but they weren't added. The SQL isn't
firing 800+ inserts, it's doing none.

[Raymond Camden]

Ok, read up on inverse in the docs. I have to be honest and say I
don't get it. Why would you NOT want to run SQL if you edit one or the
other side. I can't imagine doing someob.setSomething() and not have
it persist.

[Brian Kotek]

The whole point of inverse is that you're designating one side as the side responsible for maintaining the relationship.

[Brian Kotek]

Isn't that a problem with your model, then? When would you actually want to load up a collection with 800+ associated objects? Wouldn't it be better to load up the user you're changing, and add or remove the group from the user, than loading up the group, forcing a load of all 800 associated users in the collection, and then modifying the collection from the group side?

[Gavin Beau Baumanis]

Hi Ray / Brian,

(the following is from Java Persistence with Hibernate.

 * Awesome book by the way. 

    I find it really useful - all the examples are in Java, which I don't know - but can understand the book none the less)

The inverse attribute tells Hibernate that the collection is a mirror image of the <many-to-one> association on the other side:

Without the inverse attribute, Hibernate tries to execute two different SQL state- ments, both updating the same foreign key column, when you manipulate the link between two instances. By specifying inverse="true", you explicitly tell Hibernate which end of the link it should not synchronize with the database.

So in Brian's example you allow hibernate to persist data associated via the user-side of the relationship only.

which makes sense.

user.addgroup(); - seems normal to me. where as I can't see myself ever using;

group.addUser();  - I have certainly never had the need to do things "that" side of the relationship. One way seems natural to me - the other not so.

It is "always" (for me) on the <one-to-many> side, where I set the inverse attribute.

And, as a safe-guard - if I do I ever set a value via this method - then I have probably made a mistake - so I can tell hibernate to ignore this change via the inverse attribute.

Ensuring that all my group associations are made via the user object only.

I hoped I have helped some - and if not - then perhaps you can just take it as "gospel" for now - until such time as it does click for you?

[Bob Silverberg]

This brings up something that many people get confused by:

If you have a bi-directional relationship you should always set *both*
sides of the relationship whenever you want to persist it. In the
example of User and Group that means you should call user.addGroup()
and group.addUser(). This keeps your model in a consistent state.
Much as it seems counter-intuitive (to me anyway), Hibernate does not
do that for you automatically, and you can run into problems if you
don't follow that rule. One upside of following that rule is that
you'll never have issues with a relationship not getting persisted
when inverse is set to true on one side (which is another important,
and often missed rule).

There have been a number of blog posts written about this. I wrote a
few and I believe that Brian and Barney have written some as well.

I don't believe that any of this will address the issue of the 800
inserts, though. This is just additional information.

Bob

--
Bob Silverberg
www.silverwareconsulting.com

[Raymond Camden]

Brian - I see your point - but at no point would I have guessed that I
was doing that. To me, it was group.addUser(). That's it. Not
group.setUsers(array here).

Are you saying if I had done user.addGroup() it would have done a
single insert? Even if not, users will never have that many groups so
it should perform a lot better.

[Raymond Camden]

On Sun, Feb 27, 2011 at 5:38 AM, Gavin Beau Baumanis
<be...@palcare.com.au> wrote:
> So in Brian's example you allow hibernate to persist data associated via the
> user-side of the relationship only.
> which makes sense.
> user.addgroup(); - seems normal to me. where as I can't see myself ever
> using;
> group.addUser();  - I have certainly never had the need to do things "that"
> side of the relationship. One way seems natural to me - the other not so.

In my case, groups can add members and users can add groups, so
"natural" is certainly up to debate. ;)

> It is "always" (for me) on the <one-to-many> side, where I set the inverse
> attribute.

That makes sense - but this is m2m.

> I hoped I have helped some - and if not - then perhaps you can just take it
> as "gospel" for now - until such time as it does click for you?

Yeah, I'm going to try.

[Raymond Camden]

This seems to be working well.

In group.cfc I added inverse=true to the property.

In my groupService I now do:

group.addMember(user);
user.addGroup(group);

In the MySQL log I see much better SQL being used.

As a side question, groups also have Admins and Moderators, which are
also m2m, but since users don't join them per se, I've only defined it
on one side. There is no user.adminGroups or user.moderatorGroups.. Is
that an issue I should correct? I never ask a user for the groups he
admins. But I did end up adding a getGroups method like so: (notice
there is also a 4th way to be associated with a group - being it's
owner)

public array function getGroups() {
var hql = "from group g where exists(from g.members m where m.id =
:myid) or exists(from g.moderators m2 where m2.id = :myid) or
exists(from g.admins m3 where m3.id = :myid) or (g.owner.id = :myid)
order by name asc";
var res = ormExecuteQuery(hql, {myid=variables.id});
return res;
}

--

[Robert Rawlins]

> group.addMember(user);
> user.addGroup(group);

I find this all really interesting, having to call and establish the relationship on both sides like that seems so counter intuitive, twice as much code and you would imagine you would need.

Which of these do you then have to pass to the save()? Both of them? Either?

Robert

Sent from my iPhone

[John Whish]

It is confusing if you think of the relationship at database level (which I am often guilty of). If you think about the objects in memory and oop design then it is perfectly reasonable for one object not to know about a relationship, unless you tell it.

-- sent by a little green robot

On 27 Feb 2011 16:57, "Robert Rawlins" <rob...@supporttime.com> wrote:

[Brian Kotek]

The real point I'm making is that you probably shouldn't even HAVE a relationship from Group and User, only from User to Group. Because any time you do anything with the collection of Users from within the Group, you're forcing Hibernate to populate the entire collection. You said you have about 800 Users in a Group. What if that was 80,000 Users? 800,000 Users?!

Obviously it's not hard to see that this would get really bad really quickly. Instead, manipulate the User's groups from the User side.

The common response at this point is "If group has no User collection, what if I want to show a list of users in a group?" The answer is to use HQL to load the Users for a Group if and when you actually need them (or, more likely, a paged subset of them):

FROM User u JOIN u.groups g WHERE g.id = :groupId

Hope that makes sense. :-)

 

--