Yblog

Feiye 的个人日记 WIKI

用户工具


ORM 分组查询的问题。。。

  正常思维查询
  SELECT m1.*
  FROM message_message m1
  JOIN (
      SELECT MAX(m2.id) AS last_id
      FROM message_message m2
      WHERE (m2.receiver_id = 64 OR m2.sender_id = 64)
      GROUP BY m2.receiver_id + m2.sender_id
  ) AS last_result ON (last_result.last_id = m1.id)
  优化查询
  SELECT m1.*
  FROM message_message m1
  LEFT JOIN message_message m2
      ON (m1.receiver_id = m2.receiver_id AND m1.id < m2.id)
  WHERE m2.id IS NULL AND (m1.receiver_id = 64 OR m1.sender_id = 64)
  下面用 Django ORM 实现 (无法解决自定义 JOIN 查询增加条件 m1.id < m2.id)
  table = Message._meta.db_table
  last_qs = Message.objects.filter(Q(receiver=user) | Q(sender=user))
  # 自定义 JOIN 查询
  alias = last_qs.query.join((table, table, 'receiver_id', 'receiver_id'), promote=True)
  last_qs = last_qs.extra(where=["%s.id IS NULL" % alias])
  
  两次查询
  sql = ""
  SELECT m1.id
  FROM message_message m1
  LEFT JOIN message_message m2
      ON (m1.receiver_id = m2.receiver_id AND m1.id < m2.id)
  WHERE m2.id IS NULL AND (m1.receiver_id = %s OR m1.sender_id = %s)
  ""
  params = [user.id, user.id]
  if type_:
      sql += " AND m1.type = %s"
      params.append(type_)
  if last_id:
      sql += " AND m1.id < %s"
      params.append(last_id)
  sql += " ORDER BY m1.id DESC LIMIT %s"
  params.append(count)
  ids = Message.objects.raw(sql, params)
  return Message.objects.filter(id__in=[i.pk for i in ids]).order_by('-id')
  
  
  
  def get_group_key(message):
  min_id = min(message.receiver_id, message.sender_id or 0)
  max_id = max(message.receiver_id, message.sender_id or 0)
  return (min_id + 100000000) * 1000000000 + max_id