Well... to get you going I'd suggest you add the foreign keys.

That means that Ebean will be able read the foreign key relationships between the user, role and user_role tables ... and you won't need to define @JoinTable at all.

... aka just have the @ManyToMany ... and Ebean will work out the joins etc based on the foreign keys.

That said, I'm thinking you may have hit a bug related to defining ManyToMany joins manually without any foreign keys... So I'll see if I can reproduce that.

Any reason for not having the foreign keys?

Thanks, Rob.

Oh, remember to delete the .dictionary file after changing the schema.

That is, Ebean reads the schema information and stores it in a .dictionary file for performance reasons as reading meta data from the DB can be slow.

... so deleting the file means that Ebean will rebuild it reading the DB meta data again.

Cheers, Rob.

.

Rob thanks for the quick responses.

Well... to get you going I'd suggest you add the foreign keys.
- I'm using the myisam table type, so no foreign keys.

Oh, remember to delete the .dictionary file after changing the schema.
- My test cases that I'm using to play around with Ebean automatically deletes the .dictionary files before each run.

Is the source tree in the source forge svn repository under ebean/trunk the current working tree? If so, I can see if I can figure out whats going on as well. So far the source code seems fairly easy to follow from what little digging around I've done.

Thanks for looking at it.

I've created a bug for this at http://www.avaje.org/bugdetail-84.html

Cool - just saw the bug - thanks.

Yes ebean/trunk ... is the current tree being worked on, so when I say fixed in HEAD ... I mean the code has been committed up into ebean/trunk.

Thanks, Rob.

Just as a thought... being a many to many ... there is generally a requirement to join from user -> user_role -> role.

The JoinTable on User ... defines the first join.. but not the second join from user_role -> role. From the test case... there was not a reverse relationship from role -> user_role ... so the issue could be around there (no definition of the relationship from role -> user_role). Thats my first hunch anyway...

if i may add to this:
@ManyToMany
/*@JoinTable(
name="event_group" ,
joinColumns =
@JoinColumn(name="event_id"),
inverseJoinColumns =
@JoinColumn(name="usergroup_id")
) */
private List groups;
.
.
.
Query q = Ebean.createQuery(Event.class);
q.join("groups");
q.where("groups.id=1");
final List eventList = q.findList();
.
.
.
select e.id, e.startdate, e.enddate, e.cdate, e.minpeople, e.maxpeople, e.comment, e.canceled, e.reason, e.deleted, e.archived, e.creator_id, e.category_id, e.location_id, g.id, g.name, g.description
from event e
left outer join event_group d on e.ID = d.event_id
left outer join usergroup g on e.usergroup_id = g.ID
where g.id=1

-------------------------------

the join is wrong.
left outer join usergroup g on e.usergroup_id = g.ID
should be
left outer join usergroup g on d.usergroup_id = g.ID

i'm using svn head from today.

regards,
-erwin

the haxor fix for this to let me continue working:
SqlTreeNodeBean.java:434
node.tableJoin.localTableAlias = manyToManyJoin.getForeignTableAlias();
(make the fields public and non-final).

and yes, i know that this is even uglier than it sounds when you first see it. but it makes waiting for a real fix easier ;)

keep it up,
-erwin

persisting this ManyToMany relationship tries to execute the following sql statement:

delete from EVENT_GROUP where ID is null and EVENT_ID=? and USERGROUP_ID=?

which throws an optimistic lock exception, because the ID is not (and cannot be) null.

btw: i do not have a seperate class for the table EVENT_GROUP.

as a working solution, i dropped the primary key from the assoc-table. depending on your POV, this might be considered better design anyway.

Ok. I have reproduced the first problem - my ManyToManyAuto test missed this scenario :( ... so I'll have a look at this tomorrow.

Yes, I'm aiming to get my tests cleaned up and published ... getting there.

Anyways, I'll have a detailed look tomorrow.

Cheers, Rob.

one more thing: it seems that ManyToMany associations are not saved on new Objects.

x = new Foo();
x.setMany(theList);
Ebean.save(x);

Ebean.getReference(Foo.class, x.getId()).getMany(); <--- empty

one more thing: it seems that ManyToMany associations are not saved on new Objects.

x = new Foo();
x.setMany(theList);
Ebean.save(x);

Ebean.getReference(Foo.class, x.getId()).getMany(); <--- empty

sorry, my fault. i messed up the creation of theList. this works as expected.

me again.

just as a btw: the following seems a bit non-intuitive. is there any other way of doing this?

public Set getGroups() {
if ( groups == null ) {
final BeanSet set = new BeanSet();
set.setModifyListening(true);
setGroups(set);
}
return groups;
}

this occurs when dealing with a new Foo() where i want to add groups before saving the object the first time.
the main issue i have with this is that new BeanSet() does not enable modify listening by default. it took me quite some time to figure that one out.

Hmm...

I think this is actually a bug.

AKA:
If you are inserting a new Foo (not update)
... then Ebean should treat every entry in the ManyToMany Set as a new association (and insert into the intersection table).

I'll have a little think, but I feel this is a bug in that once you know Foo is being inserted, anything in the ManyToMany *MUST* be a new association.

For an update of Foo, listening on BeanSet/List/Map detects the changes (adds/removes)... but for insert of Foo this should not be needed.

I'll check but yes, this looks like a bug to me.

Cheers, Rob.

while you're at it: why not accept any instance of List/Set/Map for vanilla inserts?
at the moment, you just get a warning that the collection does not implement BeanCollection. You could replace it on save with a BeanCollection. The only problem I see with this is, that if somebody kept a reference to the original collection, it will be out of date after the insert.

best,
-erwin

Logged bugs:
http://www.avaje.org/bugdetail-101.html
http://www.avaje.org/bugdetail-102.html

> why not accept any instance of List/Set/Map for vanilla inserts?
Yes. We need to do that - you don't want to use Ebeans BeanList/BeanSet or BeanMap so yes, I have done that.

> You could replace it on save with a BeanCollection
At the moment I think the best approach is to not do this. That is, it someone wants to later do an update Ebean will ignore any changes to the ManyToMany (and log the warning) .. so people should fetch that entity and update - rather than just resave the original instance.

thanks, i tested this in head, and it works fine.

but it seems i can't stop digging for new issues ;)
the current puzzle i'm trying to solve is how to join 3 tables (with one additional assoc. table).
user(id, login, ...)
subscription(id, user_id, type, ...)
group(id, name, ...)
user_group(user_id, group_id)

i want to fetch all subscriptions for all users of a certain group. is this possible? i tried the following:

Query<Subscription> find = Ebean.createQuery(Subscription.class);
find.join("user");
find.join("user.groups");
find.where("groups.id IN (1)");
find.findList();

just as a test, two tables work like this:
Query<User> find = Ebean.createQuery(User.class);
find.join("groups");
find.where("groups.id IN (1)");
System.out.println(find.findList().size());

solved it with @SqlSelect for the moment, and found this bug along the way (@SqlSelect with no where clause):
Caused by: java.lang.NullPointerException
at com.avaje.ebean.server.deploy.DeploySqlSelect.buildSql(DeploySqlSelect.java:198)
at com.avaje.ebean.server.query.RawSqlSelectClauseBuilder.build(RawSqlSelectClauseBuilder.java:88)
... 7 more

find.where("groups.id IN (1)");

Looks to me like that should be:

find.where("user.groups.id IN (1)");

... but yes, that may not include the many but I'll have a look.

solved it with @SqlSelect for the moment, and found this bug along the way (@SqlSelect with no where clause):
Caused by: java.lang.NullPointerException
at com.avaje.ebean.server.deploy.DeploySqlSelect.buildSql(DeploySqlSelect.java:198)
at com.avaje.ebean.server.query.RawSqlSelectClauseBuilder.build(RawSqlSelectClauseBuilder.java:88)
... 7 more

i tried user.groups.id too, but to no avail.
and i'm sorry that i'm starting to really bother you, but i have found another issue. JoinTables are ignored when trying to use the same intersection type twice. example:

@ManyToMany
@OrderBy("name")
@JoinTable(
name="user_group",
joinColumns = {
@JoinColumn(name="user_id")
},
inverseJoinColumns = {
@JoinColumn(name="usergroup_id")
}
)
Set<Usergroup> groups;

@ManyToMany
@OrderBy("name")
@JoinTable(
name="group_admin",
joinColumns = {
@JoinColumn(name="user_id")
},
inverseJoinColumns = {
@JoinColumn(name="usergroup_id")
}
)
Set<Usergroup> adminGroups;

this will throw:
SEVERE: ERROR COMING: Multiple Intersections [Source[USERGROUP] Dest[SYSTEMUSER] Intersection[GROUP_ADMIN]]
java.lang.ExceptionInInitializerError
at tk.eabin.event.server.ServerRunner$IdleWorker.run(ServerRunner.java:27)
at java.lang.Thread.run(Thread.java:619)
Caused by: javax.persistence.PersistenceException: java.sql.SQLException: Found [2] intersection tables between [usergroup] and [systemuser]? Only expecting one or none.

hm, this might be me misunderstanding the JPA.
i thought it was enough to define the JoinTable on one side, and only use "mappedBy" on the other side.
If I define the JoinTable on both sides, this works as expected. is this well define behaviour?

regards,
-erwin

please write a good test case for this situation, because it isn't 100% clean. e.g. on the inverse side (class Usergroup) i get the same set of results when calling getUsers() and getAdmins(). but saving them uses again the correct table. i think the result of get*() may depend on what was called first, and the second one will just reuse the same data.

just tested the above hypothesis, and it is true. the data of the first call is re-used for the second call.

i tracked this down to the following:
DefaultOrmQuery.calculateHash(QueryRequest) does not include table. i added:
hc = hc * 31 + (includeTableJoin == null ? 0 : includeTableJoin.getTable().hashCode());

and i'm fine again.