How to create a separate column for each related field value while exporting data via django-import-export

Question

I have a PaymentRecord model which stores user payments for event tickets. I have a TicketPurchase model which records which tickets were purchased by which PaymentRecord. So my models look something like this -

class Event(models.Model):
    name = models.CharField(max_length=128)

class PaymentRecord(models.Model):
    event = models.ForeignKey(Event, on_delete=models.SET_NULL, null=True, related_name='payments')
    payment_time = models.DateTimeField(auto_add_now=True)
    # various payment details and user identifiers

class TicketPurchase(models.Model):
    payment_record = models.ForeignKey(PaymentRecord, on_delete=models.CASCADE, related_name='tickets')
    tier_name = models.CharField(max_length=32)
    ticket_count = models.IntegerField()

Each event that I am selling tickets for has a few "tiers", like Silver, Gold, Platinum (Each event has different names for tiers). When I export all the PaymentRecord objects for a particular event, I want to somehow get a list of all the "tiers" that exist in the queryset being exported, and then have a separate column for each tier showing how many tickets for that tier were purchased by a payment.

So I want my output to be something like this -

Event Name	Payment Time	Silver Tickets	Gold Tickets	Platinum Tickets
Event 1	# time 1	3	0	0
Event 1	# time 2	1	1	1

How can I achieve this using django-import-export?

Right now I am using a ModelResource to export a summary of the different tiers purchased in a single column, as so -

class PaymentRecordsResource(resources.ModelResource):
    class Meta:
        model = PaymentRecord
    ticket_count = Field(column_name='Tickets')

    def dehydrate_ticket_count(self, obj):
        tickets = ''
        for ticket in obj.tickets.all():
            tickets += f'{ticket.tier_name}: {ticket.ticket_count}\n'
        if not tickets:
            tickets = '-'
        return tickets.strip()

However, I want to create a separate column for each tier. Is that possible?

Answer 1

In case you're in a hurry here is the resourse for your desired table.

from django.db.models import Sum

class PaymentRecordsResource(resource.ModelResource):
    class Meta:
        model = PaymentRecord
        fields = ('event__name', 'payment_time', 'silver_ticket_count', 'gold_ticket_count', 'platinum_ticket_count')
    silver_ticket_count = Field(column_name='Silver Tickets')
    gold_ticket_count = Field(column_name='Gold Tickets')
    platinum_ticket_count = Field(column_name='Platinum Tickets')


    def dehydrate_silver_ticket_count(self, obj):
        return self.get_ticket_count(obj, 'silver')

    def dehydrate_gold_ticket_count(self, obj):
        return self.get_ticket_count(obj, 'gold')

    def dehydrate_platinum_ticket_count(self, obj):
        return self.get_ticket_count(obj, 'platinum')

    def get_ticket_count(self, obj, tier_name, default=0):
        return obj.tickets.filter(tier_name=tier_name).aggregate(Sum('ticket_count'))['ticket_count__sum'] or default

---

This is how i got to this resource. First we need to get the fields we want:

silver_ticket_count = Field(column_name='Silver Tickets')
gold_ticket_count = Field(column_name='Gold Tickets')
platinum_ticket_count = Field(column_name='Platinum Tickets')

In the same step we can exclude the fields we don't want (like id or event_id) by specifying excactly the fields we want to display in the meta class

class Meta:
    model = PaymentRecord
    fields = ('event__name', 'payment_time', 'silver_ticket_count', 'gold_ticket_count', 'platinum_ticket_count')

payment_time points to the field of the model. silver_ticket_count points to the field we defined earlier in the resource and event__name points to the relationship of the model to its linked Event and its name. Read more about how this works in the django docs.

Then i set up a method to get the value of the field.

def dehydrate_silver_ticket_count(self, obj):
    return obj.tickets.filter(tier_name='platinum').aggregate(Sum('ticket_count'))['ticket_count__sum'] or 0

obj.tickets.filter(tier_name=''platinum') gets all tickets related to the PaymentRecord with the platinum tier. Aggregation is a somewhat complicated topic you can read about it here. For our purposes this means it sums up all tickets with the platinum tier on the database level and returns the value.

Because this lookup is basically the same for gold and platinum i decided to put it in it's own method.

Note: We are making three extra hits on the DB.

def get_ticket_count(self, obj, tier_name, default=0):
    return obj.tickets.filter(tier_name=tier_name).aggregate(Sum('ticket_count'))['ticket_count__sum'] or default

Now we need to populate the remaining methods to get the value for all tiers.

def dehydrate_silver_ticket_count(self, obj):
    return self.get_ticket_count(obj, 'silver')

def dehydrate_gold_ticket_count(self, obj):
    return self.get_ticket_count(obj, 'gold')

def dehydrate_platinum_ticket_count(self, obj):
    return self.get_ticket_count(obj, 'platinum')

---

If you only have theses three tiers of tickets and they never change i suggest you add choices to your ModelField to limit the value tier_name can have to only these three choices.