Hibernate version: 3.0 alpha (CVS)

Name and version of the database you are using: PostgreSQL 8.0

I want to map multiple subclasses to a single table. The problem is that the method for deriving the discriminator value can involve some fairly complex SQL. Here are a few things I looked into but I run into roadblocks in each of them (most likely because of my limited understanding of Hibernate).

a) I looked into the ‘formula’ attribute of the <discriminator> element. The problem is that some of my discriminator queries involve joining other tables (yes, there are some issues with the data model). I believe this precludes me from utilizing this function. Correct?

b) I think my preferred approach anyway would be to create a database-side view of this table to create a simple discriminator column and map to that using Hibernate’s discriminator column functionality. So in essence what I had in mind was to use the base table for writing and use the view (base table + discriminator column) just for retrieval. So then it looked like the <loader> and <sql-query> mapping elements were just what I needed. After messing around with that a bit, I discovered that Hibernate seems to only utilize the customized SQL loader query when using session.load(). I’m a total newbie, so please bear with me. If that is true, doesn’t that mean this functionality is only useful if you’re loading a single object by its ID? If I want to query this dataset using HQL to retrieve multiple objects (session.list()), then this custom loader is ignored? Based on my understanding, this would be almost completely useless in real world application, but I’m sure that’s NOT the case. So could somebody please clear this up for me?

I might be missing the obvious solution. Any other thoughts?

Thanks!

a) You should be able to do all kinds of crazy subselects in the formula attribute. Just take a look at how the generated SQL looks like.

b) Of course loader and sql-query are only used for loading by id. How should Hibernate ever figure out how to combine the custom SQL you define and a arbitarily complex HQL query. Just use createSqlQuery for querying.

Thanks for your reply.

a) Ok, I guess that was SQL ignorance on my part. I didn't know you could do subqueries within a case/when statement. That may work.

b) Yeah, I didn't know *how* it would be done, but just wishful thinking I guess. If I understand you correctly, you are saying that by using a custom loader on a class, I'm pretty much forfeiting the query retrieval benefits I get from mapping all of my associations because I'll have to re-define them in each SQL statement again... in other words, no HQL. Do a lot of people do this? I ask because, for me, one of the big benefits of an ORM is making use of the predefined associations in the mapping. I'm not questioning the usefulness of this feature for other people, I'm just genuinely curious if I'm unique or just looking at it the wrong way...

So it looks like option (a) may work for me (the discriminator formula)...
But if anybody knows of an approach where I can use a view of my base table for creating a discriminator column without sacrificing functionality, I would be very grateful.

Thanks!