Django filter across multiple model relationships

The short answer is: you can't do this as described because the @property function is set via python after the query has been executed, ie, it uses the returned query as data for the function.

Also, your property makes an sql call for each agent_name. Not a problem for one agent, but looping through 50 will be inefficient.

It seems likely there is a relationship between orders and assignments, so you could connect them via a foreign key, much as you have with orders and customers. Then you annotate a new field into your queryset

from django.db.models.functions import Concat
from django.db.models import Value


com = request.GET.get('name')
if com != '' and com is not None:
    #start definition of orders with ( so we can break up the line for readability
    orders = (
        Order.objects.annotate(
            #Combine firstname, space, and last name into new field
            full_name = Concat(
                'order__assignment__sales_agent__name',
                 Value(' '),
                 'order__assignment__sales_agent__surname'
             )
         )
         #filter on our annotated field from the form
         .filter(full_name__icontains=com)
         .order_by('-request_date')
     )

I'm not sure about the relationship between Order and Assignment, but I confirmed that one Order and Assignment are connected through agent_name property.

It's something that you need to modify through the exact relationship, but for now, I connected the order with Foreignkey to the Assignment model for testing.

from django.db import models

# Create your models here.
class Order (models.Model):
  customer = models.ForeignKey("Customer", on_delete=models.RESTRICT)
  request_date = models.DateField()
  price = models.DecimalField(max_digits=10, decimal_places=2)

class Company (models.Model):
  name = models.CharField(max_length=24)

class Customer (Company):
  pass

class Assignment (models.Model):
  assig_year = models.PositiveSmallIntegerField()
  customer = models.ForeignKey("Customer", on_delete=models.CASCADE)
  sales_agent = models.ForeignKey("Agent", on_delete=models.CASCADE)
  order = models.ForeignKey("Order", on_delete=models.CASCADE, related_name="assignment")

class Employee (models.Model):
  name = models.CharField(max_length=32)
  surname = models.CharField(max_length=32)

class Agent (Employee):
  pass

Your agent_name constitutes the agent_name value through the name, surname field of the object that meets the conditions of the Assignment object.

    @property
    def agent_name(self):
        assignment = Assignment.objects.get(assig_year = self.request_date.year, customer = self.customer)
        if assignment is not None:
            return assignment.sales_agent.name + ' ' + assignment.sales_agent.surname
        else:
            return 'ERROR'

django's filter must have a field used as filter on an object that exists inside Queryset.

That's why I added agent_name using the annotate method and then combined the values of the name and surname fields through Concat.

from django.template import loader
from django.db.models import Q, Value
from django.db.models.functions import Concat
from django.shortcuts import HttpResponse
from .models import Order

# Create your views here.
def GetOrders(request):
    orders = Order.objects.annotate(
        agent_name=Concat('assignment__sales_agent__name', Value(' '), 'assignment__sales_agent__surname')
    ).order_by('-request_date')

    com = request.GET.get('name')
    if com != '' and com is not None:
        orders = orders.filter(Q(agent_name__icontains=com))
    
    template = loader.get_template('orders.html')
    context = {
        'orders' : orders,
    }
    return HttpResponse(template.render(context,request))

In the above state, filtering is applied through the name + surname value.

Before applying filtering

After applying filtering

Check the url query string part.

class Assignment (models.Model):
    assig_year = models.PositiveSmallIntegerField()
    customer = models.ForeignKey("Customer", on_delete=models.CASCADE)
    sales_agent = models.ForeignKey("Agent", on_delete=models.CASCADE)

    class Meta:
        #unique key year + customer
        constraints = [
            UniqueConstraint(
                fields=['assig_year', 'customer'], name='Primary_Key_Assignment'
            )
        ]

class Order (models.Model):
    assignment = models.ForeignKey(Assignment, on_delete=models.RESTRICT, related_name="orders")
    request_date = models.DateField()
    price = models.DecimalField(max_digits=10, decimal_places=2)

The reason being:

In the comments, you were worried about data duplication and ER loops. But you already had ER spaghetti and duplication by having a customer connection on both Order and Assignment. While there's nothing wrong with that, it's also somewhat redundant given the constraint that the same sales agent will handle all the customer's orders.

With the above proposed change, we remove the customer FK from Order and instead add an FK Assignment, and we keep the FK from Assignment to Customer. Data duplication is eliminated and ER spaghetti is eliminated (since the dependency chain is now linear):

Order -> Assignment -> Customer

Additionally, the view you need can now be syntactically much simpler:

def GetOrders(request):
    com = request.GET.get('name')
    if com != '' and com is not None:

        # This is the slightly more expensive but maybe more readable version:
        assignments = Assignment.objects.filter(sales_agent=com)
        orders = Orders.objects.filter(assignment__in=assignments)

        # I haven't verified this attempt at a DB optimized version, but I think it's on par:
        orders = Order.objects.select_related('assignment').filter(assignment__sales_agent=com)
    else:
        return Order.objects.none() # Or however you want to handle the case of there being no assignments/orders for a given sales agent

    template = loader.get_template('orders.html')
    context = {
        'orders' : orders,
    }
    return HttpResponse(template.render(context,request))

As a bonus, if you ever need a view to see orders per year, for example, you get that for free now, simply invoke assignment.orders. Which works for both sales agents and customers, as both of those entities use Assignment as the middle man.

You should use a GeneratedField for the agent_name field:

from django.db.models import Value as V
from django.db.models.functions import Concat

class Employee (models.Model):
    name = models.CharField(max_length=32)
    surname = models.CharField(max_length=32)

    agent_name = models.GeneratedField(
        expression = Concat('name', V(' '), 'surname'),
        output_field = models.CharField(max_length = 32 * 2),
        db_persist = True
    )

Now in your view you can filter on agent_name like you would any other field:

def get_orders(request):

    if com := request.GET.get('name'):
        assignment = Assignment.objects.get(agent_name__icontains=com)
        orders = Order.objects.filter(
            customer=assignment.customer, 
            request_date__year=assignment.assig_year,
        )

    else:
        raise Exception('no name')

    context = { 'orders' : orders }
    return render(request, context)

I ran into a similar issue before, and I think I can help. The problem is that you can't filter on agent_name because it's a property and not a real field in your database. Also, using a property that queries the database for each order can be inefficient.

One way to solve this is to annotate your Order queryset with the agent's name using a Subquery. This allows you to filter directly on agent_name. Here's how you might do it:

First, import the necessary functions at the top of your views file:

from django.db.models import Subquery, OuterRef, Value
from django.db.models.functions import Concat, ExtractYear

Then, modify your GetOrders view like this:

def GetOrders(request):
    # Subquery to get the agent's full name for each order
    assignments = Assignment.objects.filter(
        customer=OuterRef('customer'),
        assig_year=ExtractYear(OuterRef('request_date'))
    ).annotate(
        agent_full_name=Concat('sales_agent__name', Value(' '), 'sales_agent__surname')
    )

    # Annotate the orders with the agent's name
    orders = Order.objects.annotate(
        agent_name=Subquery(assignments.values('agent_full_name')[:1])
    )

    # Apply the filter if a name is provided
    com = request.GET.get('name')
    if com:
        orders = orders.filter(agent_name__icontains=com)

    orders = orders.order_by('-request_date')

    context = {'orders': orders}
    return render(request, 'orders.html', context)

This way, you're adding an agent_name field to each Order object in your queryset, which you can then filter on. It also keeps your database queries efficient.

In your template, you can still use {{ x.agent_name }} as before.

I hope this helps!

I personally suggest the way proposed in @Vegard's answer to achieve a simpler and less messy solution and I find it more logical, but if you want to proceed with the way you defined models, you can annotate to each order the agent name who is responsible for the assignment that:

1. Its customer is the same as the order's customer.
2. Its 'assig_year' is equal to the order's request_date's year.

To do this I think it will be better to use Coalesce function to return a placeholder at least (because there might be no assignment for an order), so the query goes like:

from django.db.models import OuterRef, Subquery, F, Value, CharField, Concat
from django.db.models.functions import Coalesce

# Now, orders_with_agent will contain Orders annotated with agent_name or 'No agent' if no agent was found.
orders_with_agent = Order.objects.annotate(
    agent_name=Coalesce(
        Subquery(
            Assignment.objects.filter(
                customer=OuterRef('customer'),
                assig_year=OuterRef('request_date__year')
            ).values(
                # Here we concatenate the agent's name and surname
                full_name=Concat(
                    F('sales_agent__name'),
                    Value(' '),
                    F('sales_agent__surname'),
                    output_field=CharField()
                )
            )[:1],  # Get the first matching assignment
            output_field=CharField()
        ),
        Value('No Agent')  # If no matching assignment, return 'No Agent'
    )
)

And note that as we know customer and year are unique together in each assignment, the subquery will contain on record.