Filtering related_name field, in django

Question

I am trying to filter related_name field with a query if it's possible. I serialize the tables.

class ProjectsSerializers(serializers.ModelSerializer):
    class Meta:
        model = Projects
        fields = ["id", "project_id", "project_name", "tasks"]
        depth = 1

Above in the fields list. tasks is the related_table so in the tasks table I have task_created_at field and when I retrieve the projects table. I want to filter it by that field.

def get_projects(request):
  projects = Projects.objects.filter(task_created_at__startswith="2020-04")
  serializer = ProjectsSerializers(projects, many=True)
  return Response(serializer.data)

Of course this: task_created_at__startswith="2020-04" isn't working. Because task_created_at is not in the projects. it's in the related_name which is tasks. So is there a way to filter tasks with getting projects

and the relevent models:

class Projects(models.Model):

    project_id = models.CharField(max_length=255)
    project_name = models.CharField(max_length=255)  

    def __str__(self):
        return self.project_name

    class Meta:
        db_table = "projects"

class Tasks(models.Model):

    projects = models.ForeignKey(Projects, on_delete=models.CASCADE, related_name='tasks', blank=True, null=True)
    task_price = models.IntegerField(blank=True, null=True)
    task_total_price = models.IntegerField(blank=True, null=True)
    task_created_at = models.CharField(max_length=255, blank=True, null=True)

    def __str__(self):
        return self.ad_kind

    class Meta:
        db_table = "tasks"

the example data:

[
    {
        "id": 6,
        "order_id": null,
        "project_id": "23123",
        "project_name": "プレサンス グラン 茨木駅前",
        "company_name": null,
        "analytics_id": null,
        "advertisements": [],
        "tasks": [
            {
                "id": 1,
                "task_total_price": null,
                "task_created_at": "2020-04-02",
                "task_due_date": "2020-04-07",
                "task_modified_at": "2020-04-07T06:42:41.447Z",
                "projects": 6
            },
            {
                "id": 2,
                "task_total_price": null,
                "task_created_at": "2020-02-02",
                "task_due_date": "2020-03-07",
                "task_modified_at": "2020-04-07T06:42:41.447Z",
                "projects": 6
            },
        ]
    }
]

Answer 1

You can JOIN two tables/models by __ (a double underscore).

from django.db.models import Prefetch


def get_projects(request):
    projects = Projects.objects.prefetch_related(
        Prefetch('tasks', queryset=Tasks.objects.filter(task_created_at__istartswith='2020-04'))
    ).filter(tasks__task_created_at__istartswith='2020-04')

    serializer = ProjectsSerializers(projects, many=True)
    return Response(serializer.data))

Reference

• Lookups that span relationships
• Prefetch(...)

Answer 2

You will need to use a subquery:

def get_projects(request):

    project_ids = Task.objects.filter(
      task_created_at__startswith='2020-04').values('project_id')
    projects = Project.objects.filter(id__in=project_ids)

    serializer = ProjectsSerializers(projects, many=True)
    return Response(serializer.data)

Also, please don't use plural table names ;-)

Lastly, I'd recommend using a DateTimeField for storing dates, instead of using a CharField.

Answer 3

To be able to dynamically change the representation of an object, you can modify the serializer. One way to filter the tasks is to provide a serializers.SerializerMethodField that filters the tasks dynamically.

class TaskSerializer(serializers.ModelSerializer):
    class Meta:
        model  = Task
        fields = [ ... your fields ... ]


class ProjectSerializer(serializers.ModelSerializer):
    filtered_tasks = serializers.SerializerMethodField()

    class Meta:
        model  = Project
        fields = ["id", "project_id", "project_name", "filtered_tasks"]
        depth  = 1

    def get_filtered_tasks(self, obj):
        tasks = Task.objects.filter(created_at__startswith="2020-04")
        return TaskSerializer(tasks, many=True).data

However, I don't like this approach. I think you should reconsider how you present the data. It feels like your binding the data too hard together. Although, I don't know your use-case so I cannot come with any concrete suggestions.

Also, I've changed the names here so the models are singular, as it's the convention.