Using Django 1.11
I have the following models:
class Vendor(Model): class Product(Model): class Pricebook(Model): class Quote(Model): vendor = models.ForeignKey(Vendor) class SKU(Model): product = models.ForeignKey(Product) pricebook = models.ForeignKey(Pricebook) vendor = models.ForeignKey(Vendor) class SKUPrice(Model): sku = models.ForeignKey(SKU, related_name="prices") class LineItem(Model): quote = models.ForeignKey(Quote, related_name="quote_line_items") sku = models.ForeignKey(SKU)
This is the raw query that works for me.
SELECT qli.quantity, sku_source.product_id, sku_dest.id as sku_dest_id, sku_dest_price.id as sku_dest_price_id FROM lineitem qli INNER JOIN sku sku_source ON qli.sku_id = sku_source.id LEFT JOIN sku sku_dest ON sku_dest.pricebook_id = sku_source.pricebook_id AND sku_dest.product_id = sku_source.product_id LEFT JOIN skuprice sku_dest_price ON sku_dest_price.status = 'default' AND sku_dest_price.sku_id = sku_dest.id WHERE qli.quotation_id = 40 AND qli.quantity > 0 AND sku_dest.vendor_id = 38;
What I have tried is:
(the_quote_with_id_as_40 .quotation_line_items .filter(quantity__gt=0) .select_related('sku__product') .values('sku__product__id', 'quantity') )
This produces this query
SELECT "sku"."product_id", "lineitem"."quantity" FROM "lineitem" INNER JOIN "sku" ON ("lineitem"."sku_id" = "sku"."id") WHERE ("lineitem"."quotation_id" = 40 AND "lineitem"."quantity" > 0)
which is not exactly what I want.
I can of course use raw query. But I would like to know if possible to use ORM. At the very least, this is for expanding my knowledge.
Since there’s a request to clarify my models, I am writing the following.
I have a Quote object. It allows many LineItem objects. Each LineItem is one-to-many with SKU and Quote.
A SKU belongs to a Pricebook and a Product and a Vendor. These description of the relation can also be gleaned from the above code.
But for clarity, I will repeat here.
This arrangement is such that a single, same Product can be sold by different Vendors but they will appear as different SKUs.
This is by design.
Our use case is this: a user attempts to duplicate the LineItems of a particular Quote to a different Quote.
In the mind of the user, they don’t have the sophistication to appreciate the differences between a SKU, a LineItem, or a Product.
It’s all Product in their mind. They just want the same Product to appear in a different Quote bearing the same quantity.
The challenge is this. We now have two Quotes (a source Quote to copy from and a destination Quote to copy to). Both Quotes may have the same Vendor. Or they may not. The user wants my Django app to automatically cater for both situations.
So this means I need to find out the following before I duplicate.
- the quantity as stated in the LineItem
- the Product as stated in the LineItem vis-a-vis the SKU
- find the corresponding SKU sold by the Vendor for the destination Quote
- and its corresponding unit price
The raw query allows me to extract all 4 pieces of information in a single query. It’s efficient.
Hence you see that I have the alias
This is why my WHERE criteria contains 3 pieces of conditions:
- only Source LineItems with quantity > 0 will be copied
- only LineItems from a particular source Quote will be copied
- only SKUs meant for a particular Vendor (in this case the Vendor for the destination Quote) will be looked into
It’s possible that:
- multiple LineItems for the same Product (vis-a-vis SKU) will appear in the same source Quote.
- the destination Vendor (meaning the Vendor of the destination Quote) does not sell a particular Product that the source Vendor does sell. Hence I use LEFT JOIN. This means this particular Product will not be duplicated.
I hope this clears things up.