Django foreign model by unique field

Using Django Using the ORM
1

Considering the following sample models

from django.db import models

class Product(models.Model):
    upc = models.CharField(max_length=12, unique=True)
    ...

class Sale(models.Model):
    product = models.ForeignKey(Product, on_delete=models.CASCADE, related_name='sales')
    quantity = models.IntegerField()
    price = models.DecimalField(max_digits=5, decimal_places=2)
    ...

I know I can save a Sale while setting its Product by passing a Product instance:

from .models import Product, Sale

product = Product.objects.create(upc='210123456789')
sale = Sale.objects.create(product=product, quantity=1, price=10.0)

or if I have the Product instance id handy I can simplify and save it in one step (without requiring to query the database for the desired Product).

from .models import Sale

sale = Sale.objects.create(product=1, quantity=1, price=10.0)

Now considering I have a unique constraint on one of my Product fields upc, is there a way to use that field when setting the product on the Sale?

Is there a way to eliminate the extra queries to product? (example use case is some orders being sourced from an API which identifies the product by upc, requiring me to keep a cache of Product or at least upc to ids)

from .models import Product, Sale

sale = {
    'upc': '210123456789',
    'quantity': 2,
    'price': 12.49,
}

# is there a way to eliminate this query?
product = Product.objects.get(upc=sale['upc'])

Sale.objects.create(
    product=product,
    quantity=sale['quantity'],
    price=sale['price'],
)
2

I think your root question revolves around this - and the answer is no.

Think about what’s really happening here. Your Sale model has a field named product_id, which is the primary key of the Product model. Somehow, that id needs to be obtained to assign it to the Sale model.

Now, in common practice, this isn’t a problem, or an issue to concern yourself with. My normal advice for something like this is to not worry about it.

Keep in mind that PostgreSQL aggressively caches table data anyway. With any reasonable amount of memory combined with a reasonable amount of data, most of those queries are not going to perform I/O. The amount of time you would save by implementing your own cache on top of PostgreSQL is going to be greatly exceeded by the amount of time you spend implementing and managing such a cache - and that’s over the projected life-span of the project.

1 Like
3

Thanks Ken for your answer (I know I am very belated in my response, apologies).

I understand that internally a product_id is stored on the Sale object, my question perhaps was more how to offload the query fetching the product and getting it’s ID and sending it back with the Sale INSERT query, is there a way to offload the query to happen on the server, i.e. generate the following SQL to be executed on the server

create table core_product
(
    id  serial primary key,
    upc text unique
);

create table core_sale
(
    id         serial,
    product_id int references core_product (id),
    quantity   int
);

insert into core_product (upc)
values ('abc'),
       ('def');

insert into core_sale (product_id, quantity)
select core_product.id, data.quantity
from (values ('abc', 12)) data(upc, quantity)
         inner join core_product on data.upc = core_product.upc;