0

It looks like a simple question, but I am not being able to get the expected results. I have a table like this:

| id# | Concept | Amount |    Date    |
| ... |   ...   |   10   | 10/12/2013 |
| ... |   ...   |   20   | 12/12/2013 |
| ... |   ...   |   30   | 02/01/2014 |
| ... |   ...   |   40   | 03/01/2014 |
| ... |   ...   |   50   | 04/02/2014 |
| ... |   ...   |   60   | 05/02/2014 |

I would like to find a way to get a QuerySet with this table:

| Date.year | Amount |
|    2013   |    30  |
|    2014   |   180  |

and I guess I would be able to get in a similar way, something like:

| Date.month | Amount |
|   12/2013  |    30  |
|   01/2014  |    70  |
|   02/2014  |   110  |

Any ideas?

EDIT

I will try to clarify, sorry for any misunderstanding or bad explanation :-/

I have this model:

from django.db import models
from django.core.urlresolvers import reverse

class Expense(models.Model):
    concept = models.CharField(max_length=255)
    date = models.DateField()
    amount = models.DecimalField(max_digits=7,decimal_places=2)

    class Meta:
        ordering = ['date']

    def __unicode__(self):
        return '%s' % (self.concept)

    def get_absolute_url(self):

I have this template:

...
<table>
  {% for year in years %}
    <tr>
      <td width="40%">
        {{ year.year }}
      </td>
      <td width="60%" align="right">{{ year.total_amount }}</td>
    </tr>
  {% endfor %}    
</table>
...

I am trying to find a QuerySet for my view:

def index(request):
    years = Expense.objects.values('date').annotate(total_amount=Sum('amount'))
    return render(request, 'account/index.html', {'years': years})

In this case, years is returning something "close" to what I want, because it is grouping the expenses by date, but I would like to group them by month or year, instead of "day".

I do not know what is the best way to do this, with a query to Expense or in the template, or some other way. That is what I need. I hope that now is a bit clearer.

ekad
  • 14,436
  • 26
  • 44
  • 46
makeMonday
  • 2,303
  • 4
  • 25
  • 43
  • 1
    Django uses an ORM, you don't execute SQL commands on your own. You need to have a model that has fields which are mapped to those of your table in the database (ideally you let django create the database based on those models). Then you can work with the models and fetch rows as objects. So what part are you having trouble with here? configuring the models or fetching them or what? Have you done the Django tutorial? – yuvi Nov 24 '14 at 12:36
  • I have the model "Expense" with its attributes and it looks like the first table. I am able to sort it by day (date) with: `years = Expense.objects.values('date').annotate(total_amount=Sum('amount'))`; but not by month or year on a `view` (to show it on a `template`) – makeMonday Nov 24 '14 at 12:42
  • 1
    possible duplicate of [Annotate (group) dates by month/year in Django](http://stackoverflow.com/questions/21837227/annotate-group-dates-by-month-year-in-django) – AKX Nov 24 '14 at 12:42
  • @AKX Yes, I've seen this, but it's a bit different from my case. I am not sure how can I adapt this to mine. The PO has a `Group` of `Items` which have a date. So he is kind of forwarding. I tried to adapt the solution to my case and I get a few errors when I try :( – makeMonday Nov 24 '14 at 13:01
  • Assuming your model is `MyModel`, doesn't `MyModel.objects.all().extra(select={'year': "EXTRACT(year FROM date)"}).values('year').annotate(amount=Sum('amount'))` do the trick? – AKX Nov 24 '14 at 13:07
  • This query looks good, but I get: `near "FROM": syntax error` – makeMonday Nov 24 '14 at 13:08
  • @makeMonday -- you'll have to look at the docs for your particular RDBMS. It might be `MONTH(date)`, or for MySQL, `DATE_FORMAT(date, "%Y-%m")` would do the trick... – AKX Nov 24 '14 at 13:15

2 Answers2

0

This method is not entirely efficient (because you're working with the data on the python side), though that's not to say that it is slow by any means. For most cases, it should work seamlessly. 

 #view
 def myview(request):
     expenses = Expense.objects.all().order_by('date')
     data = {}

     for exp in expenses:
         y = exp.date.strftime("%Y")
         if (data.has_key(y)):
             data[y].append(exp)
         else:
             data[y] = [exp]

     return render_to_response(request, "index.html", {"data": data})


 #html
 {% for year, expenses in data.items %}
   <h1> {{ year }} </h1>
   <ul>
     {% for expense in expenses %}
         <li><b>{{ expense.date|date:"M" }}:</b> {{ expense.amount }}</li>
     {% endfor %}
   </ul>
 {% endfor %}

You can obviously fit this however you'd like. The idea is pretty simple - parse the data and arrange it with python after you fetch it through SQL.

yuvi
  • 18,155
  • 8
  • 56
  • 93
  • Ok, but this is not what I want. I mean, I am trying to find the best way to show, in a template, a year (2014) and the total amount of expenses that year. I was trying to do that in the template, but reading around StackOverflow (and also my common sense :P) I found that it might be a bad practice. So I was trying to find a `query` to get a "`table`" with those values. – makeMonday Nov 24 '14 at 14:43
  • @makeMonday Ohhh ok, I understand now what you're trying to do. Before I update my answer - is efficiency a big issue for you? – yuvi Nov 24 '14 at 15:41
  • @makeMonday there. Updated my answer – yuvi Nov 24 '14 at 16:18
  • [CORRECTION]: Sorry @yuvi, I did not read it right. This is still not what I wanted :( I will post an answer with my solution. Maybe it will clear a little bit more my issue. Thanks. – makeMonday Nov 24 '14 at 16:31
  • @makeMonday you want to sum up the results first for each year? – yuvi Nov 24 '14 at 17:46
0

I was finally able to get the expected results, but I do not really like it. I'm still wondering if there is a way to solve this question in a way similar to what I was asking.

Basically, I created another dictionary (amounts) that looks for the years in expense and based on that it keeps adding the expense.amount based on the year. So in the end amounts will be something like: {2013: Decimal('16.58'), 2014: Decimal('104.66')}

My solution:

views.py:

from django.template.defaulttags import register
...         
@register.filter
def get_item(dictionary, key):
    return dictionary.get(key)

def index(request):
    years = Expense.objects.dates('date','year')
    expenses = Expense.objects.all().values()
    amounts = {}

    for expense in expenses:
        year = expense['date'].year
        if year in amounts:
            amounts[year] = amounts[year] + expense['amount']
        else:
            amounts[year] = expense['amount']

    return render(request, 'index.html', {'years': years, 'amounts': amounts})

template:

<tbody> 
  {% for year in years %}
    <tr>
      <td width="50%">
        <a href="{{year.year}}">
          {{ year.year }}
        </a>
      </td>
      <td width="50%" align="right">{% if year.year in amounts %} {{ amounts|get_item:year.year }} {% endif %}</td>
    </tr>
  {% endfor %}
</tbody>

Any better idea? ;)

makeMonday
  • 2,303
  • 4
  • 25
  • 43