I am trying to get the "where" attribute of the class descriptor in the mapping file to work in Hibernate2. I added this to my class descriptor:



I turned on SQL display and watched as I tried to select on the material table. No dice. The where clause didn't show up.

I removed my mapping file, to see if it was working on the right one, and it barfed, as it should have.

I changed the where clause to "allyourbasebelongtous" to see if it would barf on the bad SQL, but nothing happened.

I restarted the JBoss server each time.

Am I doing something wrong? Does this feature need to be "turned on", or is it not yet implemented?

Thanks,
Ken

Which *exact* version of Hibernate are you using?


And note that this is only used in a *query*, not in load(), for example.

I believe it's 2.0.2 - I downloaded it two weeks ago, and it was the newest version available at that time that wasn't a beta release.

The situation in which I'm using it is a many-to-many relationship. There is a bundle object which is related to a set of materials, through a bundle_materials correlation table. When I iterate through the collection of materials, I would expect the where clause to be executed on them. Is it perhaps calling a load() on each individual item in the collection, therefore not invoking the where clause?

Thanks,
Ken

The where clause is not used at all during association navigation. I suspect you might be able to argue that it should be used for navigating many-to-many associations. If you think so, sibmit a feature request to JIRA and I'll give it some thought.

And is there any possibility to define where-constraints for <many-to-one> association? Maybe with the help of <filter> ?

The constraint for a many-to-one is called "foreign key". Everything else wouldn't be a many-to-one.

Could you please give a hint for such a task:
- I have articles, each article belongs exactly to one category
- Each category has a flag "hidden", which means, it is not accessable
- I want to list all accessable articles:

select a from Articles a join a.category c where c.hidden = false

I would agree with this statement, however having the ability to append arbitrary sql to all queries (including association and primary key queries) would still be useful as a mechanism for creating "dynamic views" on audit history tables.

The ability not just to save audit history but also to load historical versions into domain objects is a comon requirement in the world of derivatives trading systems. Every system I know of that has solved this problem uses a proprietary ORM layer, since standard solutions do not exist.

Although with Hibernate it is possible to use views and triggers to save audit history, as far as I am aware there is no way to load a version as of a particular date in history. However I believe it should be possible with some modifications.

The assumption is that the entities being versioned have both Version and LastUpdated properties, and that two parallel sets of tables exist in the database (one holding the latest version of each entity, and one holding all versions). The history tables are maintained via triggers on the primary tables.

To query on the history tables, the user would create a new SessionFactory with the desired as-of date. When configuring this factory each versioned entity is mapped to its history table, with some additional filter sql to select the last version as-of the specified date.

So if a normal query looks like:

select <...> from Foo f where f.Id=42

the corresponding history query would look like:

select <...> from Foo_history f where f.Id=42 inner join
(select Id, max(Version) as Version from Foo_history
where LastUpdated<='12/31/2004' group by Id) as fh
on f.Id=fh.Id and f.Version=fh.Version

It seems to me that anytime you step into the world of audit history, you end up going outside the bounds of strict relational algebra. However, in the real world (if financial derivatives can be considered the real world) the ability to extend the ORM mappings to audit history is an important requirement.

This stuff can be solved using Hibernate3 filters.

Interesting. I haven't looked much into Hibernate events as I am using NHibernate. If anybody has an example of using events in this way I would certainly be interested in learning more.

In any case, thanks for the response.

gavin, thanks for the hint, conc hibernate filters. Still I suffer difficulties with filtering many-to-one associated entities.

I have articles, each article belongs exactly to one category
- Each category has a flag "active", which means, it is not available
- I want to list all available articles
- I don't want the "active" field to be mapped

Mapping for article:

Mapping for catalog:

Code:

Resulting SQL:

I have also tried not to define the table name:

and to replace the table name with property name (catalog.active = 1), class name (CatalogImpl.active = 1) -- nothing helped (the table alias is not substituted).

Please, give a hint, how a filter can be properly defined.