Django: How to filter on inner join based on properties of second table?

Question

My question is simple: in a Django app, I have a table Users and a table StatusUpdates. In the StatusUpdates table, I have a column user which is a foreign key pointing back to Users. How can I do a search expressing something like:

users.filter(latest_status_update.text__contains='Hello')

Edit:

Please excuse my lack of clarity. The query that I would like to make is something like "Give me all the users whose latest status update contains the text 'hello'". In Django code, I would do the following (which is really inefficient and ugly):

hello_users = []
for user in User.objects.all():
  latest_status_update = StatusUpdate.objects.filter(user=user).order_by('-creation_date')[0]
  if latest_status_update.text.contains('Hello'):
    hello_users.append(user)

return hello_users

Edit 2:

I've already found the solution but since I was asked, here are the important parts of my models:

class User(models.Model):
  ...

class StatusUpdate(models.Model):
  user = models.ForeignKey(User)
  text = models.CharField(max_length=140)
  creation_date = models.DateTimeField(auto_now_add=True, editable=False)
  ....

daveslab · Accepted Answer · 2016-10-29T06:50:46.867

Okay, I think I got it:

from django.db.models import Max, F

User.objects\
  .annotate(latest_status_update_id=Max('statusupdate__id'))\
  .filter(
    statusupdate__id=F('latest_status_update_id'), 
    statusupdate__text__icontains='hello'
  )

For more info, see this section of the Django documentation.

Please note: I ended up changing my strategy a bit and settling for the strategy where the highest ID means the latest update. This is the case because I realized that a User could post two updates the same time and that would break my query.

score 0 · Answer 2 · edited Jun 18 '20 at 22:10

Not sure I understand, are you trying to do something like

(StatusUpdates
    .objects
    .select_related("user")
    .filter(text__contains = "hello")
    .order_by("-updated")
    .first())

This will return the StatusUpdate that was modified last (if you have a field called updated that stores the time of the last modification) which contains "Hello" in the text field. If none of the StatusUpdates contains that string, it will return None.

Then you can do:

latest = (StatusUpdates
    .objects
    .select_related("user")
    .filter(text__contains = "hello")
    .order_by("-updated")
    .first())
#then if you needed the user too
if latest is not None:
    user = latest.user #which does not call the DB again since you selected related`

If this isn't what you needed, please provide more details (models) and clarify your need

Thanks for your answer. I updated the question to be more clear. — daveslab, Oct 26 '16 at 07:24

Apoorv Kansal · Answer 3 · 2016-10-28T14:11:26.193

0

latest_status_updates = filter(lambda x: x.text.contains('hello'),
    [
        user.statusupdates_set.order_by('-creation_date').first() 
        for user in User.objects.all()
    ]
)
users = list(set([status_update.user for status_update in latest_status_updates]))

EDIT: Now I first get all LATEST status updates of each user into a list which is then filtered by the text field found in StatusUpdate class. In the second line, I extract users out of the filtered status updates and then produce a unique list of users.

I hope this helps!

edited Oct 28 '16 at 14:11

answered Oct 28 '16 at 13:17

Apoorv Kansal

3,210
6
27
37

Your query doesn't take into account the fact that I want to search by the **latest** status update **per user**. Furthermore, your list of users won't be unique. – daveslab Oct 28 '16 at 13:26
Please see the changes and let me know if there is a problem :) – Apoorv Kansal Oct 28 '16 at 14:13

Django: How to filter on inner join based on properties of second table?

3 Answers3

Linked