Best way to do calculated fields in Django models?

I'm trying to get a top 10 list by ratings on my objects. 
In SQL, I
can do this:

SELECT
  COUNT(*) AS n,
  (SUM(rating)/COUNT(*)) AS average
FROM
  ratings_rating
WHERE
  content_type_id=15 AND
  object_id=1
GROUP BY content_type_id, object_id
ORDER BY average DESC
LIMIT 10;

Is there a way to do something similar using the Django ORM?

There's different parts to this question.  In that SQL there
is:

* A calculated field (average) based on a SQL grouping and a
SUM and
COUNT
* A GROUP BY
* An ORDER BY on the calculated field

I'd prefer to use the ORM but I know I can bust out into SQL
if I have
to.

Thanks,
Rob


--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the
Google Groups "Django users" group.
To post to this group, send email to django-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---

I've figured out how to do the extra SELECT columns but not
the GROUP
BY:

>>>
Rating.objects.filter(content_type=15).filter(object_id=1).e
xtra(select={'n': 'COUNT(*)', 'average':
'SUM(rating)/COUNT(*)'})._get_sql_clause()

That's looking good but without the GROUP BY I get the MySQL
error:

OperationalError: (1140, 'Mixing of GROUP columns
(MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal
if there is
no GROUP BY clause')

>From the looks of it the only way to do this is with the
cursor.execute(SQL).

-Rob


--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the
Google Groups "Django users" group.
To post to this group, send email to django-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---

Rob Hudson wrote:
> I've figured out how to do the extra SELECT columns but
not the GROUP
> BY:
>
> >>>
Rating.objects.filter(content_type=15).filter(object_id=1).e
xtra(select={'n': 'COUNT(*)', 'average':
'SUM(rating)/COUNT(*)'})._get_sql_clause()
>
> That's looking good but without the GROUP BY I get the
MySQL error:
>
> OperationalError: (1140, 'Mixing of GROUP columns
> (MIN(),MAX(),COUNT(),...) with no GROUP columns is
illegal if there is
> no GROUP BY clause')
>
> >From the looks of it the only way to do this is
with the
> cursor.execute(SQL).
>
> -Rob

Rob,

There is no direct support for grouping in the DB API. Its a
tough
problem that I've been thinking about, but I'm not sure how
it would
fit into the DB API. For example, in your query you aren't
returning
Rating objects anymore so using the Rating manager doesn't
really fit.
Its almost like you need some sort of dynamic model class
that can
create attributes on the fly based on the query results
(easy enough
with a list of dicts), but the hard part is then having this
class
understand relations so you can dereference related objects.

So in your example you would want it to return a collection
of objects
with a content_type_id, object_id, n and average attributes
and then
allow access of the content_type and object relations via
results[0].content_type.field...

I'd love to see support for this in Django, but I don't see
a lot of
discussion about it. People sometimes refer to SQLAlchemy
for these
cases, but I haven't seen how that would really help with
the related
objects part of the problem.

-Dave


--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the
Google Groups "Django users" group.
To post to this group, send email to django-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---

DavidA wrote:
> Rob,
> 
> There is no direct support for grouping in the DB API.
Its a tough
> problem that I've been thinking about, but I'm not sure
how it would
> fit into the DB API. For example, in your query you
aren't returning
> Rating objects anymore so using the Rating manager
doesn't really fit.
> Its almost like you need some sort of dynamic model
class that can
> create attributes on the fly based on the query results
(easy enough
> with a list of dicts), but the hard part is then having
this class
> understand relations so you can dereference related
objects.
> 
> So in your example you would want it to return a
collection of objects
> with a content_type_id, object_id, n and average
attributes and then
> allow access of the content_type and object relations
via
> results[0].content_type.field...

Thanks for the reply.

After working a little longer on this I realized my original
SQL wasn't 
exactly correct.  I want the top 10 objects with the highest
average. 
That means I needed to remove the WHERE object_id=1 so it
can get any 
object_id.

I found that Jeff Croft is sorting by calculated field at 
Lost-Theories.com but what he's doing is the 
dictsortreversed:"get_rating" in the template,
which I believe pulls all 
rows into memory, sorts them on that method, and he then
displays them. 
  I thought about using this method for a moment but I need
the sorting 
to happen at database time so I know which 10 rows to pull. 
(But it's 
good to know you can sort all rows like that, that's pretty
slick.)

I did end up dropping into SQL and decided to gather all
object_ids 
using a similar SQL statement.  Then putting all my related
objects into 
a list and passing that to the context.  Not optimal but I'm
guessing it 
will work fine on small rows of data.

The other option is to update an "average" column
in my related object's 
  table whenever a rating is updated.  That could easily be
done.  If it 
turns out I'm using the average rating as a key piece of
data then this 
does start to make sense (and it is starting to sound like I
am).

> I'd love to see support for this in Django, but I don't
see a lot of
> discussion about it. People sometimes refer to
SQLAlchemy for these
> cases, but I haven't seen how that would really help
with the related
> objects part of the problem.

I'd like to see more discussion of this for Django.  It
sounds like a 
hard problem, but certainly sounds like a 1.0 feature for
the DB layer.

Could we educate ourselves on how other frameworks are
solving this same 
problem and maybe that will lead us to a solution?

-Rob

--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the
Google Groups "Django users" group.
To post to this group, send email to django-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---