GROUP BY fields appearance, bug or feature?

[Jari Pennanen]

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!

[Praveen Krishna R]

First of all did you try accessing the email subject with a dotted notation?

try

u = User.objects.all()

and 

u[0].subject

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

--

Praveen Krishna R

[Jari Pennanen]

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[0].subject
>

> > django-users...@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*

[Jari Pennanen]

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)