Dynamic Filtering

Using Django Using the ORM
1

Hello i have this model:

class TaskEmployeeHistory(models.Model):
    employee = models.ForeignKey(Employee, on_delete=models.CASCADE)
    client = models.ForeignKey(Client, on_delete=models.CASCADE, null=True, blank=True)
    project = models.ForeignKey(Project, on_delete=models.CASCADE, null=True, blank=True)

    task = models.CharField(max_length=150)
    task_reference = models.CharField(max_length=150, blank=True)

    status = models.CharField(max_length=50, blank=True)

    start_date = models.DateTimeField()
    end_date = models.DateTimeField()

    site = models.ForeignKey(Site, on_delete=models.CASCADE)
    department = models.ForeignKey(Department, on_delete=models.CASCADE)
    section = models.ForeignKey(Section, on_delete=models.CASCADE)
    position = models.ForeignKey(Position, on_delete=models.CASCADE)
    country = models.ForeignKey(Country, on_delete=models.CASCADE)

    time_spent = models.CharField(max_length=20)
    determination = models.CharField(max_length=250)

    table_type = models.ForeignKey(TaskProd, on_delete=models.CASCADE)
    task_value = models.JSONField(default=list, blank=True)

    def __str__(self):
        return str(self.employee.first_name + ' ' + self.employee.last_name)

Then i have to filter an employee history dynamically.
For example, I have this json response which are list of ids that a user configured.

{'employee': [1, 3], 'country': [1], 'site': [1, 2, 3], 'department': [], 'section': [], 'position': [], 'client': [], 'project': [1]}

As you can see, there are empty keys and this empty keys should be ignored.

I have here a little code snippet that works for just one key.

employees = request.data['employee']
serializers = list()

for employee in employees:
    task_history = TaskEmployeeHistory.objects.filter(
            employee=employee
        )
    serializer = TaskEmployeeHistorySerializer(task_history, many=True)

    serializers.append(serializer.data)

How can i make this dynamically?

The code below will not works because I am filtering a list of ids for every related foreign key.

filters = {}

for key, value in request.post.items():
    if key in ['filter1', 'filter2', 'filter3']:
        filters[key] = value

Test.objects.filter(**filters)

I am thinking if the code below will do, or if there is any more efficient way to do this.

TaskEmployeeHistory.objects.filter(
                  employee__in=[1, 4, 7],
                  site__in=[1, 2, 3],
                  project__in=[1]
)

Thank you!

2

I tried this solution, not sure if this is enough.

        append_in = '__in'

        fields = [
            'employee',
            'country',
            'site',
            'department',
            'section',
            'position',
            'client',
            'project'
        ]

        filters = dict()

        for key, data in request.data.items():
            if data:
                if key in fields:
                    filters[key + append_in] = data

        task_employee_history = TaskEmployeeHistory.objects.filter(**filters)
3

Looks good to me - my only suggestion would be to change your fields list to a dict, where the value is the key__in version. This saves the concatenation operation.

1 Like