Anorm with multiple result sets

[Fernando Correia]

Is there an example showing how to process multiple result sets with Anorm?

Searching for it, I only found this question asked back in 2011, without answer:

https://groups.google.com/d/msg/play-framework/G8ljcM7wQyQ/v--toMcauRoJ

For instance, I'd like to be able to do this in a single query (probably through a stored procedure):

select customer, order_date from orders where order_id={id};
select item, qty, price from order_items where order_id={id};

The query would return 2 result sets, and I'd like to convert the first to an Order object and the second to a list of OrderItem objects.

[Daniel Manchester]

Hi Fernando,

It looks like you're working with hierarchical data. Here's an adaptation of an approach that's worked for me in that situation; this assumes you've already written a RowParser[Order] and RowParser[OrderItem]:

1. Compose the two RowParsers into a RowParser[(Order, OrderItem)] using the "~" syntax.
   
   
2. Combine your two SQL queries into one, joining on order_id. Place both tables' columns in the select clause. (Just using "*" is probably easiest.)
   
   
3. Execute the SQL such that you get a List[(Order, OrderItem)]:
   
     DB.withConnection { implicit connection =>
       SQL(sqlQuery).as(composedRowParser *)
     }
   
   
4. Convert the List[(Order, OrderItem)] to an (Order, List[OrderItem]):
   
    (resultsList(0)._1, resultsList.map(_._2))

Dan

[Fernando Correia]

Hi Daniel,

Thanks for your suggestion. It's very useful and I'll probably use it when it's appropriate. It's worth noticing that there is a performance cost with the join and the redundant columns, though.

But I would still like to know how to retrieve multiple result sets in Anorm. My made-up example was about hierarchical data, but there are other situations in which it might be needed, especially dealing with stored procedures.

[Prashant singh]

Hi Fernando,

     Recently, I too have to deal with Multiple Resultset and our application is heavily built around Anorm. I was wondering if you have found any solution to use Anorm to parse multiple result sets ?. Thanks

[Fernando Correia]

Prashant, no I haven't. I couldn't afford to invest more time in this particular optimization, so for now I'm just issuing multiple select commands.