Selecting data from two level deep

Question

Here's my model design:

class Group(models.Model):
    name  = models.CharField(max_length=30)
    ...

class User(models.Model):
    name              = models.CharField(max_length=50)
    ...
    group             = models.ForeignKey(Group)

class Photo(models.Model):
    title                = models.CharField(max_length=100)
    ...
    user                 = models.ForeignKey(User)

How can I get a list of all groups, under each group 5 random photos which belong to users belonging to the group?

I tried:

g = Group.objects.all()

for group in g:
    r = Photo.objects.filter(user.group=group).order_by('?')[:5]

That is giving:

Django Version: 1.4.3
Exception Type: SyntaxError
Exception Value:    
keyword can't be an expression (views.py, line 37)
Exception Location: /PATH/python2.7/site-packages/django/utils/importlib.py in import_module, line 35
Python Executable:  /PATH/python
Python Version: 2.7.3

But even if my code worked, I don't believe it's the ideal way since it will generate too many queries.

Please help.

score 2 · Accepted Answer · edited May 23 '17 at 11:56

2

The issue is that you can't use user.groups as a keyword argument to the .filter(…) function.

Instead, use user__groups=group to span the user -> group relationship:

Photo.objects.filter(user__group=group).order_by('?')[:5]

The relevant documentation: https://docs.djangoproject.com/en/dev/topics/db/queries/#lookups-that-span-relationships

Unfortunately getting rid of the for-loop would be fair fairly tricky, as it would require querying for the top N rows from a GROUP BY query… So unless you've got a bunch of groups, the Python for-loop is probably the most straight forward way to do it.

Now, that said, if performance is important, you'll probably need to come up with a smarter solution because ordering by RANDOM() is very slow too…

edited May 23 '17 at 11:56

Community

1
1

answered Dec 25 '12 at 19:18

David Wolever

148,955
89
346
502

Thanks, that actually fixed the error but what about the too many queries part? – Adam Dec 25 '12 at 19:38
Oh, sorry — what do you mean by "too many queries"? That will (should) perform exactly one query: `SELECT * FROM photo JOIN user ON …, group ON … WHERE group.id = group_id ORDER BY RANDOM() LIMIT 5`, and you can see the SQL that would be generated using: `str(Photo.objets.filter(…).order_by(…)[:5])` – David Wolever Dec 25 '12 at 19:41
Oh, I see - you want to group by the group. In that case, see the updated answer. – David Wolever Dec 25 '12 at 19:42

Selecting data from two level deep

1 Answers1