Django ORM 查询问题

[hananonamida]

SELECT
	* 
FROM
	"woocommerce_shoporder" 
WHERE
	shop_id = '5' 
	AND transaction_id != '' 
	AND ( order_type IS NULL OR order_type != 'presto_mart_child_order' ) 
	AND NOT fulfillment_status = 'fulfilled' 
	AND EXISTS ( SELECT * FROM "woocommerce_shoporderitem" WHERE woocommerce_shoporderitem.order_id = woocommerce_shoporder.ID AND woocommerce_shoporderitem.sku_id IS NOT NULL AND woocommerce_shoporderitem.is_retail != TRUE ) 
	AND NOT (
		(
		SELECT COUNT
			( item.* ) 
		FROM
			woocommerce_shoporderitem item
			LEFT JOIN woocommerce_externalorder ON item.ID = woocommerce_externalorder.shop_order_item_id 
		WHERE
			item.sku_id IS NOT NULL 
			AND item.is_retail != TRUE 
			AND item.order_id = woocommerce_shoporder.ID 
			AND woocommerce_externalorder.status IN ( 3, 4 ) 
		) = ( SELECT COUNT ( item2.* ) FROM woocommerce_shoporderitem item2 WHERE item2.order_id = woocommerce_shoporder.ID AND item2.sku_id IS NOT NULL ) 
	)

求助，如何把以上 SQL 语句翻译为 Django ORM 形式的查询

[Morriaty]

我 hive 都用不到你这么复杂的 sql ，你应该做的是优化表结构

[ddmasato]

不是可以使用 raw 吗

[hananonamida]

@ddmasato 因为还有别的条件，不太想用插入的形式

[weizhen199]

师傅别念了，不行写点中间表吧

[vanwtf]

试着写了下,不知道能不能给你提供思路


ws_list = WoocommerceShoporderModel.objects.filter(
shop_id = '5',
transaction_id != '',
(Q(order_type is None) | Q(transaction_id != '')),
fulfillment_status != 'fulfilled'
)

new_ws = WoocommerceShoporderModel.objects.none()

for ws in ws_list:
wi = woocommerce_shoporderitem.objects.filter(
order_id=ws.id,sku_id !=None,is_retail != True)
item2_count = Item2Model.objects.filter(order_id=ws.id,sku_id!=None).count()
item1_count = ItmeModel.objects.filter(
sku_id != None,
is_retail != True,
order_id=ws.id,
status__in=[3,4]).count()

if wi and (item1_count != item2_count):
# 这个写法不一定对,太久没用忘了怎么写了
new_ws |= [ws]

[dayeye2006199]

直接.raw 把，别勉强

[wuwukai007]

.raw 不太行 ，用 RawSQL 不是 raw ，是 RawSQL

[allisone]

与其贴 sql 不如说下业务逻辑设计到哪些模型，可能还能给你写思路。