Hi all

So we are constructing a query based on a user profile; for each attribute in the profile, we add criteria used to filter a search on another table.

the method that does this looks like




where each add method contains



everything was going splendid until I noticed under certain conditions, we want our where clause to look like

WHERE (overriding profile condition) OR (foo condition AND bar condition ...) ...

of course disjuction will put an OR on the WHERE clause, however, since we construct the query as we go thru the profile attributes, criteria added in the different methods are put at the "root" level of the where clause, not in the elements of the disjuction.

is there any way to overcome this?

edit

Ive made progress; the add methods now add to a conjuction.

the remaining problem is adding a DetachedCriteria to the conjunction.

sometimes we have to do a 3 way join because certain fields on which we search are normalized out of the main searchable table. before adding the disjunction we were



doesnt seem like anything like that is addable to a junction.

Help Please!!!

I've done the same thing. My solution was to subclass DetachedCriteria so that createAlias only created an alias if the alias didn't already exist. Once you have that enhancement, you can have your addXCriteria methods do pretty complex things.

For example, if you've a class Foo, containing associations to Bar class and Snafu class, your FooDTO class (or whatever factory-type class you're using) can contain this method:This code can't work with DetachedCriteria because you can't add the same alias twice, but with your subclass (which maintains a record of all aliases already added) it works fine. The same addWeirdCriteria method can be called many times by the API user, and other addXCriteria methods can add the same aliases so that the resulting query is correct.

you mean have our DTOs all extend DetachedCriteria? I think we want to avoid doing tricks like that.

recapping, before I read the part of the requirement that indicated we (might) need a disjunction for the query, based on the profile, I could add the join WHERE condition directly to a DetachedCriteria.

In implementing this next requirement, I can add Restrictions to a Disjunction, and add that Disjuntion to the main conjunction, if necessary. I cannot add the necessary DetachedCriteria to the Conjunction?...there must be some other way to do it.

thanx for the reply, btw

No, you just need a single ImprovedDetachedCriteria class that extends DetachedCriteria, and use that. The only deficiency in DetachedCriteria that you're trying to eliminate is that calling crit.createAlias("x", "y"); twice causes an exception. You (well, I) want it to simply do nothing.

Alternatively, you could have each DTO provide a method like createThisDTOsDetachedCriteria, which returns a DetachedCriteria with all necessary aliases set up. Once you have all the aliases you need for any and all of your addFooCriteria methods, they can all work correctly. The problem is that each createAlias will put another join into the resulting SQL: this is fine if the tables being joined are needed, but it's wasteful if there are tables that are joined but not used.

It's quite hard to explain this, but try this (on paper). Figure out a reasonably complicated sequence of addFooCriteria methods that might end up being called in your app. Expand that out to normal DetachedCriteria.add() calls. Optimize it by adding all appropriate createAliases. Then reverse the first step, converting all your DetachedCriteria.add() calls back to the correct addFooCriteria calls. At this point, you have several createAlias() calls sprinkled in between. Your choices are to move all those createAliases into a single method, the one that created the original DetachedCriteria; or to put the createAlias() call in each addFooCriteria method, so that you don't end up with unnecessary outer joins in your end result. But to do this second option, you need createAlias not to barf if you add the same alias twice.