I'm having trouble figuring out how to use the @Where annotation to limit the objects
in an associated collection.

My sample application has Users and Contacts. A User contains basic user information
(username, real name, password, etc) and a list of Contacts. A Contact contains the
contact's basic information (username, address, etc) and a 'deleted' flag. I want to use
some mechanism (@Where seems a logical choice) to limit the objects in the User's
contacts list to just those which are not deleted. (Since the basic User information is
the same as the basic Contact inforomation, User simply contains an instance of Contact
rather than repeating name, address, etc... This isn't the best design but it works well
enough for the simple sample I'm trying to create.)

The classes are:

@Entity(access = AccessType.FIELD)
@Table(name = "con_user")
public class User extends PersistentObject
{
@ManyToOne(cascade = { CascadeType.ALL })
@JoinColumn(name = "contact_oid")
private Contact contact;

private String password;

@Column(name = "is_admin")
private Boolean admin;

@ManyToMany(
cascade = { CascadeType.PERSIST, CascadeType.MERGE },
fetch = FetchType.LAZY
)
@JoinTable(
table = @Table(name = "con_user_contact"),
joinColumns = { @JoinColumn(name = "user_oid") },
inverseJoinColumns = { @JoinColumn(name = "contact_oid") }
)
@BatchSize(size = 20)
@Where(clause="eachContactsCollectionItem deleted attribute is 'false'")
private Collection<Contact> contacts;

...
}


@Entity(access = AccessType.FIELD)
@Table(name = "con_contact")
public class Contact extends PersistentObject
{
@Column(name = "first_name")
private String firstName;

@Column(name = "last_name")
private String lastName;

private String email;

private String phone;

@Column(name = "is_deleted")
private boolean deleted = false;

...
}


If I use:
@Where(clause="deleted = '1'")

the generated SQL is:
select contacts0_.user_oid as user1_1_, ... contact1_.is_deleted as is9_0_0_
from con_user_contact contacts0_
left outer join con_contact contact1_ on contacts0_.contact_oid=contact1_.oid
where contacts0_.deleted = '1' and contacts0_.user_oid=?

That is, it thinks 'deleted' is an attribute of the join table between User and Contact.

If I use:
@Where(clause="contacts.deleted = '1'")

it treats "contacts.deleted" as a literal and generates:
...
where contacts.deleted = '1' and contacts0_.user_oid=?

I've tried various combinations of things but can't find the right formula.


I am using Hibernate 3.1 and Hibernate Annotations 3.1beta7.


Any suggestions would be greatly appreciated.
Thanks,
James

@Where is pure SQL so you can always do some operation on the target table

But i personnaly would use a session.createFilter() to return only the non deleted contacts

Hi Emmanuel,

Isn't a session.createFilter() going to apply to any fetch of Contact instances in the current session? If I have another fetch-for-contacts in the same session I don't want it to exclude deleted contacts. I only want to narrow the collection in the lazy association fetch.

BTW: As you might guess, this is a very simplified and not very real-world application. I'm trying to create a sample app full of common (and perhaps not-so-common) scenarios for others here to lean on.

Thanks,
James

createFilter() applies to a given collection (check the API), that's a different feature than the Hibernate Filters actually (well the second is the generalization of the first)

Hi Emmanuel,

I've found:

http://www.hibernate.org/hib_docs/v3/ap ... ang.String)
and
hibernate-3.1/test/org/hibernate/test/legacy/MasterDetailTest.java

If I'm reading that correctly, session.createFilter() seems to apply to the collection after the objects have been fetched from the database.

I'm not sure how I would hook that up to the lazily-fetched collection such that user.getContacts() returns only the non-deleted items and even if I figure that out, I would rather filter at the SQL layer. In the real-world scenario there might be thousands of records marked as deleted (or archived or expired or whatever) and relatively few that are "live". I wouldn't want to pay the cost to fetch a pile of objects into the JVM just to have them filtered out.

(Thanks for being patient. I've been doing Hibernate for years and never had a need to touch filtering until recently.)

Thanks,
James

createFilter does not apply the filters in memory. They are applied at the DB level. In other words, the original collection is not touched

Ok, so if I understand you correctly the sequence of events goes like this:

1) Load a User object which does an SQL fetch from con_user and con_contact

2) Invoke user.getContacts() which fetches the user's contacts via con_user_contact and con_contact

3) Invoke session.createFilter(user.getContacts(), ...) which gives me a means to fetch a new, narrowed list of contacts.

Is that correct?

My real goal, however, is for user.getContacts() to return the already-narrowed list. e.g. -- the list of "active" contacts. That is why I was looking at @Where initially.

Am I going about this entirely backward?

Thanks again for your patience!
James

2 is not necessary.

If you want 'transparency' then hibernate filters might be what you want (@Filter)

The alternative solution would be to use a SQL view to hide this filtering rule