Блог

У меня возникла путаница в том, как написать запрос django для получения моих данных. У меня есть 2 таблицы «билет» и «ticket_details«. Ниже приведена схема для них.

 Ticket(id, name, type, user) TicketDetails(ticket_id, message, created_time)  

Примечание. С одним идентификатором билета может быть связано несколько сообщений.

А ticket_id-это внешний ключ к таблице билетов.

Я хотел бы получить все столбцы из обеих таблиц, в которых для определенного идентификатора билета должно быть выбрано только последнее сообщение из таблицы TicketDetails.

 Example: Ticket id, name, type, user 1,install, application, usr1  TicketDetails ticket_id, message, creted_time 1, lt;message1gt;, 12:00 PM 1, lt;message2gt;, 04:00 PM 2, lt;message3gt;, 05:00 PM --gt;latest entry  Expected Output: id, name, type, user, message, created_time 1, install, application, usr1, lt;message3gt;, 05:00PM  

Заранее спасибо

Я сделал некоторые предположения о ваших моделях, вы не предоставили никаких:

 class Ticket(models.Model):  name = models.CharField(max_length=50)  type = models.CharField(max_length=50)  user = models.ForeignKey('auth.User', on_delete=models.CASCADE)   # Model names should NEVER end with "s" class TicketDetail(models.Model):  ticket = models.ForeignKey(Ticket, on_delete=models.CASCADE)  message = models.CharField(max_length=50)  created_time = models.DateTimeField(auto_now_add=True)  

У вас есть 2 варианта:

1. вы можете написать это на чистом sql, вы потеряете возможность фильтровать

    sql = """ SELECT ticket.id, ticket.name, ticket.type, ticket.user_id, detail.message  FROM {ticket} ticket  LEFT JOIN (  SELECT detail.ticket_id, detail.message   FROM {detail} detail  INNER JOIN (  SELECT MAX(id) id, ticket_id   FROM {detail}   GROUP BY ticket_id  ) detail_message ON detail.id = detail_message.id ) detail ON detail.ticket_id = ticket.id """.format(ticket=Ticket._meta.db_table, detail=TicketDetail._meta.db_table) tickets = Ticket.objects.raw(sql)  for ticket in tickets:  print(ticket.id, ticket.message)  

2. Напишите это в стиле «джанго»

    latest_messages = TicketDetail.objects.values('ticket_id').annotate(id=models.Max('id')).values('id') tickets = Ticket.objects.prefetch_related(models.Prefetch('ticketdetail_set', TicketDetail.objects.filter(id__in=latest_messages))).order_by('id')  for ticket in tickets:  print(ticket.id)  # this iteration will only ever yield 1 result.. or nothing.  for detail in ticket.ticketdetail_set.all():  print(detail.message)  

Вот тесты:

 # uses factoryboy and faker to fill in the data   class UserFactory(factory.django.DjangoModelFactory):  class Meta:  model = auth.models.User  django_get_or_create = ('username',)   first_name = fake.first_name()  last_name = fake.last_name()  email = factory.LazyAttribute(lambda obj: "{}.{}@gmail.com".format(obj.last_name, obj.first_name).lower())  username = factory.Sequence(lambda n: 'user'   str(n))   class SimpleTestCase(TestCase):    def setUp(self):  ticket1 = Ticket.objects.create(user=UserFactory(), type='A', name='Number 1')  TicketDetail.objects.create(ticket=ticket1, message='you wont see this')  TicketDetail.objects.create(ticket=ticket1, message='you wont see this either')  TicketDetail.objects.create(ticket=ticket1, message='YES!!')   ticket2 = Ticket.objects.create(user=UserFactory(), type='B', name='Number 2')  TicketDetail.objects.create(ticket=ticket2, message='you also wont see this')  TicketDetail.objects.create(ticket=ticket2, message='you also wont see this either')  TicketDetail.objects.create(ticket=ticket2, message='also YES!!')   def test_flatten_pure_sql(self):  sql = """  SELECT ticket.id, ticket.name, ticket.type, ticket.user_id, detail.message  FROM {ticket} ticket  LEFT JOIN (  SELECT detail.ticket_id, detail.message   FROM {detail} detail  INNER JOIN (  SELECT MAX(id) id, ticket_id   FROM {detail}   GROUP BY ticket_id  ) detail_message ON detail.id = detail_message.id  ) detail ON detail.ticket_id = ticket.id  """.format(ticket=Ticket._meta.db_table, detail=TicketDetail._meta.db_table)  self.assertEquals(['YES!!', 'also YES!!'], [x.message for x in Ticket.objects.raw(sql)])   def test_orm_way(self):  latest_messages = TicketDetail.objects.values('ticket_id').annotate(id=models.Max('id')).values('id')  tickets = Ticket.objects.prefetch_related(models.Prefetch('ticketdetail_set', TicketDetail.objects.filter(id__in=latest_messages))).order_by('id')  self.assertEquals(['Number 1', 'Number 2'], [x.name for x in tickets])  self.assertEquals(['YES!!'], [x.message for x in tickets[0].ticketdetail_set.all()])  self.assertEquals(['also YES!!'], [x.message for x in tickets[1].ticketdetail_set.all()])