CODEWITHSUNDEEP
grailsgrails-orm
Hamila Mohamed Amine
Hamila Mohamed Amine

Reputation: 290

Query to get data day by day or month by month etc ? in grails

I want to draw some charts in my grails application, but before this I must get the data to feed the chart from the data base, but I still can’t figure out how to get that data that I need with the correct method.

I have a start Date and an end Date that limit the period and I want to get Data by day or week, month, year, etc.

I think of making a loop from start Date to end Date with step with step equal to one day /week /month /year etc.

Do you think this is the right approach? if yes how to write this in groovy , if no please tell how to do this the right way

Thanks

Upvotes: 0

Views: 1439

Answers (1)

felipenasc
felipenasc

Reputation: 448

What I like to do in this case is to group and sum the results in the query itself, just like you would do in plain sql using sum() and group by clauses. Grails is based on Hibernate, and Hibernate as a select new map() syntax that is great for theses cases.

Here is an example in Grails to select financial totals by month (like financial transactions):

def query = """
  select new map(sum(tx.value) as total, month(tx.date) as month, year(tx.date) as year)
    from Transaction tx
   where tx.date >= :beginDate and tx.date < :endDate
   group by month(tx.date), year(tx.date)
   order by year(tx.date) desc , month(tx.date) desc
"""
def result = Transaction.executeQuery(query,[beginDate:someDate, endDate:anotherDate])

With that code you can just loop over your result variable and get to each result item which are Maps. Like so:

result.each{ item ->
    println "Financial total for ${item.month}/${item.year} is equal to ${item.total}"
}

I think this is very simple and strait to the point solution. Regards...

Upvotes: 1

Related Questions