[Coldbox 6.0.0] [CBORM 2.5.0]

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!

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

Support Open Source: www.patreon.com/ortussolutions
Linked In: https://www.linkedin.com/pub/3/731/483
Social: twitter.com/ortussolutions | twitter.com/lmajano

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...").

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)

You can also just do an HQL statement, I think that might be best.

Luis Majano
CEO
Ortus Solutions, Corp
1-888-557-8057
909-248-3408

Support Open Source: www.patreon.com/ortussolutions
Linked In: https://www.linkedin.com/pub/3/731/483
Social: twitter.com/ortussolutions | twitter.com/lmajano