GROUP BY month(date)

[Lei Wu]

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智能手机

[Jacopo Cappellato-4]

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, [hidden email] <
[hidden email]> 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智能手机

[Jacques Le Roux]

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, [hidden email] <
> [hidden email]> 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智能手机