Multiple left join and left join against the same model raw query convert to Django ORM

Question

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.

Thank you.

UPDATE

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 sku_source and sku_dest

This is why my WHERE criteria contains 3 pieces of conditions:

1. only Source LineItems with quantity > 0 will be copied
2. only LineItems from a particular source Quote will be copied
3. only SKUs meant for a particular Vendor (in this case the Vendor for the destination Quote) will be looked into

It's possible that:

1. multiple LineItems for the same Product (vis-a-vis SKU) will appear in the same source Quote.

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

Answer 1

To me, the way you approach the problem is incorrect.

You are still trying to explain the query, not the objects that you need. When working with an ORM, you need to stop thinking about query, and start thinking about objects, and how they can play/interact with each other. By doing so, you will put your business logic inside you code (models, managers, etc) and not inside you queries. You don't want to have business logic into the query and then start thinking, OK how do I create this query now? Smart objects with dumb queries are easier to be optimized and understand by the others, rather than smart queries with dumb code (objects).

Start with objects, write your code, then optimize it.

If you show me a not-optimized object oriented version of how/what you need in order to make these duplicates, I will probably be able to show you how you can optimize it.

Until then, and not to be completely off-topic, give a try to this query, but I cannot give any guarantees that its what you are looking for:

quote.quotation_line_items.filter(
    quantity__gt=0,
    sku__vendor__sku__vendor=38,
    sku__vendor__sku__product=models.F('sku__product'),
    sku__vendor__sku__pricebook=models.F('sku__pricebook'),
).values(
    'quantity',
    'sku__product_id', # sku source product_id
    'sku__vendor__sku__id', # sku dest id
    'sku__vendor__sku__skuprice__id' # sku dest skuprice id
)