[Coldbox 6.0.0] [CBORM 2.5.0]

jcho...@gmail.com

unread,

Oct 30, 2020, 1:11:26 PM10/30/20

to ColdBox Platform

Hi,

Is it possible to use the SQL MONTH() and YEAR() functions?

I've been trying variations of this:

prc.SentSummary = EmailHistoryService.newCriteria()

.withProjections(property="MONTH(CreationDate):TheMonth,YEAR(CreationDate):TheYear", count="EmailHistoryID:NumberOfSentEmails", groupproperty = "TheMonth,TheYear")

.order("TheMonth")

.order("TheYear")

.asStruct()

.list();

With the error message: "could not resolve property: MONTH(CreationDate) of: EmailHistory"

The cfquery alternative is:

SELECT COUNT(EmailHistoryID) AS EmailsSent, MONTH(CreationDate) AS TheMonth, YEAR(CreationDate) AS TheYear

FROM tbl_EmailHistory

GROUP BY MONTH(CreationDate), YEAR(CreationDate)

ORDER BY TheYear, TheMonth

.....but I would like to use the CBORM method if possible!

Luis Majano

unread,

Oct 30, 2020, 3:11:08 PM10/30/20

to ColdBox Platform

I think you want to maybe look at adding them as sql restrictions: https://coldbox-orm.ortusbooks.com/criteria-queries/coldbox-criteria-builder/restrictions/sql-restrictions or adding them as sql projections https://coldbox-orm.ortusbooks.com/criteria-queries/coldbox-criteria-builder/result-transformers#withprojections

Luis Majano

CEO

Ortus Solutions, Corp

1-888-557-8057

909-248-3408

www.ortussolutions.com

Support Open Source: www.patreon.com/ortussolutions

Linked In: https://www.linkedin.com/pub/3/731/483

Social: twitter.com/ortussolutions | twitter.com/lmajano

On Oct 30, 2020, 12:11 PM -0500, col...@googlegroups.com, wrote:

withProjections(property="MONTH(CreationDate):TheMonth,YEAR(CreationDate):Th

jcho...@gmail.com

unread,

Nov 3, 2020, 9:23:46 AM11/3/20

to ColdBox Platform

Thanks for the reply, Luis.

I can't work out the format of a restriction for this query due to needing a 'GROUP BY' statement. The withProjections method is the one I used.

prc.SentSummary = EmailHistoryService.newCriteria()

.withProjections(property="MONTH(CreationDate):TheMonth,YEAR(CreationDate):TheYear", count="EmailHistoryID:NumberOfSentEmails", groupproperty = "TheMonth,TheYear")

.order("TheMonth")

.order("TheYear")

.asStruct()

.list();

....but Hibernate does not like applying the SQL functions Month and Year to a property. Specifically the groupproperty doesn't like either the alias ("...could not resolve property: TheMonth of: EmailHistory...") or the SQL functions ("...could not resolve property: MONTH(CreationDate) of: EmailHistory...").

jcho...@gmail.com

unread,

Nov 4, 2020, 9:55:55 AM11/4/20

to ColdBox Platform

I've also tried:

prc.SentSummary = EmailHistoryService.executeQuery(query= "SELECT COUNT(EmailHistoryID) AS EmailsSent, MONTH(eh.CreationDate) AS TheMonth, YEAR(eh.CreationDate) AS TheYear from EmailHistory eh GROUP BY MONTH(eh.CreationDate), YEAR(eh.CreationDate)", asQuery = true );

This results in:

Can't cast Object type [[Ljava.lang.Object;] to a value of type [Component]

BUT the log file shows a correct SQL statement:

select

count(emailhisto0_.EmailHistoryID) as col_0_0_,

month(emailhisto0_.CreationDate) as col_1_0_,

year(emailhisto0_.CreationDate) as col_2_0_

from

tbl_EmailHistory emailhisto0_

group by

month(emailhisto0_.CreationDate) ,

year(emailhisto0_.CreationDate)