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
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.