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: which orders don't have a matching product, since Morel 0.8 hasn't got outer joins yet.

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"  }
];

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.0,name="Earl Grey",quantity=12},
   {id=2,lineTotal=840.0,name="Ethiopia",quantity=30},
   {id=4,lineTotal=1400.0,name="Ethiopia",quantity=50},
   {id=5,lineTotal=111.0,name="Earl Grey",quantity=6},
   {id=8,lineTotal=600.0,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 reads more like modern SQL and keeps the condition close to the scan it belongs with. Either form compiles to the same thing; pick the one that reads better for the query.

On missing pieces: Morel 0.8 does not yet implement outer joins (issue 75). For "all orders including unmatched ones" you'd do it in two passes and concatenate — or, as below, compute just the unmatched set with an exists subquery. The limitation is worth knowing about before you design a report around it.

Variations

Same 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 };

Orders whose product doesn't exist in the products table — Morel's answer to "left join where right is null". 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 };

See also