Best way to include a sub-select queryset in Django in just 1 hit to the database

Question

I have 2 models, Product and Productdetails with a OneToOne relationship like this:

class Product(IotaModel):

   details = models.OneToOneField(
       ProductDetails,
       null=True,
       on_delete=models.SET_NULL
   )

I want a queryset that do this:

SELECT *
FROM product_details
WHERE id = (
    SELECT details_id
    FROM products
    WHERE id=<product_id>
)

I tried this:

details_id = Product.objects.filter(pk=product_pk, active=True).only('details')[:1]
return ProductDetails.objects.filter(pk=details_id, active=True)

But it does not work becouse .only('details') gave me the fields (id, details_id) and the next filter takes id instead of details_id.

I also try to add .defer('id') but that does not work.

I know that I can get the details_id using .values_list('details_id') but I think this would involve making 2 queries to the Database.

What could be the best approach to reach the desired SQL query in just 1 hit to the DB?

Thanks in advance!

Answer 1

You should make just the query for the ProductDetails. Something like this I think.

product_details = ProductDetails.objects.get(
    active=True, product__id=product_pk, product__active=True
)

The double underscore it's to look on related objects.