We’ve two options when writing queries and I wonder which one do you prefer.
I am a SQL Developer and I know that PyPika makes my apps compatible with PostgreSQL but it’s kinda new stuff for me.
I wonder does using PyPika have any benefits other than making apps cross-database compatible?
I think QB is great!
The only thing I suffer from is the documentation, specially for the details. e.g. I was puzzled for 15 minutes on how to import concat_ws. After trying all options I found out it had to be imported as Concat_ws. I still have to figure out if an and_ and or_ conditions are present in PyPika.
The query in question is this:
SELECT
SED_START.batch_no, SED_START.item_code, SED_START.t_warehouse,
IFNULL(SUM(SED_START.qty), 0) AS start_qty, IFNULL(SUM(SED_MANUFACTURE.qty), 0) AS manufactured_qty,
IFNULL(SUM(SED_START.qty), 0) - IFNULL(SUM(SED_MANUFACTURE.qty), 0) AS rem_qty
FROM
`tabWork Order` AS WO
LEFT JOIN `tabStock Entry` AS SE_START ON SE_START.work_order = WO.name
AND SE_START.docstatus = 1
AND SE_START.purpose = 'Material Transfer for Manufacture'
LEFT JOIN `tabStock Entry Detail` AS SED_START ON SED_START.parent = SE_START.name
AND SED_START.parentfield = "items" AND SED_START.parenttype = "Stock Entry"
LEFT JOIN `tabStock Entry` AS SE_MANUFACTURE ON SE_MANUFACTURE.work_order = WO.name
AND SE_MANUFACTURE.docstatus = 1
AND SE_MANUFACTURE.purpose = 'Manufacture'
LEFT JOIN `tabStock Entry Detail` AS SED_MANUFACTURE ON SED_MANUFACTURE.parent = SE_MANUFACTURE.name
AND SED_MANUFACTURE.parentfield = "items" AND SED_MANUFACTURE.parenttype = "Stock Entry"
AND SED_MANUFACTURE.item_code = SED_START.item_code
AND SED_MANUFACTURE.batch_no = SED_START.batch_no
WHERE
WO.name = 'MFG-WO-2023-00037'
#AND SED_START.item_code = SED_MANUFACTURE.item_code
GROUP BY
SED_START.batch_no, SED_START.item_code, SED_START.t_warehouse
ORDER BY
SED_START.idx ASC
Basically, it finds batches with quantities when starting work order. Then subtracts manufactured quantities from the batches.
