Django filter that also grabs a reverse ForeignKey, or better way to combine associated database info?

user_11420295 0
Viewed 31

I am working on some backend django work that requires me to grab an Employee by filtering, but I also need to grab the EmployeeAddress object that is associated to the Employee. I was wondering if this was possible within a single query. I need the employees address, and employee info to be in a combined single dictionary, to access on the front end side with JS.

I have models as such,

Class Employee(models.model):
    first_name
    last_name
    email
    

Class EmployeeAddress(models.model):
    employee = models.ForeignKey(Employee):
    street
    city
    state

I have a view, that kinda does the job, but having trouble merging merging the two separate QuerySets into a single listed dictionary with all values.

I was hoping there was just a way to get the EmployeeAddress without even writing the second query, and just grabbing that associated data in the first employee_list query?

def employee_ajax_list(request):
    email = request.GET.get('email', None)
    employee_list = Employee.objects.filter(email=email)
    employee_address = EmployeeAddress.objects.filter(employee_id__in=employee_list).values(
        'street', 'city', 'state',)
    # this chain kinda works, but splits them into 2 separate dictionaries?
    employee_info = list(chain(employee_list.values(), employee_address))
    data = {
        'employee_list': employee_info
    }
    return JsonResponse(data)

Just looking on some advice to make this work a little smoother!

django-queryset django-views django-models django
2 Answers

Change this line

employee = models.ForeignKey(Employee)

to

employee = models.ForeignKey(Employee, related_name="address")

That will let you access an employee's address by doing employee.address in regular code or employee__address in queries.

For example:

Employee.objects
    .filter(email=email)
    .exclude(address=None)
    .values(
        'email', 
        'name', 
        'address__street', 
        'address__city', 
        'address__state'
    )

(The .exclude() clause is in case someone doesn't have an address set.)

That should output something like:

<QuerySet [{'email': 'johnsmith@example.com', 
'name': 'John Smith', 'address__street': 
'123 Maple St', 'address__city': 'Springfield', 
'address__state': 'MO'}]>
user_4189290

Maybe something like this should be a bit better and in a single query:

employee_info = EmployeeAddress.objects.filter(employee__email=email).values("employee__email", "employee__<another_field>", "street", "city", "state")

data = {
    'employee_list': employee_info
}

A best way of doing it should be with DRF serializers:

class EmployeeAddressSerializer(serializers.ModelSerializer):
    class Meta:
        fields = "__all__"
        model = Employee


class EmployeeSerializer(serializers.ModelSerializer):
    # you should add related_name="addresses" in the
    # foreignkey if you want this works.
    addresses = EmployeeAddressSerializer(many=True)

    class Meta:
        fields = "__all__"
        model = Employee

Then to use:

employee = Employee.objects.filter(email=email).last()
return JsonResponse(EmployeeSerializer(employee).data)
user_20367670
Related