Electronics & Programming

develissimo

Open Source electronics development and programming

  • You are not logged in.
  • Root
  • » Django
  • » GROUP BY fields appearance, bug or feature? [RSS Feed]

#1 Jan. 16, 2011 09:54:07

Jari P.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

GROUP BY fields appearance, bug or feature?


Hi!

Suppose following model:

class UserEmail(models.Model):
"""User email"""

user = models.ForeignKey(User, db_index=True,
null=True, blank=True, editable=False)
"""User recieving the email"""

added = models.DateTimeField(_("added"), auto_now_add=True)
"""Added to database"""

subject = models.CharField(_('subject'), max_length=128)
"""Subject"""

message = models.TextField(_('message'))
"""Message"""

How can I retrieve list of users and their latest email subject?

This almost works:

User.objects.all()\
.annotate(latest_email_added=Max('useremail__added'))

But it does not give me the other fields of latest email such as
subject, so I try to add other fields using extra:
User.objects.all()\
.annotate(latest_email_added=Max('useremail__added'))\
.extra(select={'email_subject' : 'myapp_useremail.subject'})

Suddenly it adds a GROUP BY to the query with a long list of fields
that should not be there, which breaks everything, now I get multiple
rows per user which is not wanted.

If I try to modify the group_by manually, like this:
a = User.objects.all()\
.annotate(latest_email_added=Max('useremail__added'))\
.extra(select={'email_subject' : 'dmusic_useremail.subject'});
a.query.group_by = ;
print a.query

There is still one extra field in group by making it break:
... GROUP BY "auth_user"."id", (dmusic_useremail.subject)

Can someone elaborate this behavior?

Any help is appreciated, thanks!

--
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 athttp://groups.google.com/group/django-users?hl=en.

Offline

#2 Jan. 16, 2011 10:17:35

Praveen K.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

GROUP BY fields appearance, bug or feature?


*First of all did you try accessing the email subject with a dotted
notation?*
try
u = User.objects.all()
and
u.subject


On Sun, Jan 16, 2011 at 12:53 PM, Jari Pennanen <jari.penna...@gmail.com>wrote:

> Hi!
>
> Suppose following model:
>
> class UserEmail(models.Model):
> """User email"""
>
> user = models.ForeignKey(User, db_index=True,
> null=True, blank=True, editable=False)
> """User recieving the email"""
>
> added = models.DateTimeField(_("added"), auto_now_add=True)
> """Added to database"""
>
> subject = models.CharField(_('subject'), max_length=128)
> """Subject"""
>
> message = models.TextField(_('message'))
> """Message"""
>
> How can I retrieve list of users and their latest email subject?
>
> This almost works:
>
> User.objects.all()\
> .annotate(latest_email_added=Max('useremail__added'))
>
> But it does not give me the other fields of latest email such as
> subject, so I try to add other fields using extra:
> User.objects.all()\
> .annotate(latest_email_added=Max('useremail__added'))\
> .extra(select={'email_subject' : 'myapp_useremail.subject'})
>
> Suddenly it adds a GROUP BY to the query with a long list of fields
> that should not be there, which breaks everything, now I get multiple
> rows per user which is not wanted.
>
> If I try to modify the group_by manually, like this:
> a = User.objects.all()\
> .annotate(latest_email_added=Max('useremail__added'))\
> .extra(select={'email_subject' : 'dmusic_useremail.subject'});
> a.query.group_by = ;
> print a.query
>
> There is still one extra field in group by making it break:
> ... GROUP BY "auth_user"."id", (dmusic_useremail.subject)
>
> Can someone elaborate this behavior?
>
> Any help is appreciated, thanks!
>
> --
> 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<django-users%2bunsubscr...@googlegroups.com>
> .
> For more options, visit this group at
>http://groups.google.com/group/django-users?hl=en.
>
>


--
*Praveen Krishna R*

--
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 athttp://groups.google.com/group/django-users?hl=en.

Offline

#3 Jan. 16, 2011 10:42:16

Jari P.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

GROUP BY fields appearance, bug or feature?


User != UserEmail

User does not have subject, thus it will raise AttributeError: 'User'
object has no attribute 'subject'

Secondly, I do not want to create 1+N queries (where N is number of
users), with creating a query per user this would be simple but very
inefficient.

On Jan 16, 12:16 pm, Praveen Krishna R <rpraveenkris...@gmail.com>
wrote:
> *First of all did you try accessing the email subject with a dotted
> notation?*
> try
> u = User.objects.all()
> and
> u.subject
>
> On Sun, Jan 16, 2011 at 12:53 PM, Jari Pennanen
> <jari.penna...@gmail.com>wrote:
>
>
>
>
>
>
>
>
>
> > Hi!
>
> > Suppose following model:
>
> > class UserEmail(models.Model):
> >    """User email"""
>
> >    user = models.ForeignKey(User, db_index=True,
> >                             null=True, blank=True, editable=False)
> >    """User recieving the email"""
>
> >    added = models.DateTimeField(_("added"), auto_now_add=True)
> >    """Added to database"""
>
> >    subject = models.CharField(_('subject'), max_length=128)
> >    """Subject"""
>
> >    message = models.TextField(_('message'))
> >    """Message"""
>
> > How can I retrieve list of users and their latest email subject?
>
> > This almost works:
>
> > User.objects.all()\
> >  .annotate(latest_email_added=Max('useremail__added'))
>
> > But it does not give me the other fields of latest email such as
> > subject, so I try to add other fields using extra:
> > User.objects.all()\
> >  .annotate(latest_email_added=Max('useremail__added'))\
> >  .extra(select={'email_subject' : 'myapp_useremail.subject'})
>
> > Suddenly it adds a GROUP BY to the query with a long list of fields
> > that should not be there, which breaks everything, now I get multiple
> > rows per user which is not wanted.
>
> > If I try to modify the group_by manually, like this:
> > a = User.objects.all()\
> >  .annotate(latest_email_added=Max('useremail__added'))\
> >  .extra(select={'email_subject' : 'dmusic_useremail.subject'});
> > a.query.group_by = ;
> > print a.query
>
> > There is still one extra field in group by making it break:
> > ... GROUP BY "auth_user"."id", (dmusic_useremail.subject)
>
> > Can someone elaborate this behavior?
>
> > Any help is appreciated, thanks!
>
> > --
> > 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<django-users%2Bunsubscribe@google
> > groups.com>
> > .
> > For more options, visit this group at
> >http://groups.google.com/group/django-users?hl=en.
>
> --
> *Praveen Krishna R*

--
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 athttp://groups.google.com/group/django-users?hl=en.

Offline

#4 Jan. 16, 2011 14:21:45

Jari P.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

GROUP BY fields appearance, bug or feature?


Here is my problem as using powerful raw,

User.objects.raw('SELECT usr.*, em.added as latest_email_added,
em.success as latest_email_success, em.subject as latest_email_subject
FROM auth_user AS usr LEFT JOIN dmusic_useremail as em ON em.user_id =
usr.id GROUP BY usr.id HAVING Max(em.added)')

That shall be good enough for me, it seems to be Sqlite3 and MySQL
compatible SQL (which is just right for my app)

On Jan 16, 11:53 am, Jari Pennanen <jari.penna...@gmail.com> wrote:
> Hi!
>
> Suppose following model:
>
> class UserEmail(models.Model):
>     """User email"""
>
>     user = models.ForeignKey(User, db_index=True,
>                              null=True, blank=True, editable=False)
>     """User recieving the email"""
>
>     added = models.DateTimeField(_("added"), auto_now_add=True)
>     """Added to database"""
>
>     subject = models.CharField(_('subject'), max_length=128)
>     """Subject"""
>
>     message = models.TextField(_('message'))
>     """Message"""
>
> How can I retrieve list of users and their latest email subject?
>
> This almost works:
>
> User.objects.all()\
>   .annotate(latest_email_added=Max('useremail__added'))
>
> But it does not give me the other fields of latest email such as
> subject, so I try to add other fields using extra:
> User.objects.all()\
>   .annotate(latest_email_added=Max('useremail__added'))\
>   .extra(select={'email_subject' : 'myapp_useremail.subject'})
>
> Suddenly it adds a GROUP BY to the query with a long list of fields
> that should not be there, which breaks everything, now I get multiple
> rows per user which is not wanted.
>
> If I try to modify the group_by manually, like this:
> a = User.objects.all()\
>   .annotate(latest_email_added=Max('useremail__added'))\
>   .extra(select={'email_subject' : 'dmusic_useremail.subject'});
> a.query.group_by = ;
> print a.query
>
> There is still one extra field in group by making it break:
> ... GROUP BY "auth_user"."id", (dmusic_useremail.subject)
>
> Can someone elaborate this behavior?
>
> Any help is appreciated, thanks!

--
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 athttp://groups.google.com/group/django-users?hl=en.

Offline

  • Root
  • » Django
  • » GROUP BY fields appearance, bug or feature? [RSS Feed]

Board footer

Moderator control

Enjoy the 19th of August
PoweredBy

The Forums are managed by develissimo stuff members, if you find any issues or misplaced content please help us to fix it. Thank you! Tell us via Contact Options
Leave a Message
Welcome to Develissimo Live Support