I have a Ticket booking model
class Movie(models.Model): name = models.CharField(max_length=254, unique=True) class Show(models.Model): day = models.ForeignKey(Day) time = models.TimeField(choices=CHOICE_TIME) movie = models.ForeignKey(Movie) class MovieTicket(models.Model): show = models.ForeignKey(Show) user = models.ForeignKey(User) booked_at = models.DateTimeField(default=timezone.now) I would like to filter MovieTicket with its user field and group them according to its show field, and order them by the recent booked time. And respond back with json data using Django rest framework like this:
[ { show: 4, movie: "Lion king", time: "07:00 pm", day: "23 Apr 2017", total_tickets = 2 }, { show: 7, movie: "Gone girl", time: "02:30 pm", day: "23 Apr 2017", total_tickets = 1 } ] I tried this way:
>>> MovieTicket.objects.filter(user=23).order_by('-booked_at').values('show').annotate(total_tickets=Count('show')) <QuerySet [{'total_tickets': 1, 'show': 4}, {'total_tickets': 1, 'show': 4}, {'total_tickets': 1, 'show': 7}]> But its not grouping according to the show. Also how can I add other related fields (i.e., show__movie__name, show__day__date, show__time)
2 Answers
Answers 1
You have to group by show and then count the total number of movie tickets.
MovieTicket.objects.filter(user=23).values('show').annotate(total_tickets=Count('show')).values('show', 'total_tickets', 'show__movie__name', 'show__time', 'show__day__date')) Use this serilizer class for the above queryset. It will give the required json output.
class MySerializer(serializers.Serializer): show = serailizer.IntegerField() movie = serializer.StringField(source='show__movie__name') time = serializer.TimeField(source='show__time') day = serializer.DateField(source='show__day__date') total_tickets = serializer.IntegerField() It is not possible to order_by booked_at since that information gets lost when we group by show. If we order by booked_at group by will happen on unique booked_at times and show ids and that is why the ticket count was coming 1. Without order_by you will get correct count.
EDIT:
use this query:
queryset = (MovieTicket.objects.filter(user=23) .order_by('booked_at').values('show') .annotate(total_tickets=Count('show')) .values('show', 'total_tickets', 'show__movie__name', 'show__time', 'show__day__date'))) You cannot annotate on an annotated field. So you will to find the total tickets count in python. To calculate total_tickets count for unique show ids:
tickets = {} for obj in queryset: if obj['show'] not in tickets.keys(): tickets[obj['show']] = obj else: tickets[obj['show']]['total_tickets'] += obj['total_tickets'] the final list of objects you need is tickets.values()
The same serializer above can be used with these objects.
Answers 2
I would like to filter MovieTicket with its user field and group them according to its show field, and order them by the recent booked time.
This queryset will give you exactly what you want:
tickets = (MovieTicket.objects .filter(user=request.user) .values('show') .annotate(last_booking=Max('booked_at')) .order_by('-last_booking') ) And respond back with json data using Django rest framework like this: [ { show: 4, movie: "Lion king", time: "07:00 pm", day: "23 Apr 2017", total_tickets = 2 }, { show: 7, movie: "Gone girl", time: "02:30 pm", day: "23 Apr 2017", total_tickets = 1 } ]
Well this json data is not the same as the query you described. You can add total_tickets by extending the annotation and show__movie__name into the .values clause: this will change the grouping to show+movie_name, but since show only has one movie_name it wont matter.
However, you cannot add show__day__date and show__time, because one show have multiple date-times, so which one would you want from a group? You could for example fetch the maximum day and time but this does not guarantee you that at this day+time there will be a show, because these are different fields, not related by each other. So the final attempt may look like:
tickets = (MovieTicket.objects .filter(user=request.user) .values('show', 'show__movie__name') .annotate( last_booking=Max('booked_at'), total_tickets=Count('pk'), last_day=Max('show__day'), last_time=Max('show__time'), ) .order_by('-last_booking') ) 
0 comments:
Post a Comment