limit HQL rows with top 1 or limit

6,195 views
Skip to first unread message

Andrew Scott

unread,
May 3, 2015, 5:29:03 PM5/3/15
to col...@googlegroups.com
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)



Regards,
Andrew Scott
WebSite: http://www.andyscott.id.au/

Andrew Scott

unread,
May 3, 2015, 10:45:51 PM5/3/15
to col...@googlegroups.com
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 Exception

Error 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.



Regards,
Andrew Scott
WebSite: http://www.andyscott.id.au/


Andrew Scott

unread,
May 4, 2015, 3:59:26 AM5/4/15
to col...@googlegroups.com
bump anyone?

Regards,
Andrew Scott
WebSite: http://www.andyscott.id.au/


Andrew Scott

unread,
May 5, 2015, 8:08:48 PM5/5/15
to col...@googlegroups.com
I could still use some help here!

Regards,
Andrew Scott
WebSite: http://www.andyscott.id.au/


Tim Brown

unread,
May 5, 2015, 11:42:01 PM5/5/15
to col...@googlegroups.com
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( );


--
--
You received this message because you are subscribed to the Google Groups "ColdBox Platform" group.
For News, visit http://blog.coldbox.org
For Documentation, visit http://wiki.coldbox.org
For Bug Reports, visit https://ortussolutions.atlassian.net/browse/COLDBOX
---
You received this message because you are subscribed to the Google Groups "ColdBox Platform" group.
To unsubscribe from this group and stop receiving emails from it, send an email to coldbox+u...@googlegroups.com.
To post to this group, send email to col...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/coldbox/CACLZGDknAPX_WLHj77DHA%2BcKAoS2TmtFw7Xb6num%3DhpT-SdVZg%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

Andrew Scott

unread,
May 6, 2015, 1:53:47 AM5/6/15
to col...@googlegroups.com
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.




Regards,
Andrew Scott
WebSite: http://www.andyscott.id.au/


To view this discussion on the web visit https://groups.google.com/d/msgid/coldbox/CANPdUebbWvbq2eTKWVAh34v_kta1n9kBYDCR-EWi7PE-PW1Lig%40mail.gmail.com.

Andrew Scott

unread,
May 6, 2015, 1:45:17 PM5/6/15
to col...@googlegroups.com
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



Regards,
Andrew Scott
WebSite: http://www.andyscott.id.au/


Luis Majano

unread,
May 6, 2015, 2:22:35 PM5/6/15
to col...@googlegroups.com, col...@googlegroups.com
Is the limit By criterion something custom?

Luis Majano

unread,
May 6, 2015, 2:25:51 PM5/6/15
to col...@googlegroups.com, col...@googlegroups.com
If it is custom, you could technically build the same LimitBy Criterion in CF via the dynamic Proxy call.

createDynamicProxy( LimitCFC, “org.hibernate.criterion.Criterion” )



Andrew Scott

unread,
May 6, 2015, 2:32:23 PM5/6/15
to col...@googlegroups.com
Hmm, that looks interesting but not sure how I would create that CFC though.

Regards,
Andrew Scott
WebSite: http://www.andyscott.id.au/


Andrew Scott

unread,
May 6, 2015, 2:35:53 PM5/6/15
to col...@googlegroups.com
Yes, it implements the criterion.

Regards,
Andrew Scott
WebSite: http://www.andyscott.id.au/


To view this discussion on the web visit https://groups.google.com/d/msgid/coldbox/1430936553173.5e2c5c2c%40Nodemailer.
Reply all
Reply to author
Forward
0 new messages