first cut of a view with raw sql [views_dot_py]

hjwp · hjwp · commit f63f7a5cc164 · 2021-02-24T20:08:31.000Z
diff --git a/src/allocation/views.py b/src/allocation/views.py
@@ -0,0 +1,16 @@
+from allocation.service_layer import unit_of_work
+
+
+def allocations(orderid: str, uow: unit_of_work.SqlAlchemyUnitOfWork):
+    with uow:
+        results = uow.session.execute(
+            """
+            SELECT ol.sku, b.reference
+            FROM allocations AS a
+            JOIN batches AS b ON a.batch_id = b.id
+            JOIN order_lines AS ol ON a.orderline_id = ol.id
+            WHERE ol.orderid = :orderid
+            """,
+            dict(orderid=orderid),
+        )
+    return [{"sku": sku, "batchref": batchref} for sku, batchref in results]
