propertyEq is in the subquery criterion, but that is not the issue. If I leave .maxResults() in that position it is not know otherwise that section works. But that is only part of the problem.
I have data that is in need of a subquery going back to the orginal post.
WHERE (catId =
(SELECT TOP (1) catId
WHERE (type = 1) AND (parentId IS NULL))) OR
(type = 0) AND (parentId IS NULL)
In SQL the top one here does what it is supposed to do, but in HQL the top / limit etc is not supported. I thought the CriteriaBuilder might be a way to get around this limitation. But so far I am not getting a handle on how to get this query to work. I did come across a post that describes another technique which on the surface may work, just can’t seem to get it to work in ColdFusion.
The snippet I found is
IQuery q1 = session.CreateQuery(“select id from table1 order by id desc”);
IQuery q2 = session.CreateQuery(“select colone, coltwo from table2 where table1id in (:subselect)”);
Which so far I have, yeah it is ugly code at the moment.
var c = forumService.newCriteria();
var result =
c.restrictions.eq(“type”, javaCast(“int”, 1)),
var q = “FROM Category as cat where (type = 0 and parentId is null) or (catId =:idlist)”;
q &= " ORDER BY cat.left";
var query = orm.getSession(orm.getEntityDatasource(‘Category’)).createQuery(q);
var result1 = query.list();
Which errors on the setParameterList() with some message that I don’t have handy right now.