How to use prefetch_related and select_related at the same time in Django?

Question

This is a part of models.py in my django app.

class User(models.Model):
    user_id = models.AutoField(primary_key=True)
    uuid = models.CharField(max_length=32)
    name = models.CharField(max_length=10)
    phone = models.CharField(max_length=20)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

        
class UserForm(models.Model):
    user = models.OneToOneField(User, on_delete=models.CASCADE, primary_key=True)
    access_info = models.CharField(max_length=250)
    etc_comment = models.CharField(max_length=250)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)


class UserAddress(models.Model):
    user_address_id = models.AutoField(primary_key=True)
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    address_name = models.CharField(max_length=100)
    address_name_detail = models.CharField(max_length=100)
    address_type = models.CharField(max_length=11)
    address_coord = models.PointField()
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

I am using MySQL database and linked it to django database.

My question is how to get all three data together just in a single query.

As I know, I have to use django's prefetch_related or select_related method to get them together like this

objs = User.objects.select_related('userform').get(user_id=1)

But, what about getting them from three model classes?

Please, let me know if you have any idea.

I think you're supposed to use select_related for FKs. But your query is starting from User, so you'd need to use prefetch_related. You can have multiple string values (for both btw) so like `prefetch_related("userform", "useraddress")` But don't take my word for it; I'm just guessing since I don't use it that often. I can only deduce for select_related since that and `.values()` act the same way. — acw, May 18 '21 at 02:20
Thanks for replying. I tried it like ```User.objects.prefetch_related("userform", "useraddress")```. But the result throws ```Cannot find 'useraddress' on User object, 'useraddress' is an invalid parameter to prefetch_related()```. — SeongEon Jo, May 18 '21 at 02:30
It seems not to throw an error when I use ```User.objects.using('product').prefetch_related('userform', 'useraddress_set').get(user_id=1)```. But it makes 3 query statements. ```SELECT ... FROM 'user'```, ```SELECT ... FROM 'userform'```, ```SELECT ... FROM 'user_address'``` — SeongEon Jo, May 18 '21 at 02:43
Well try and do userform_set, but I guess that still wouldn't work? In that case, select_related all accepts multiple values so you can try that as well. — acw, May 18 '21 at 02:46
When I tried userform_set, the result says it cannot find userform_set in User. Is there any way to use ```prefetch_related``` and ```select_related``` at the same time in one code? — SeongEon Jo, May 18 '21 at 02:56
It is possible. Using select_related organizes everything into one query, but we use select_related mostly for getting a single object (hence the FK) and prefetch_related for multiple objects like a M2M field. Specifically, prefetch_related is meant to be a reverse FK relationship and that's why I first pointed out to use prefetch_related. Take a look at this answer. There are a couple of blog posts that explain when to use either: https://stackoverflow.com/questions/31237042/whats-the-difference-between-select-related-and-prefetch-related-in-django-orm — acw, May 18 '21 at 03:05

How to use prefetch_related and select_related at the same time in Django?

0 Answers0