Django newbie here! I am coming from .NET background I am frustrated as to how to do the following simple thing:

My simplified models are as follows

class Circle(BaseClass):
    name = models.CharField("Name", max_length=2048, blank=False, null=False)
    active = models.BooleanField(default=False)
    ...

class CircleParticipant(BaseClass):
    circle = models.ForeignKey(Circle, on_delete=models.CASCADE, null=True, blank=True)
    user = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True)
    status = models.CharField("Status", max_length=256, blank=False, null=False)
    ...

class User(AbstractBaseUser, PermissionsMixin):
    email = models.EmailField(verbose_name="Email", unique=True, max_length=255, validators=[email_validator])
    first_name = models.CharField(verbose_name="First name", max_length=30, default="first")
    last_name = models.CharField(verbose_name="Last name", max_length=30, default="last")
    ...

My goal is to get a single circle with participants that include the users as well. With the extra requirement to do all that in a single DB trip.

in SQL terms I want to accomplish this:

SELECT circle.name, circle.active, circle_participant.status, user.email. user.first_name. user.last_name
FROM circle
JOIN circle_participant on circle.id = circle_participant.id
JOIN user on user.id = circle_participant.id
WHERE circle.id = 43

I've tried the following:

Circle.objects.filter(id=43) \
    .prefetch_related(Prefetch('circleparticipant_set', queryset=CircleParticipant.objects.prefetch_related('user')))

This is supposed to be working but when I check the query property on that statement it returns

SELECT "circle"."id", "circle"."created", "circle"."updated", "circle"."name", "circle"."active", FROM "circle" WHERE "circle"."id" = 43

(additional fields omitted for brevity.)

Am I missing something or is the query property incorrect?

More importantly how can I achieve fetching all that data with a single DB trip.

For reference here's how to do it in .NET Entity Framework

dbContext.Circle
  .Filter(x => x.id == 43)
  .Include(x => x.CircleParticipants) // This will exist in the entity/model
  .ThenInclude(x => x.User)

CodePudding user response：

.prefetch_related will use a second query to reduce the bandwidth, otherwise it will repeat data for the same Circle and CircleParticipants multiple times. Your CircleParticipant however acts as a junction table, so you can use:

Circle.objects.filter(id=43).prefetch_related(
    Prefetch('circleparticipant_set', queryset=CircleParticipant.objects.select_related('user')
    )
)

CodePudding user response：

Am I missing something or is the query property incorrect?

There are two ways that Django gives you to solve the SELECT N 1 problem. The first is prefetch_related(), which creates two queries, and joins the result in memory. The second is select_related(), which creates a join, but has a few more restrictions. (You also haven't set related_name on any of your foriegn keys. IIRC that is required before using select_related().)

More importantly how can I achieve fetching all that data with a single DB trip.

I would suggest that you not worry too much about doing it all in one query. One of the downsides of doing this in one query as you suggest is that lots of the data that comes back will be redundant. For example, the circle.name column will be the same for every row in the table which is returned.

You should absolutely care about how many queries you do - but only to the extent that you avoid a SELECT N 1 problem. If you're doing one query for each model class involved, that's pretty good.

If you care strongly about SQL performance, I also recommend the tool Django Debug Toolbar, which can show you the number of queries, the exact SQL, and the time taken by each.

in SQL terms I want to accomplish this:

There are a few ways you could accomplish that.

Use many-to-many

Django has a field which can be used to create a many-to-many relationship. It's called ManyToManyField. It will implicitly create a many-to-many table to represent the relationship, and some helper methods to allow you to easily query for all circles a user is in, or all users that a circle has.

You're also attaching some metadata to each user/circle relationship. That means you'll need to define an explicit table using ManyToManyField.through.

There are examples in the docs here.

Use a related model query

If I specifically wanted a join, and not a subquery, I would query the users like this:

Users.objects.filter(circleparticipant_set__circle_id=43)

Use a subquery

This also creates only one query, but it uses a subquery instead.

Users.objects.filter(circleparticipant_set=CircleParticipant.objects.filter(circle_id=43))