CODEWITHSUNDEEP
ormcoldfusion
Seybsen
Seybsen

Reputation: 15572

ColdFusion ORM paging with cfquery

Is it possible to limit the results (for paging) given by a <cfquery dbtype="hql"> like Raymond Camden demonstrated here with EntityLoad?

<cfset items = entityLoad("person", {},url.sort & " " & url.sortdir, {maxresults=pageSize,offset=url.start-1})>

In my case the objects could not be requestet with a simple entityload() because there are a few things I need to check:

<CFQUERY name="qEvents" dbtype="hql">
FROM events e
LEFT JOIN e.event_cat_id AS cat
LEFT JOIN cat.lang_id AS lang
WHERE lang.lang_id = <CFQUERYPARAM value="#session.lang_id()#" />
AND e.active = 1
AND (
        (
            DATE(e.date_begin) >= <CFQUERYPARAM value="#somedate#" cfsqltype="cf_sql_date" />
            AND
            DATE(e.date_begin) <= <CFQUERYPARAM value="#somedate#" cfsqltype="cf_sql_date" />
        ) 
        OR
        (
            DATE(e.date_end) >= <CFQUERYPARAM value="#somedate#" cfsqltype="cf_sql_date" />
            AND
            DATE(e.date_end) <= <CFQUERYPARAM value="#somedate#" cfsqltype="cf_sql_date" />
        )
        OR
        (
            DATE(e.date_begin) >= <CFQUERYPARAM value="#somedate#" cfsqltype="cf_sql_date" />
            AND
            DATE(e.date_end) <= <CFQUERYPARAM value="#somedate#" cfsqltype="cf_sql_date" />
        ) 
        OR
        (
            DATE(e.date_begin) <= <CFQUERYPARAM value="#somedate#" cfsqltype="cf_sql_date" />
            AND
            DATE(e.date_end) >= <CFQUERYPARAM value="#somedate#" cfsqltype="cf_sql_date" />
        )
    )
GROUP BY e.event_id
ORDER BY e.date_begin
</CFQUERY>

I know there is a maxrow-attribute for <cfquery> but that's not exactly what I need.

Upvotes: 3

Views: 637

Answers (1)

Sam Farmer
Sam Farmer

Reputation: 4118

Use the ormoptions attribute

<cfquery name="" dbtype="hql" ormoptions="#{maxresults=pageSize,offset=url.start-1}#"

Upvotes: 5

Related Questions