Django orm latest data-CodePudding

reference

class Board(model.Model):
    master = models.ForeignKey(User, on_delete=models.CASCADE)
    member = models.ForeignKey(User, on_delete=models.CASCADE)
    status = models.PositiveIntegerField(default=0)

class MSG(models.Model):
    Board= models.ForeignKey(Board, on_delete=models.CASCADE)
    writer = models.ForeignKey(User, on_delete=models.CASCADE)
    text = models.TextField()
    created_at = models.DateTimeField(auto_now_add=True, null=True)

How can I get the following values from this relationship?

i'm using PostgreSQL

response = {
    master="USER1",
    member="USER2",
    status=0,
    master_latest_MSG="master last_msg"
    master_latest_date="2020.03.21",
    member_latest_MSG="member last_msg"
    member_latest_date="2020.03.22",
}

Is there any better way instead of subquery?

NEW

The latest MSG of all boards, information of the board is required.

response = [
   {
        master="USER1",
        member="USER2",
        status=0,
        master_latest_MSG="master last_msg"
        master_latest_date="2020.03.21",
        member_latest_MSG="member last_msg"
        member_latest_date="2020.03.22",
   },
   {
        master="USER3",
        member="USER4",
        status=0,
        master_latest_MSG="master last_msg"
        master_latest_date="2020.03.23",
        member_latest_MSG="member last_msg"
        member_latest_date="2020.03.23",
   },
]

CodePudding user response：

Edited because "new" Q section

In a readable but not performance optimal approach, you can use latest and F expressions to get data for a board:

from django.db.models import F

def GetBoardData(board):

  data = (
    MSG
    .objects
    .filter(board = board)
  )

  latest_master = (
    data
    .filter(writer=F('board__master')) #<--- using F
    .latest('created_at')              #<--- using latest 
  )

  latest_member = (
    data
    .filter(writer=F('board__member'))
    .latest('created_at')
  )
  
  # be carefull, latest_member and latest_master can be None
  return {
    master=latest_master.writer.username,
    member=latest_member.writer.username,
    status=board.status,
    master_latest_MSG=latest_master.text
    master_latest_date=latest_master.created_at,
    member_latest_MSG=latest_member.text
    member_latest_date=latest_member.created_at,
  }

And call function for each board:


response = [
   GetBoardData(board)
   for board in Board.objects.all()
]

For performance optimal approach, use window functions.

Be free to format dates as you need.