[coldbox 3.7.0; cf10; mysql] Running DB Methods on properties in criteria builder.

I have a table full of events.

I am using criteria builder to get and list these events.

A property in events is “dateStart”, which holds a standard date.

I need to get all events for a given Month

In SQL, I might do something like:

.. WHERE MONTH(dateStart) = #month(now())# and YEAR(dateStart) =#year(now())#

I need to acheive this using criteria builder, but can’t quite work out how:

I tried:

`

events.isGT(“Month(dateStart)”, arguments.month);

`

but get error could not resolve property: MONTH(dateStart) of: Event

Can anyone suggest how I would be best to list events for a given Month and Year using Criteria Builder?

Many Thanks

Jason

You should be able to do that by adding a sqlRestriction() to the Criteria Builder. sqlRestriction() allows you to use arbitrary SQL to modify the resultset (e.g., add SQL restrictions directly to the WHERE clause of the query that Criteria Builder ultimately generates).

So it might look like:

var c = newCriteria();
c.sqlRestriction( …sql here );

Of course, any SQL that you add directly is going to be specific to the database type you’re using…but it’s darn handy for more exotic queries :slight_smile:

The first argument in isGT() is the ORM property name configured in your model. If you want to use SQL-specific functions ( e.g. - MONTH() ), you’ll have to use sqlRestriction.

That said, you could datePart() in conjunction with createDate() to create your date variable:

events.isGT(“dateStart”, createDate(datePart(“yyyy”,now()),arguments.month,01));

HTH,
Jon

In the end, I have gone for this… which is working great… not sure if there is a more efficient way.

`

if (isDate(arguments.month)){
var monthStart = createDate(Year(arguments.month),Month(arguments.month),1);
events.isGE(“dateStart”, monthStart)
.isLT(“dateStart”, dateAdd(“m”,1,monthStart));
}

`

Just checking: Do you want to run Year() on your month? You could use now() instead if you want the current year. If you run year(arguments.month) on a two-digit month variable, you’re going to get either 1900 or 1899 as the year.

-J