first cut of a view with raw sql [views_dot_py]

hjwp · hjwp · commit 43c2df7105a1 · 2020-03-05T19:57:00.000Z
diff --git a/src/allocation/views.py b/src/allocation/views.py
@@ -0,0 +1,13 @@
+from allocation.service_layer import unit_of_work
+
+def allocations(orderid: str, uow: unit_of_work.SqlAlchemyUnitOfWork):
+    with uow:
+        results = list(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]
