Re: values() and order_by()

[dmik]

https://code.djangoproject.com/ticket/17144 has patch fixing this problem

On Tuesday, July 3, 2012 2:51:17 PM UTC+4, jjmaestro wrote:
>
> Hi there! 
>
> I have the following model: 
>
>   class Visit(models.Model): 
>   user = models.ForeignKey(User) 
>   visitor = models.ForeignKey(User, related_name="visitor") 
>   last_visit = models.DateTimeField(default=timezone.now()) 
>   count_visits = models.IntegerField(default=0) 
>
> I would like to make the following SQL query using the Django ORM: 
>
>   SELECT last_visit, SUM(count_visits) AS total_count_visits 
>   FROM core_visit 
>   WHERE user_id=42 
>   GROUP BY user_id 
>   ORDER BY last_visit DESC 
>
> So far, I haven't been able to do it. When I use values('user') and 
> order_by('-last_visit') the last_visit column is automatically added 
> to the GROUP BY clause. I've read in the Django doc that this is how 
> it's supposed to be but I can't seem to understand it. Why do I have 
> to group the results by whatever I choose as order? What am I missing? 
>
> Also, I know I can do a RawQuerySet but unfortunately, I would like to 
> be able to use a full QuerySet (this query goes into Tastypie and so 
> far, I haven't been successful in using it with anything but full 
> QuerySets, not even ValueQuerySets...) 
>
> All help and ideas would be greatly appreciated. 
>
> Thanks a lot in advanced, 
>
> -- 
> J. Javier Maestro <jjmaes...@ieee.org> 
>

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/django-users/-/hjN-fd4PGngJ.
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.

Re: values() and order_by()

[Bill Freeman]

I haven't played much with annotation/aggregation, so I may be barking up
the wrong tree. but on:

   https://docs.djangoproject.com/en/1.4/topics/db/aggregation/

I find:

   "the original results are grouped according to the unique combinations of
the fields specified in the values() clause"

I take this to mean that all the columns mentioned in the values() method
(or all columns if none are mentioned) are forced to be part of grouping.
Might this be responsible for the unexpected GROUP_BY column?

You have not shown (or I have missed) the query set constructs which you
have tried, so I have to guess at what you are passing to value() or even
which model (User or Visit) you begin from.

(I also don't understand why you have two foreign keys from Visit to User,
but that is probably not important.)

Bill

On Wed, Jul 4, 2012 at 8:15 AM, J. Javier Maestro <jjmaes...@ieee.org> wrote:
> On Wed, Jul 4, 2012 at 1:59 PM, J. Javier Maestro <jjmaes...@ieee.org> wrote:
>> Hi there!
>>
>> Thanks a lot for the reply. However, I don't think your solution would
>> work in my case, nor it answers the question of whether it can be done
>> or why the order_by() adds stuff to the GROUP BY clause when used in
>> conjunction with values().
>>
>> Let's see:
>>
>> On Tue, Jul 3, 2012 at 11:08 PM, Bill Freeman <ke1g...@gmail.com> wrote:
>>> First, I think that you're going about last_visit in the wrong way.
>>> timezone.now() is evaluated once, when the module is imported,
>>> rather than when the user visits.  DateTimeField's auto_now=True
>>> option is tailor made for this kind of situation.
>>
>> I know about auto_now and, apart from the discussions that can be
>> found all over the Internet, is a nice feature... unless you update
>> records (like I do). Then, auto_now does nothing since it only affects
>> save(). In any case, the default value is actually a leftover from the
>> initial implementation, right now is not even needed.
>
> Bill,
>
> Re-reading the update() documentation and the way I implemented the
> middleware, you are right that auto_now will be the best solution. I
> will use save() instead of update() and will get that fixed.
>
> Thanks a lot for the suggestion!  If you have any ideas about the
> values() and order_by() issue, I will be happy to hear them!
>
> Cheers,
>
> --
> J. Javier Maestro <jjmaes...@ieee.org>
>
> --
> 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.
>

-- 
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.

Re: values() and order_by()

[J. Javier Maestro]

On Wed, Jul 4, 2012 at 1:59 PM, J. Javier Maestro <jjmaes...@ieee.org> wrote:
> Hi there!
>
> Thanks a lot for the reply. However, I don't think your solution would
> work in my case, nor it answers the question of whether it can be done
> or why the order_by() adds stuff to the GROUP BY clause when used in
> conjunction with values().
>
> Let's see:
>
> On Tue, Jul 3, 2012 at 11:08 PM, Bill Freeman <ke1g...@gmail.com> wrote:
>> First, I think that you're going about last_visit in the wrong way.
>> timezone.now() is evaluated once, when the module is imported,
>> rather than when the user visits.  DateTimeField's auto_now=True
>> option is tailor made for this kind of situation.
>
> I know about auto_now and, apart from the discussions that can be
> found all over the Internet, is a nice feature... unless you update
> records (like I do). Then, auto_now does nothing since it only affects
> save(). In any case, the default value is actually a leftover from the
> initial implementation, right now is not even needed.

Bill,

Re-reading the update() documentation and the way I implemented the
middleware, you are right that auto_now will be the best solution. I
will use save() instead of update() and will get that fixed.

Thanks a lot for the suggestion!  If you have any ideas about the
values() and order_by() issue, I will be happy to hear them!

Cheers,

-- 
J. Javier Maestro <jjmaes...@ieee.org>

-- 
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.

Re: values() and order_by()

[J. Javier Maestro]

Hi there!

Thanks a lot for the reply. However, I don't think your solution would
work in my case, nor it answers the question of whether it can be done
or why the order_by() adds stuff to the GROUP BY clause when used in
conjunction with values().

Let's see:

On Tue, Jul 3, 2012 at 11:08 PM, Bill Freeman  wrote:
> First, I think that you're going about last_visit in the wrong way.
> timezone.now() is evaluated once, when the module is imported,
> rather than when the user visits.  DateTimeField's auto_now=True
> option is tailor made for this kind of situation.

I know about auto_now and, apart from the discussions that can be
found all over the Internet, is a nice feature... unless you update
records (like I do). Then, auto_now does nothing since it only affects
save(). In any case, the default value is actually a leftover from the
initial implementation, right now is not even needed.

> Next, since you have the user id, I'm going to assume that you have
> an actual user object.  In the code below I'll presume that it's
> request.user .
>
> visits_qs = Visit.objects.filter(user=request.user)
> total_visits_count = visits_qs.count()
> latest_visit = visits_qs.order_by('-last_visit')[0]
>
> Yes, this is two database queries, but unless you are seeing a performance
> issue that you've traced to the fact that this is more than one query, this
> code is a heck of a lot more maintainable than trying to make it happen
> in one.

First, this won't work in my implementation. My model stores the
actual count of a given visitor in the count_visits field and the
field is updated every time the visitor visits a user profile (via a
middleware and an F() expression) while your solution stores a full
record every time you want to log a visit to a user profile.

Logging visits your way will eat quite a bit of space in the database.
Also, it's not only about saving space and it's not only about two
database queries. In your implementation, the count() will scan the
whole set of records for the given user. This scan will grow with the
number of records and in this case, taking into account that the whole
purpose is to show a counter for visits to a given user profile... I
think that your proposed workaround does not really fit.


So, I ask again: Why the order_by('colum') when used in conjunction
with values(), adds the column to the GROUP BY?

Thanks,

-- 
J. Javier Maestro 

-- 
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.

Re: values() and order_by()

[Bill Freeman]

First, I think that you're going about last_visit in the wrong way.
timezone.now() is evaluated once, when the module is imported,
rather than when the user visits.  DateTimeField's auto_now=True
option is tailor made for this kind of situation.

Next, since you have the user id, I'm going to assume that you have
an actual user object.  In the code below I'll presume that it's
request.user .

visits_qs = Visit.objects.filter(user=request.user)
total_visits_count = visits_qs.count()
latest_visit = visits_qs.order_by('-last_visit')[0]

Yes, this is two database queries, but unless you are seeing a performance
issue that you've traced to the fact that this is more than one query, this
code is a heck of a lot more maintainable than trying to make it happen
in one.

Bill

On Tue, Jul 3, 2012 at 6:51 AM, J. Javier Maestro <jjmaes...@ieee.org> wrote:
> Hi there!
>
> I have the following model:
>
>   class Visit(models.Model):
>   user = models.ForeignKey(User)
>   visitor = models.ForeignKey(User, related_name="visitor")
>   last_visit = models.DateTimeField(default=timezone.now())
>   count_visits = models.IntegerField(default=0)
>
> I would like to make the following SQL query using the Django ORM:
>
>   SELECT last_visit, SUM(count_visits) AS total_count_visits
>   FROM core_visit
>   WHERE user_id=42
>   GROUP BY user_id
>   ORDER BY last_visit DESC
>
> So far, I haven't been able to do it. When I use values('user') and
> order_by('-last_visit') the last_visit column is automatically added
> to the GROUP BY clause. I've read in the Django doc that this is how
> it's supposed to be but I can't seem to understand it. Why do I have
> to group the results by whatever I choose as order? What am I missing?
>
> Also, I know I can do a RawQuerySet but unfortunately, I would like to
> be able to use a full QuerySet (this query goes into Tastypie and so
> far, I haven't been successful in using it with anything but full
> QuerySets, not even ValueQuerySets...)
>
> All help and ideas would be greatly appreciated.
>
> Thanks a lot in advanced,
>
> --
> J. Javier Maestro <jjmaes...@ieee.org>
>
> --
> 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.
>

-- 
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.

values() and order_by()

[J. Javier Maestro]

Hi there!

I have the following model:

  class Visit(models.Model):
  user = models.ForeignKey(User)
  visitor = models.ForeignKey(User, related_name="visitor")
  last_visit = models.DateTimeField(default=timezone.now())
  count_visits = models.IntegerField(default=0)

I would like to make the following SQL query using the Django ORM:

  SELECT last_visit, SUM(count_visits) AS total_count_visits
  FROM core_visit
  WHERE user_id=42
  GROUP BY user_id
  ORDER BY last_visit DESC

So far, I haven't been able to do it. When I use values('user') and
order_by('-last_visit') the last_visit column is automatically added
to the GROUP BY clause. I've read in the Django doc that this is how
it's supposed to be but I can't seem to understand it. Why do I have
to group the results by whatever I choose as order? What am I missing?

Also, I know I can do a RawQuerySet but unfortunately, I would like to
be able to use a full QuerySet (this query goes into Tastypie and so
far, I haven't been successful in using it with anything but full
QuerySets, not even ValueQuerySets...)

All help and ideas would be greatly appreciated.

Thanks a lot in advanced,

-- 
J. Javier Maestro <jjmaes...@ieee.org>

-- 
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.

QuerySet values + annotate + order_by

[ScottF]

The background here is that I am trying to modify django-voting to
sort the objects by score, but this is a general question.

The following code works minus the order_by statement on the end:

   queryset = self.filter(
object_id__in = object_ids,
content_type = ctype,
).values(
'object_id',
).annotate(
score = CoalesceSum('vote', default='0'),
num_votes = CoalesceCount('vote', default='0'),
).order_by('score')

Without the order_by statement, I get the following result:

[{'score': 1, 'object_id': 2, 'num_votes': 1}, {'score': -1,
'object_id': 3, 'num_votes': 1}, {'score': 1, 'object_id': 4,
'num_votes': 1}]

but when adding order_by, I get the following error:

FieldError: Cannot resolve keyword 'score' into field. Choices are:
content_type
, id, object_id, user, vote

The choices listed belong to the "Vote" model.  But why aren't the
available choices 'object_id' (from the values statement) and 'score'
and 'num_votes' (from the annotate statement)?  I thought you could do
an order by anything in an annotate statement.

Thanks,
Scott



-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-us...@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.