ColdFusion ORM paging with cfquery
Is it possible to limit the results (for paging) given by a <cfquery dbtype="hql">
, such as Raymond Camden demonstrated here with EntityLoad
?
<cfset items = entityLoad("person", {},url.sort & " " & url.sortdir, {maxresults=pageSize,offset=url.start-1})>
In my case, objects couldn't be queried with a simple entityload()
one because there are several 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 an attribute maxrow
for <cfquery>
, but that is not exactly what I need.
+3
source to share