Export xlsx file with model having reverse foreign key relation and make that reverse foreign key relation as separate column

Question

I am using django import_export package for export my data into xlsx file.

I am having issue while exporting the data to excel in the format I need.

models.py

class Fans(models.Model):
    """
        Model for survey answering people
    """
    fan_id = models.AutoField(db_column='FAN_ID', primary_key=True)
    first_name = models.CharField(
        db_column='FIRST_NAME', max_length=45, blank=True, null=True)
    last_name = models.CharField(
        db_column='LAST_NAME', max_length=45, blank=True, null=True)
    phone = models.CharField(
        db_column='PHONE', max_length=45, blank=True, null=True)
    email = models.CharField(
        db_column='EMAIL', max_length=45, blank=True, null=True)
    gender = models.CharField(
        db_column='GENDER', max_length=45, blank=True, null=True)

class Responses(models.Model):
    """
        Model for responses given by fans
    """
    survey = models.ForeignKey(
        Surveys, on_delete=models.CASCADE, db_column='SURVEY_ID', related_query_name="part")
    fan = models.ForeignKey(Fans, on_delete=models.CASCADE,
                            db_column='FAN_ID', related_query_name="given", related_name="given")
    survey_question = models.ForeignKey(
        SurveyQuestions, on_delete=models.DO_NOTHING, db_column='SURVEY_QUESTION_ID',
        related_query_name="response")
    response = models.CharField(
        db_column='RESPONSE', max_length=255, blank=True, null=True)
    correct_answer = models.IntegerField(
        db_column='CORRECT_ANSWER', blank=True, null=True)
    load_id = models.IntegerField(db_column='LOAD_ID', blank=True, null=True)

class SurveyQuestions(models.Model):
    """
        Model for surveys questions
    """
    survey = models.ForeignKey(Surveys, on_delete=models.CASCADE,
                               db_column='SURVEY_ID', related_query_name="question")
    survey_question_id = models.AutoField(
        db_column='SURVEY_QUESTION_ID', primary_key=True)
    survey_question_name = models.CharField(
        db_column='SURVEY_QUESTION_NAME', max_length=255)
    question = models.CharField(
        db_column='QUESTION', max_length=255, blank=True, null=True)
    response_type = models.CharField(
        db_column='RESPONSE_TYPE', max_length=255, blank=True, null=True)
    load_date = models.DateField(db_column='LOAD_DATE', auto_now_add=True)

I want to export data of the fans with recorded responses in the following format:

first_name, last_name, phone, email, question1, question2, question3
abc, xyz, 1234566780, [email protected], response1, response2, response3

Here, the first four fields are directly from Fans model, however the last three column headers represent "question" field from SurveyQuestions model and values come from the "response" field of Responses model.

Till now, I am able to achieve the following format:

first_name, last_name, phone, email, given
abc, xyz, 1234566780, [email protected], {question1: response1, question2: response2, question3: response3}

given field is json of question-response as key-value pair.

admin.py

class FanResource(resources.ModelResource):

    """
        Resource for exporting to excel
    """

    given = fields.Field()

    class Meta:
        model = Fans
        fields = ("first_name", "last_name", "email",
                  "phone", "given")

    def dehydrate_given(self, instance):
        res = {}
        for x in instance.given.values('response', 'survey_question__question'):
            res[x['survey_question__question']] = x['response']
        return json.dumps(res)

Any help would be appreciated. Thanks in advance!!

14/10/20 UPDATE

Using the answer below, I was able to achieve the format required. Code is as follows:

def after_export(self, queryset, data, *args, **kwargs):
        survey_questions = {x["survey_question_id"]: x["question"] for x in SurveyQuestions.objects.filter(
            survey=self.survey).values('survey_question_id', 'question')}
        for k, v in survey_questions.items():
            res = []
            for x in queryset:
                try:
                    res.append(x.given.get(survey_question=k).response)
                except ObjectDoesNotExist:
                    res.append(None)
            data.append_col(res, header=v)

Now, the issue is that it is taking too long as it is hitting the database for each entry. And other issue is order is not proper (i.e responses are not in same line as per the corresponding fans).

Answer 1

I think the way to achieve this would be to override after_export(), and manipulate the exported dataset. For example:

    def after_export(self, queryset, data, *args, **kwargs):
        response1 = [i for i in range(data.height)]
        data.append_col(response1, header="response1")
        response2 = [i for i in range(data.height)]
        data.append_col(response2, header="response2")

This will append new columns to the end of the export. In after_export() you will have access to both the data set and the queryset, so hopefully you can manipulate this data to fill the 'response' columns correctly.