Hey

This is just a request-for-comments post, I'd be glad to hear any input on this.

I've got an EntityMetdata bean which may be owned by six other types of entities. None of the 6 types of entities may share a metadata; if an entity owns an EntityMetadata, then that EntityMetadata is unique to that entity.

I want to write a finder method that returns all EntityMetadata entities that are not owned by any other entities. That is my problem.

Simple enough -- with JDBC I can just left join each of the 6 entity tables to the EntityMetadata table and check where the entityMetadataId is null to see where these relationships do not exist.

However, these relationshiops are defined in CMR as one way only. This really inhibits the EJB-QL I can write, as I cannot arbitrarily join tables using EJB-QL.

I wrote this EJB-QL, but it takes over 5 minutes to execute (versus 10 seconds or so for the JDBC equivalent):

 * select distinct object(m)
 * from
 * EntityMetadata AS m,
 * Block as block,
 * File as file,
 * Folder as folder,
 * Page as page,
 * Stylesheet as stylesheet,
 * Template as template,
 * Symlink as symlink
 * where
 * not block.metadata = m and
 * not file.metadata = m and
 * not folder.metadata = m and
 * not page.metadata = m and
 * not stylesheet.metadata = m and
 * not template.metadata = m and
 * not symlink.metadata = m

I'm thinking that I will have to make this relationship bidirectional so that I do not have to join the tables together like this.

Any thoughts?