Django 1.11 querysets: Get all objects (model instances) of a relationship set

Question

If have a model for companies. These companies have an attribute employment_set, because workers are assigned to companies via the employment relation. How can I query all employees for a given company? The model for the employements look like this:

class Employment(
    SoftDeletableModel,
    TimeStampedModel,
    models.Model
):
    company = models.ForeignKey(Company, on_delete=models.CASCADE)
    employee = models.ForeignKey(UserWorkerProfile)
    employed_by = models.ForeignKey(UserOwnerProfile)

I tried using company.employment_set.values("employee"), but this returns a strange activatorquery set. Is there a way to return the normal queryset? Or is values() already the correct method?

Edit: To eloborate a little more: I want to end up with a queryset containing all the UserWorkerprofile model instances.

In the documentation for values() it says:

Returns a QuerySet that returns dictionaries, rather than model instances, when used as an iterable.

and I want exactly a queryset of model instances.

Well like you say, the `employment_set`. This is a manager, so you can for example write `some_company.employment_set.all()` to obtain all the `Employment`s. This is like every QuerySet: you can `.filter(..)`, etc. — Willem Van Onsem, Jun 11 '18 at 20:54
@willem-van-onsem Yeah but then I have all the employment model instances. I elaborated the question further. I want to end up with a queryset of all the employees, not the employments. — J. Hesters, Jun 11 '18 at 20:58
aah, that's `UserWorkerProfile.objects.filter(employment__company=some_company)`. — Willem Van Onsem, Jun 11 '18 at 20:58
@willem-van-onsem Thank you this already helps a lot! Is this the only way to do it in reverse so? From the company, more like I described it above? — J. Hesters, Jun 11 '18 at 20:59
Not as far as I know, you can not query a relation of an object manager as far as I know (afaik, there does *not* exists something like `some_company.employment_set.userworkerprofile_set`). — Willem Van Onsem, Jun 11 '18 at 21:01

Willem Van Onsem · Accepted Answer · 2018-06-12T08:33:53.007

Based on your comment, you want all UserWorkerProfiles that have a (there can be zero, one or more) related Employment instances an Employment instance that has as Company (every Employment has exactly one company), a given company.

We can query such UserWorkingProfiles with the following query:

UserWorkingProfile.objects.filter(employment__company=some_comany)

So this queryset will return all UserWorkingProfiles for which there exists an Employment instance that links to the given company some_company and that UserWorkingProfile.

In Django one uses two consecutive underscores to look "through" a relation (this can be used in bidirectionally, note that two consecitive underscores are used for other things than looking through relations).

We thus wrote a query that looks, more or less like:

SELECT `userworkingprofile`.*
FROM `userworkingprofile`
JOIN `employment` ON `employment`.`employee_id` = `userworkingprofile`.`id`
WHERE `employment`.`company_id` = 123

With 123 in reality the pk of some_company.

Note that it is possible that the same UserWorkingProfile occurs multiple times in this queryset, if the employee has worked several times for some_company.

If you want every UserWorkingProfile to occur at most once, you should add .distinct() to it:

UserWorkingProfile.objects.filter(employment__company=some_comany).distinct()

Although Django indeed defines a object manager named employment_set on a Company instance, as far as I know you can not "combine" two such managers (so some_company.employment_set.employee will not work).

Then I described it falsely. I want them to have at least one related Employement instance :) — J. Hesters, Jun 12 '18 at 08:22
@J.Hesters: well this is indeed what the query does. But due to the fact of the `JOIN`ing, the same `UserWorkingProfile` can occur multiple times, if that person worked *several* times fro that company. — Willem Van Onsem, Jun 12 '18 at 08:34

Django 1.11 querysets: Get all objects (model instances) of a relationship set

1 Answers1