Getting only the models related to a Django queryset

Question

I don't have much experience with Django (I'm using 1.3) so I have the feeling on the back of my head that this is a dumb question... But anyway:

I have models like this:

class User(models.Model):
    name = models.CharField()

class Product(models.Model):
    name = models.CharField()
    public = models.BooleanField()

class Order(models.Model):
    user = models.ForeignKey(User)
    product = models.ManyToManyField(Product, through='OrderProduct')

class OrderProduct(models.Model):
    product = models.ForeignKey(Product)
    order = models.ForeignKey(Order)
    expiration = models.DateField()

And let's say I do some query like this

Product.objects.filter(order__status='completed', order__user____id=2)

So I'd get all the products that User2 bought (let's say it's just Product1). Cool. But now I want the expiration for that product, but if I call Product1.orderproduct_set.all() I'm gonna get every entry of OrderProduct with Product1, but I just want the one returned from my queryset. I know I can just run a different query on OrderProducts, but that would be another hit on the database just to bring back data the query I ran before can already get. .query on it gives me:

SELECT "shop_product"."id", "shop_product"."name"
FROM "shop_product"
INNER JOIN "shop_orderproducts" ON ("shop_product"."id" = "shop_orderproducts"."product_id")
INNER JOIN "shop_order" ON ("shop_orderproducts"."order_id" = "shop_order"."id")
WHERE ("shop_order"."user_id" = 2  AND "shop_order"."status" = completed )
ORDER BY "shop_product"."ordering" ASC

If I could SELECT * instead of specific fields I'd have all the data that I need in one query. Is there anyway to build that query and get only the data related to it?

EDIT I feel I need to clarify some points, I'm sorry I haven't been clearer:

I'm not querying against OrderProduct because some products are public and don't have to be bought but I still have to list them, and they'd not be returned by a query against OrderProduct
The result I'm expecting is a list of products, along with their Order data (in case they have it). In JSON, it'd look somewhat like this

[{id: 1, order: 1, expiration: 2013-03-03, public: false}, {id: 1, order: , expiration: , public: true

Thanks

Instead of Product1.orderproduct_set.all(), does it work if you do Product1.orderproduct_set.filter(order__user__id = 2) ? — Tony Lâmpada
– Tony Lâmpada, Commented Mar 1, 2013 at 20:17

Dan Hoerst · Accepted Answer · 2013-03-01 19:02:01Z

1

I'm gonna get every entry of OrderProduct with Product1, but I just want the one returned from my queryset.

You just want which "one"? Your query is filtering on the Product model, so all Users, Orders, and OrderProducts associated with each of the Products in the returned queryset will be accessible.

If you want one specific OrderProduct, then you should be filtering as op = OrderProduct.objects.filter(xxxxx) and then accessing the models up the chain like so:

op.product, op.order, etc.

answered Mar 1, 2013 at 19:02

Dan Hoerst

6,3283 gold badges41 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gabe Over a year ago

Thanks for the reply, but it may not be the right way for me. See the edit on the post.

Matt · Accepted Answer · 2013-03-01 19:47:59Z

0

I would have suggested the method prefetch_related, but this isn't available in Django 1.3.

Dan Hoerst is right about selecting from OrderProduct, but that still hits the database more than necessary. We can stop that by using the select_related method.

>>> from django.db import connection
>>> len(connection.queries)
0
>>> first_result = OrderProduct.objects.select_related("order__user", "product")
...               .filter( order__status="completed",
...                        order__user__pk=2 )[0]
>>> len(connection.queries)
1
>>> name = first_result.order.user.name
>>> len(connection.queries)
1
>>> product_name = first_result.product.name
>>> len(connection.queries)
1

edited Mar 1, 2013 at 19:47

answered Mar 1, 2013 at 19:16

Matt

10.7k5 gold badges39 silver badges68 bronze badges

5 Comments

Dan Hoerst Over a year ago

select_related does not work against Many to Many relationships. So the benefits in this case may not be that great, and first_result.product.name would result in an extra query.

Matt Over a year ago

@DanHoerst Yes, but since we are selecting on the ‘linking’ model and not the models being joined by a M2M relationship… this will work. The number of queries will drop to one.

Matt Over a year ago

I've added some code, to show the number of queries being run.

Gabe Over a year ago

Yeah, prefetch_related would probably be perfect... but I'm not sure I can start the query from OrderProduct, sice it wouldn¶t get me some products I actully need. See the EDIT up there

Matt Over a year ago

In that case, there is no way to achieve this in less than two queries.

Collectives™ on Stack Overflow

Getting only the models related to a Django queryset

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related