[Python] How do I filter across multiple model relationships?

My models:

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)

@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'

class Company (models.Model):
pass

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)

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

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

class Agent (Employee):
pass


One assignment relates each customer to a sales agent for a given year. Each customer may have several orders along the year and the agent assigned to the customer is accountable for serving all of them. In one of my views I am displaying all orders by listing their corresponding sales agent, customer, date and price:

def GetOrders(request):
orders = Order.objects.order_by('-request_date')

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


orders.html:

<!DOCTYPE html>
<html>
<head>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
<main>
<table>
<thead>
<th>Agent</th>
<th>Customer</th>
<th>Date</th>
<th>Price</th>
</thead>
<tbody>
{% for x in orders %}
<td>{{ x.agent_name }}</td>
<td>{{ x.customer.name }}</td>
<td>{{ x.request_date }}</td>
<td>{{ x.price }}</td>
</tr>
{% endfor %}
</tbody>
</table>
</main>
</body>
</html>


I want to add some filtering capability to select the sales agent I'm interested in. I don't know how to deal with relationships to check the sales agent. I tried the agent_name property:

<!DOCTYPE html>
<html>
<head>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
<main>
<div class="filters">
<form action="" method="GET">
<div class="row">
<div class="col-xl-3">
<label>Agent:</label>
<input type="text" class="form-control" placeholder="Name" name="name" {% if name %} value = "{{ name }}" {% endif %}>
</div>
<div class="col-xl-2" style="padding-top: 2%;">
<button type="submit" class="btn custom-btn">Filter</button>
</div>
</div>
</form>
</div>
<p/>
<table>
<thead>
<th>Agent</th>
<th>Customer</th>
<th>Date</th>
<th>Price</th>
</thead>
<tbody>
{% for x in orders %}
<td>{{ x.agent_name }}</td>
<td>{{ x.customer.name }}</td>
<td>{{ x.request_date }}</td>
<td>{{ x.price }}</td>
</tr>
{% endfor %}
</tbody>
</table>
</main>
</body>
</html>


My view turns to:

def GetOrders(request):
orders = Order.objects.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))


But I cannot use it as a filter criterium because it is not a real model field and I get a FieldError:

Cannot resolve keyword 'agent_name' into field
​

Continue reading...