groupBy datetime

[Locke]

Depending on dresende/node-sql-query#10 I want to group by a DATETIME field and want to pass the format string to be able to group by a day, year or even an hour.

[dresende]

Can you show me an sql example?

[dresende]

ORM still does not support it, I have to think how to do it. If you have any idea of how this could be done please post an example.

[Locke]

If I see it correct ORM does not use the .fun(..) method from node-sql-query, does it?

I'm thinking about either a general support for the fun method or a special implementation with strftime.

[dresende]

Yes, it doesn't use it but maybe it should, or... the drivers should. Or at least expose it.

[Locke]

In Locke/node-orm2 and Locke/node-sql-query I simply added strftime to the aggregation functions and can now group via:

Model.aggregate().count().strftime([ query.Text('%Y-%m-%d'), 'date' ]).groupBy('strftime_date').get(...)

I don't know if it's compatible with all drivers, therefore I updated only the sqlite driver.

As this is a lot of typing I could imagine adding wrappers like .groupByDay('myDateField') in the AggregateFunctions proto.

[dresende]

I just don't want to add strftime directly, I want to expose the generic function call. And have also to expose the query part.

[dresende]

I would expose strftime if it was generic or have the same function across drivers (even if it had different names).

[dresende]

You can now call any supported function like this:

Person.aggregate().call("strftime", [ orm.Text('%Y-%m-%d'), 'date' ]).as('dt').groupBy('dt').get(function () {
    console.log(arguments);
});

The .as() is optional and it's just an alias that can for example be used in .groupBy.

Please try the latest commit and see if it works for you.

[Locke]

Great, in my testcase that works fine. I still need to deploy that into my
productive environment, but I don't expect any complications. I'll close
this issue if it works.
Am 06.07.2013 01:03 schrieb "Diogo Resende" [email protected]:

You can now call any supported function like this:

Person.aggregate().call("strftime", [ orm.Text('%Y-%m-%d'), 'date' ]).as('dt').groupBy('dt').get(function () {
console.log(arguments);});

The .as() is optional and it's just an alias that can for example be used
in .groupBy.

Please try the latest commit and see if it works for you.

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/204#issuecomment-20543222
.