Morel Cookbook

Problem

I have orders and products, and I want each order enriched with the product it refers to — price, category, the lot. Inner join territory. Also: keep the orders whose product has gone missing, instead of silently dropping them.

Setup

val orders = [
  { id = 1, productId = 101, quantity = 12, region = "north" },
  { id = 2, productId = 204, quantity = 30, region = "south" },
  { id = 4, productId = 204, quantity = 50, region = "west"  },
  { id = 5, productId = 101, quantity = 6,  region = "east"  },
  { id = 8, productId = 102, quantity = 25, region = "west"  },
  { id = 9, productId = 305, quantity = 40, region = "north" }
];

val products = [
  { id = 101, name = "Earl Grey",  category = "tea",    price = 18.50 },
  { id = 102, name = "Darjeeling", category = "tea",    price = 24.00 },
  { id = 204, name = "Ethiopia",   category = "coffee", price = 28.00 }
];

Example

Join each order to its product and compute a line total:

from ord in orders, p in products
  where ord.productId = p.id
  yield { ord.id, p.name, ord.quantity, lineTotal = real ord.quantity * p.price };

val it =
  [{id=1,lineTotal=222,name="Earl Grey",quantity=12},
   {id=2,lineTotal=840,name="Ethiopia",quantity=30},
   {id=4,lineTotal=1400,name="Ethiopia",quantity=50},
   {id=5,lineTotal=111,name="Earl Grey",quantity=6},
   {id=8,lineTotal=600,name="Darjeeling",quantity=25}]
  : {id:int, lineTotal:real, name:string, quantity:int} list

What's happening

Two scans in the from list — ord in orders, p in products — form a cross product, and the where clause narrows it to the pairs where the join condition holds. That's the ANSI-SQL mental model and it works in Morel too. The yield step can reach into either side because both names are still in scope.

There's also a dedicated join step with an inline on clause, which keeps the condition next to the scan it belongs with. Either form compiles to the same thing; pick whichever reads better.

Look at what the result doesn't contain. Order 9 points at product 305, which isn't in products, so the inner join drops it — six rows in, five out, no warning. That silent narrowing is how joins lose data.

Morel 0.9 added outer joins. left join keeps every row on the left and makes the right-hand scan's fields option: SOME where a match was found, NONE where it wasn't. Because p is now an option, plain p.name no longer type-checks — reach through it with the safe navigation operator ?., which turns p?.name into a string option.

Variations

Keep every order, matched or not. Order 9 comes through with NONE:

from ord in orders
  left join p in products on ord.productId = p.id
  yield { ord.id, name = p?.name };

val it =
  [{id=1,name=SOME "Earl Grey"},{id=2,name=SOME "Ethiopia"},
   {id=4,name=SOME "Ethiopia"},{id=5,name=SOME "Earl Grey"},
   {id=8,name=SOME "Darjeeling"},{id=9,name=NONE}]
  : {id:int, name:string option} list

right join and full join work the same way, putting the option on the other side or on both.

Same inner join, written with an explicit join step:

from ord in orders
  join p in products on ord.productId = p.id
  yield { ord.id, p.category };

Just the unmatched orders. A left join followed by a test for NONE would do it, but an anti-join says it more directly. Note exists is a top-level expression, like from, and takes its own scan:

from ord in orders
  where not (exists p in products where p.id = ord.productId)
  yield { ord.id, ord.productId };

val it = [{id=9,productId=305}] : {id:int, productId:int} list

See also