Anorm and Duplicate Column Names

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 = s.product_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]] = {
  val productStockItemParser: RowParser[(Product, StockItem)] = {
    productParser ~ stockItemParser map (flatten)

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

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: “” instead of “id”. Over to you, Peg:

P.S commit link on github

Invitation to Scala

Here is the material for the talk I gave at the Harvard IQSS’s “Tech talk” lecture series. This is a gentle introduction to the Scala language and to functional programming. As this lecture had quite a few demonstrations, you need to follow the lecture notes and try the commands shown there on a scala REPL in order to get the most of it.

Very big “Thank You” for all the IQSS staff that attended – it was great fun*!


SlideShare featured award
SlideShare’s featured award

This presentation just got featured by SlideShare’s editorial team. Thanks, guys!



* well, for me, at least.