CODEWITHSUNDEEP
pythondjangodjango-queryset
Gabe
Gabe

Reputation: 1286

Getting only the models related to a Django queryset

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:

  1. 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

  2. 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

Upvotes: 1

Views: 2512

Answers (2)

Matt
Matt

Reputation: 10312

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

Upvotes: 0

Dan Hoerst
Dan Hoerst

Reputation: 6320

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.

Upvotes: 1

Related Questions