limit HQL rows with top 1 or limit

andrews · May 3, 2015, 9:28pm

I have a need for something that I think can’t be done with HQL, at least my searching shows nothing so far. The following query errors as the sub select returns more rows than it should.

from Category
where (catId = (
from Category
where type = 1 and parent is null) )
or (type = 0 and parent is null)

From what I can tell, I may need to convert this using criteria builder, but thought I would ask if this is at all possible and if not, maybe someone can help convert it a criteria builder or at least get me started with the criteria builder.

The SQL query that works is this

SELECT *
FROM Category
WHERE (catId =
(SELECT TOP (1) catId
FROM Category
WHERE (type = 1) AND (parentId IS NULL))) OR
(type = 0) AND (parentId IS NULL)

andrews · May 4, 2015, 2:45am

Ok here is what I have so far, but the issue remains the same with criteria builder as well.

var c = forumService.newCriteria();
var result =
c.add(

c.createSubcriteria( “fbCategory”, “cat” )
.withProjections( property=“categoryId” )
.propertyEq( “categoryId” )
.maxResults(1)

)
.list( );

The error is, is this a limitation on ORM or is this a problem with criteria builder itself?

Application Execution ExceptionError Type: Object : [N/A]

Error Messages: The maxResults method was not found.
Either there are no methods with the specified method name and argument types or the maxResults method is overloaded with argument types that ColdFusion cannot decipher reliably. ColdFusion found 0 methods that match the provided arguments. If this is a Java object and you verified that the method exists, use the javacast function to reduce ambiguity.

andrews · May 4, 2015, 7:58am

bump anyone?

andrews · May 6, 2015, 12:08am

I could still use some help here!

Tim_Brown · May 6, 2015, 3:41am

I preface this with that I know little, if anything about criteria builder. However, I don’t see a method of .propertyEq() in the documentation listed here:
http://wiki.coldbox.org/wiki/Extras:CriteriaBuilder.cfm#Criterias

I guess could be some fancy onMissingmethod() magic, but like CF seems to like to do on occasion, it could be reporting one error that really was caused by the fact that the previously chained method didn’t return what it was supposed to… Just a stab in the dark.

var c = forumService.newCriteria();
var result =
c.add(

c.createSubcriteria( “fbCategory”, “cat” )
.withProjections( property=“categoryId” )
.propertyEq( “categoryId” )
.maxResults(1)

)
.list( );

andrews · May 6, 2015, 5:53am

Hey Tim,

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.

SELECT *
FROM Category
WHERE (catId =
(SELECT TOP (1) catId
FROM Category
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”);
q1.SetMaxResults(100);

IQuery q2 = session.CreateQuery(“select colone, coltwo from table2 where table1id in (:subselect)”);

q2.SetParameterList(“subselect”, q1.List());

Which so far I have, yeah it is ugly code at the moment.

var c = forumService.newCriteria();
var result =
c.and(

c.restrictions.eq(“type”, javaCast(“int”, 1)),
c.restrictions.isNull(“parent”)
)
.order(“left”)
.maxResults(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);

query.setParameterList(“idlist”, c.list());
var result1 = query.list();

Which errors on the setParameterList() with some message that I don’t have handy right now.

andrews · May 6, 2015, 5:44pm

Ok, I see where the issue is now. It’s a limitation on how Hibernate is building the query.

From what I can tell by the below link is that it could be done, by getting the session. Luis, Joel anyone have an idea if we could do this in ColdBox now or is this something that might need to be added?

I am impressed by the limitBy added to criteron in here as well.

http://stackoverflow.com/questions/11605862/hibernate-how-to-set-max-result-in-detachedcriteria