The whole thing behind that it is: I'm developing an invoicing/
inventory application and I run into performance problems that might
require using raw SQL and admittedly I'm not an SQL expert and I
dislike the idea of doing so.
I thought more on an approach of "SQL view" than denormalisation or a
pseudoattribute. I've also read a little about F() and the extra()
function to inject SQL, but that's to be done for every query while
I'm more interested on having the calculation (business rule) defined
once in the model.
I could also access a view in PostGres where 'total' is defined as
'price*quantity' but as far as I know (and my SQL knowledge is rather
poor), views are not updatable.
The example was a quick and dirty draft and should read:
from django.db import models
class InvoiceLine(models.Model):
price = models.DecimalField()
quantity = models.DecimalField()
total = models.ExpressionField('price * quantity')
The SQL looks something like this for InvoiceLine.objects.all():
SELECT price, quantity, price * quantity AS total FROM invoiceline;
Marc
On Aug 18, 4:08 pm, Russell Keith-Magee <freakboy3...@gmail.com>
wrote:
> On Tue, Aug 18, 2009 at 9:21 PM, mettwoch<mettw...@pt.lu> wrote:
>
> > Hi,
>
> > Some time ago I asked if there is a way to aggregate on an expression
> > and I was told that it's not possible at the moment. I managed to use
> > a mix of aggregation and some arithmetics to reduce processing time by
> > a factor of 20 in critical areas of my application. While doing so, I
> > felt that some kind of a new field-type could be useful and perhaps
> > lead to make aggregation on expressions possible. Something like an
> > ExpressionField (or SQLField or whatever ...) that does some SQL
> > processing directly on the database and that could be used like this:
>
> > class InvoiceLine():
> > price = DecimalField()
> > quantity = DecimalField()
> > total = ExpressionField('price*quantity')
> > invoice = ForeignKey(Invoice)
>
> > One could do:
>
> > invoice.invoiceline_set.objects.aggregate(Sum('total'))
>
> > Is this a reasonable approach
>
> At this point you haven't really explained what the approach _is_. To
> pick just one aspect - what does "ExpressionField" do?. It could be a
> denormalization - that is, a column that is stored in the database
> that is automatically updated with as 'price' and 'quantity' are
> updated. It could be part of turning InvoiceLine from a SQL table to a
> SQL view. It could be a pseudoattribute that only appears in Django's
> ORM and is computed on request from the current value of price and
> quantity.
>
> You haven't really given a proposal. You've given a example usage of
> an API which is either ambiguous or buggy - InvoiceLine doesn't have a
> base class; the 'objects' manager is being used on a related _set
> object for no apparent reason. You also haven't explained how that API
> you have proposed would be interpreted as SQL.
>
> > and is it perhaps already in the
> > timeline?
>
> The idea of adding denormalization fields has been discussed in the
> past. Some code has been written, but nothing has been committed to
> trunk. Search the archives for the discussion.
>
> There have also been proposals to allow F() objects inside aggregates,
> and to allow annotation of data that isn't an aggregate onto a model.
> I suspect these may be closer to what you are trying to achieve. These
> ideas have open tickets, but I'm not aware of anyone actively working
> on these topics at the moment.
>
> Yours,
> Russ Magee %-)
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
