Hi,

I have a entity with some properties like title and description and a many-to-many association with keywords. I want to do a criteria that returns all rows where 'xyz' is in the title or the description or 'xyz' is a keyword. I can easily do 'and' as below but not 'or'.. Ideas?

Keyword keyword = keywordDAO.findKeywordByName(token,
session);
if (keyword != null) {
Criteria kwCriteria = crit
.createCriteria("keywords");
kwCriteria.add(Restrictions.idEq(keyword
.getKeywordId()));
}

LogicalExpression tokenExpression = Restrictions.or(
Restrictions.like("title", token, MatchMode.ANYWHERE),
Restrictions.like("alternativeTitle", token, MatchMode.ANYWHERE));
tokenExpression = Restrictions.or(tokenExpression,
Restrictions.like("description", token, MatchMode.ANYWHERE));
crit.add(tokenExpression);

Regards,
Damon.

Hi

kwCriteria.add(Expression.like("some value", Obj));

I think you can find it using Expression. Try to create a if loop and place the or condition there.

Sorry, I'm not sure I follow. How would I use a like expression in this case?

Right now I have..

Criteria crit = session.createCriteria(AdamAsset.class);
Criteria kwCriteria = crit.createCriteria("keywords");

Are you suggesting that I can drop the kwCriteria and instead find the association directly from the main crit. eg.

crit.add(Expression.like("xyz", keywords.Keyword.name))

I can't see anything in the docs that suggests I can use Expressions against sets in this way (but it probably would solve my problem if it was).

The other alternative would be some way of saying that the new kwCriteria should be 'or'ed rather than 'and'ed.

Criteria crit = session.createCriteria(AdamAsset.class);
Criteria kwCriteria = crit.createCriteria("keywords", "or");

Cheers
Damon.

hi

Yes

crit.add(Expression.like("xyz", keywords.Keyword.name))


get it from the main criteria.

Thanks,
That got me looking in the right direction.. Turns out the exact suggestion doesnt seem to work. Tells me it can't find the property 'keywords.keywordName'.

crit.add(Expression.like("xyz", keywords.keywordName))

However I can use a sql restriction as follows (linking on id not name).. Hacky but it works..

Restrictions.sqlRestriction("(keywords6_.keyword_id = "
+ keyword
.getKeywordId()
+ " and keywords6_.asset_id = {alias}.asset_id)"))

Also I have to somehow convince the Criteria to add the keyword6_ table to the join.. Hibernate will add keyword6_ to the join if I go..

Criteria kwCriteria = crit.createCriteria("keywords");

The catch is that I need to hardcode keywords6_ into the code because I can't find a way to get the alias of the junction table. (keywords6_ is the middle of a many to many - can I change this?).

And also Hibernate creates an inner join instead of a left outer join so a keyword must be associated or no result (I presume this is due to it being many-to-many).

But it mostly works. ;-)

Damon.

Criteria.createAlias() is what you are looking for.

yourCritera.createAlias("yourAssociation", "aliasYouWant")

yourCriteria.add(Restrictions.eq("aliasYouWant.somePropertyOnTheAssociation", someValue);

I wouldn't count on using the aliases that Hibernate generates.

Great thanks! With the createAlias things are much tidier now.. Two issues are still causing me pain though.. Any idea on those? ;-)


// 1. I want there to be a distinct in the select or else I get loads
// of duplicates back. I know I can use a projection here but then I
// don't end up with an object, rather I get a bunch of properties
// and will have to query again for each page of results.
select

{snip}


from adam_asset this_
left outer join adam_retained_copy adamretain3_ on this_.master_copy=adamretain3_.retained_copy_id
left outer join adam_collection adamcollec4_ on adamretain3_.collection_id=adamcollec4_.collection_id
left outer join adam_agreed_usage adamagreed5_ on this_.asset_id=adamagreed5_.asset_id


// 2. Ideally I want this to be an left outer join to adam_keyword because
// otherwise I never get back assets that have no associated keywords
inner join adam_keyword keywords6_ on this_.asset_id=keywords6_.asset_id
inner join keyword kw1_ on keywords6_.keyword_id=kw1_.keyword_id

where

(((((this_.title like ? or this_.alternative_title like ?) or this_.description like ?) or this_.copyright_credit like ?) or this_.copyright_notice like ?) or kw1_.keyword_id=?)

and

(((((this_.title like ? or this_.alternative_title like ?) or this_.description like ?) or this_.copyright_credit like ?) or this_.copyright_notice like ?) or kw1_.keyword_id=?)

order by this_.asset_id desc

You're pretty much out of luck with the outer join.

As for the distinct, I was thinking about it from the other thread that just got recently resurrected.

Have you tried a DetachedCriteria as a subquery, then essentially build up your main query where your id is in select distinct(id) from your table. That should at least eliminate your duplicates.

I would think if they incorporated something into the framework it would have to do something similar as if you're using outer join fetching then each parent, child fetch may generate multiple returns of the same parent.

Not quite sure I follow on the distinct.. Do you mean..

DetachedCriteria ids = DetachedCriteria.forClass(AdamAsset.class)
.setProjection(Projections.distinct(Projections
.property("assetId")));
crit.add(Subqueries.in("assetId", ids));

This generates an additional clause as below..

where
? in (select distinct this0__.asset_id as y0_ from adam_asset this0__)

Which gives a class cast exception.

As far as I know there should be no problems returning unique results.. I have been doing this in HQL for months, I just thought I would move to Criteria when I upgraded to H3. In HQL I piece the query together as follows..

String dataSelect = "select distinct new AssetSummary";
dataSelect += "(a.assetId, a.title, a.description, a.copyrightCredit, a.archiveMaster.adamCollection.name, a.archiveMaster.filePathName, aau.generalUseCategory, aau.sensitivityIssues) ";
String countSelect = "select count(distinct a.assetId) ";

This worked nicely.. But of course the string manipulation was a bit nasty. :-(


Damon.

DetachedCriteria ids = DetachedCriteria.forClass(AdamAsset.class)
.setProjection(Projections.distinct(Projections
.property("assetId")));
crit.add(Subqueries.PropertyIn("assetId", ids));

in will do your whole object which is why you're getting your class cast, it took me a while (and some prodding) to realize that.

As for the distinct in general, Gavin had commented at one point that it would be trivial to add but to my knowledge nothing was done with it. I might have to dig into the HQL parser so see what it does with the distinct keyword.

Hi,

Well I have given up on implementing pagination using setMaxResults and setFirstResults due to not being able to return a distinct set using the Criteria API. I tried the propertyIn approach outlined above but the SQL generated was invalid for some reason.

Here is what I have now done. My intention is too combine this approach with a second level Query and Data cache to improve performance if required. We currently have around 5000 records in our database and returning all 5000 ids (so getTotal will be accurate) seems to have reasonable performance. We will see if the solution scales. ;-)


Issue 1. A distinct, paged resultset.

// Set a distinct projection
Criteria crit = session.createCriteria(AdamAsset.class);
crit.setProjection(Projections.distinct(Projections
.property("assetId")));

// Specify restrictions

// List all ids in the resultset and the total
List results = crit.list();
Integer iTotal = new Integer(results.size());

// Construct a page of results
List results_data = new ArrayList();
Iterator it = results.iterator();
int start = pageNum * pageSize;
int end = start + pageSize + 1;
int i = 0;
while (it.hasNext()) {

Integer assetId = (Integer) it.next();

if (i >= start) {
AdamAsset asset = this.getAdamAsset(assetId);

results_data.add(asset);
}
i++;
if (i >= end)
break;
}

Issue 2. Or'ing a child table into Criteria restrictions.

Vampboy's pointer on using the 'in' operator helped me to arrive at this solution to 'or' between fields in a parent table and a field in its child table.

tokenExpression = Restrictions
.or(
tokenExpression,
Restrictions
.sqlRestriction("this_.asset_id in (select asset_id from adam_keyword where keyword_id = 506)"));

All is now well in the world. ;-)

Regards,
Damon.