CODEWITHSUNDEEP
djangodjango-queryset
patrickf
patrickf

Reputation: 53

Deriving multiple values from single field in django queryset

I have a model that looks a bit like this:

class InvoiceEntry(models.Model):
    ...
    username = models.CharField(max_length=20)  <-- the users name
    billtime = models.DecimalField()            <-- how much time has been spent
    billable = models.CharField(max_length=1)   <-- is this work billable? (Y or N)
...

These fields cant be changed - they are a part of a table that already contains 200k+ rows.

I need to create a queryset that returns:

...say, of the last 7 days (that bit is easy).

What is the neatest way to do this?

Thanks in advance - Patrick

Upvotes: 2

Views: 1214

Answers (1)

Brandon Bertelsen
Brandon Bertelsen

Reputation: 44638

It would be easier (and better for you all around) if you set billable to be a BooleanFIeld ( see: https://docs.djangoproject.com/en/dev/ref/models/fields/#booleanfield ). For a CharField, default form is a textinput box, for a bool field, it's a checkmark - makes more sense to the user! Rather than explaining to people that they have to use Y or N.

Also, your model doesn't have a primary key. You may need it for per user view definitions in the future. userid = models.AutoField(primary_key=True) can save you big hassles.

If you had a primary key you could avoid potential confusion with duplicated usernames in the future, as well. My solution below for example would count john.smith as one user if there were two different users with the same name (your Model allows for this currently, unless you add unique = True).

For example.

   class InvoiceEntry(models.Model):
        username = models.CharField(max_length=20)  <-- the users name
        billtime = models.DecimalField()            <-- how much time has been spent
        billable = models.BooleanField()   # Now you just use consistent True or False

Personally, for tasks like this I just generate my own dictionary. Using your original model.

q = InvoiceEntry.objects.all() # only call it once
results = []
users = q.values_list('username',flat=True)
for user in users
    dict = { 
        "user": user,
        "unbillable":sum(q.filter(username = user,billable="N").values_list('billtime',flat=True)),
        "billable":sum(q.filter(username = user,billable="Y").values_list('billable',flat=True)),
        }
    results.append(dict)

With my recommendation to change the field to Boolean: 

q = InvoiceEntry.objects.all() # only call it once
results = []
users = q.values_list('username',flat=True)
for user in users
    dict = { 
        "user": user,
        "unbillable":sum(q.filter(username = user,billable=False).values_list('billtime',flat=True)),
        "billable":sum(q.filter(username = user,billable=True).values_list('billtime',flat=True)),
        }
    results.append(dict)

So now, if you're using render_to_reponse in a view or something like that your template would look like: 

{% for result in results %}
{{ result.user }}
{{ result.unbillable }} 
{{ result.billable }}
{% endfor %}

Upvotes: 2

Related Questions