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 <[email protected]> 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 = [('auth_user', 'id')];
> 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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
