A bit of a technical post, just in case someone else hits this problem, e.g. while working through Play for Scala book (which I heartily recommend). That’s how I stumbled on this issue.

Anorm is an interesting alternative to ORMs. This is pretty much going against the flow of “let us write your SQL for you”, so they have some explainin’ to do. Anorm sticks to native SQL, and make it easier to parse result rows into objects. One way of making it easy are SQL row parser combinators.

The idea is this: parsers take column values from an SQL result row, and return a Scala object. Combining the parsers yields tuples, that can later be parsed into Scala objects (normally, some case class).

So that’s all fine and dandy, but the code below produces unwanted results:

val prodsAndItems =  SQL("""
    SELECT p.*, s.* FROM products p INNER JOIN stock_items s 
    ON p.id = s.product_id
    ORDER BY p.id
    """)

  val productParser: RowParser[Product] = {
    long("id")~long("ean")~str("name")~str("description") map {
      case id~ean~name~description => Product(id, ean, name, description)
    }
  }
  
  val productsParser: ResultSetParser[List[Product]] = { 
    productParser *
  }

  val stockItemParser: RowParser[StockItem] = {
    long("id")~long("product_id")~long("warehouse_id")~long("quantity") map {
      case id~prodId~wareHouseId~quantity => StockItem(id,prodId,wareHouseId,quantity)
    }
  }

  val stockItemsParser: ResultSetParser[List[StockItem]] = {
    stockItemParser*
  }
  
  val productStockItemParser: RowParser[(Product, StockItem)] = {
    productParser ~ stockItemParser map (flatten)
  }

  def selectAllProductsAndItems: Map[Product,Seq[StockItem]] = DB.withConnection{ implicit c =>
    val res:List[(Product, StockItem)] = prodsAndItems.as( productStockItemParser * )
    res.groupBy( _._1 ).mapValues( _.map(_._2) )
  }

This is a classic case of products, warehouses and stock items (product quantities that are stocked in warehouses). You would expect the call to selectAllProductsAndItems to return a map object mapping products to their stock items. Instead, it returns a set of new products that don’t exist in the database, and maps a single stock item to each one.

Now, I love creating new products. I just don’t want my data access layer to do it for me. Enough of this “machines replacing people” zeitgeist.

Why was Anorm doing this? (spoiler alert from here on)
Both stock_items and products have a column called “id”. The basic parsers (defined in stockItemParser and productParser) call the columns by their names, and so get ambiguous results.

The solution turns out to be pretty simple (as always) – reference the column by its full name: “products.id” instead of “id”. Over to you, Peg:

P.S commit link on github