You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ofbiz.apache.org by "wulei.bj.cn@gmail.com" <wu...@gmail.com> on 2014/11/15 08:19:14 UTC
GROUP BY month(date)
Guys,
I have an Expense entity with a trx_date colum of type DATE to record the date that I bought sth.
And then I wanna summary the total expenses by month, and I hit a blocker. I tried to create an view entity with a complex alias to represent the month, it's like:
<alias name="month" group-by="true">
<complex-alias operator="month()">
<complex-alias-field entity="Expense" field="trx_date">
</complex-alias>
</alias>
However, it did not work. Could anyone help me out here to get what I wanted? Thanks so much!
I am using the default database derby by the way. And Ofbiz version is 12.04.
--
发自我的ONEPLUS智能手机
Re: GROUP BY month(date)
Posted by Jacques Le Roux <ja...@les7arts.com>.
Yes, why not committing it?
Jacques
Le 15/11/2014 09:29, Jacopo Cappellato a écrit :
> month is not a standard function of SQL even if nowadays is available in
> most database management systems.
> However you may find it useful the patch below that adds the support for
> the date and month functions (if others are interested it could be
> committed to public repository).
> In order to use it you will have to define an alias field like:
>
> <alias entity-alias="EX" name="trx_date" function="month"/>
>
> I hope it helps,
>
> Jacopo
>
> Index:
> ../trunk/framework/entity/src/org/ofbiz/entity/model/ModelViewEntity.java
>
> ===================================================================
>
> ---
> ../trunk/framework/entity/src/org/ofbiz/entity/model/ModelViewEntity.java
> (revision
> 1637645)
>
> +++
> ../trunk/framework/entity/src/org/ofbiz/entity/model/ModelViewEntity.java
> (working
> copy)
>
> @@ -65,6 +65,8 @@
>
> functionPrefixMap.put("count-distinct", "COUNT(DISTINCT ");
>
> functionPrefixMap.put("upper", "UPPER(");
>
> functionPrefixMap.put("lower", "LOWER(");
>
> + functionPrefixMap.put("year", "YEAR(");
>
> + functionPrefixMap.put("month", "MONTH(");
>
> }
>
>
>
> /** Contains member-entity alias name definitions: key is alias, value
> is ModelMemberEntity */
>
> @@ -480,7 +482,7 @@
>
> fieldSet = alias.getFieldSet();
>
> }
>
> }
>
> - if ("count".equals(alias.function) ||
> "count-distinct".equals(alias.function)) {
>
> + if ("count".equals(alias.function) ||
> "count-distinct".equals(alias.function) || "year".equals(alias.function) ||
> "month".equals(alias.function)) {
>
> // if we have a "count" function we have to change the type
>
> type = "numeric";
>
> }
>
>
> On Sat, Nov 15, 2014 at 8:19 AM, wulei.bj.cn@gmail.com <
> wulei.bj.cn@gmail.com> wrote:
>
>> Guys,
>>
>> I have an Expense entity with a trx_date colum of type DATE to record the
>> date that I bought sth.
>> And then I wanna summary the total expenses by month, and I hit a blocker.
>> I tried to create an view entity with a complex alias to represent the
>> month, it's like:
>> <alias name="month" group-by="true">
>> <complex-alias operator="month()">
>> <complex-alias-field entity="Expense" field="trx_date">
>> </complex-alias>
>> </alias>
>>
>> However, it did not work. Could anyone help me out here to get what I
>> wanted? Thanks so much!
>>
>> I am using the default database derby by the way. And Ofbiz version is
>> 12.04.
>> --
>> 发自我的ONEPLUS智能手机
Re: GROUP BY month(date)
Posted by Jacopo Cappellato <ja...@hotwaxmedia.com>.
month is not a standard function of SQL even if nowadays is available in
most database management systems.
However you may find it useful the patch below that adds the support for
the date and month functions (if others are interested it could be
committed to public repository).
In order to use it you will have to define an alias field like:
<alias entity-alias="EX" name="trx_date" function="month"/>
I hope it helps,
Jacopo
Index:
../trunk/framework/entity/src/org/ofbiz/entity/model/ModelViewEntity.java
===================================================================
---
../trunk/framework/entity/src/org/ofbiz/entity/model/ModelViewEntity.java
(revision
1637645)
+++
../trunk/framework/entity/src/org/ofbiz/entity/model/ModelViewEntity.java
(working
copy)
@@ -65,6 +65,8 @@
functionPrefixMap.put("count-distinct", "COUNT(DISTINCT ");
functionPrefixMap.put("upper", "UPPER(");
functionPrefixMap.put("lower", "LOWER(");
+ functionPrefixMap.put("year", "YEAR(");
+ functionPrefixMap.put("month", "MONTH(");
}
/** Contains member-entity alias name definitions: key is alias, value
is ModelMemberEntity */
@@ -480,7 +482,7 @@
fieldSet = alias.getFieldSet();
}
}
- if ("count".equals(alias.function) ||
"count-distinct".equals(alias.function)) {
+ if ("count".equals(alias.function) ||
"count-distinct".equals(alias.function) || "year".equals(alias.function) ||
"month".equals(alias.function)) {
// if we have a "count" function we have to change the type
type = "numeric";
}
On Sat, Nov 15, 2014 at 8:19 AM, wulei.bj.cn@gmail.com <
wulei.bj.cn@gmail.com> wrote:
> Guys,
>
> I have an Expense entity with a trx_date colum of type DATE to record the
> date that I bought sth.
> And then I wanna summary the total expenses by month, and I hit a blocker.
> I tried to create an view entity with a complex alias to represent the
> month, it's like:
> <alias name="month" group-by="true">
> <complex-alias operator="month()">
> <complex-alias-field entity="Expense" field="trx_date">
> </complex-alias>
> </alias>
>
> However, it did not work. Could anyone help me out here to get what I
> wanted? Thanks so much!
>
> I am using the default database derby by the way. And Ofbiz version is
> 12.04.
> --
> 发自我的ONEPLUS智能手机