4

Is there some scala relational database framework (anorm, squeryl, etc...) using postgres-like aggregators to produce lists after a group-by, or at least simulating its use?

I would expect two levels of implementation:

  • a "standard" one, where at least any SQL grouping with array_agg is translated to a List of the type which is being aggregated,

  • and a "scala ORM powered" one where some type of join is allowed so that if the aggregation is a foreign key to other table, a List of elements of the other table is produced. Of course this last thing is beyond the reach of SQL, but if I am using a more powerful language, I do not mind some steroids.

I find specially intriguing that the documentation of slick, which is based precisely in allowing scala group-by notation, seems to negate explicitly the output of lists as a result of the group-by.

EDIT: use case

You have the typical many-to-many table of, say, products and suppliers, pairs (p_id, s_id). You want to produce a list of suppliers for each product. So the postgresql query should be

SELECT p_id, array_agg(s_id) from t1 group by p_id

One could expect some idiomatic way to to this in slick, but I do not see how. Furthermore, if we go to some ORM, then we could also consider the join with the tables products and suppliers, on p_id and s_id respectively, and get as answer a zip (product, (supplier1, supplier2, supplierN)) containing the objects and not only the ids

Nerdroid
  • 13,398
  • 5
  • 58
  • 69
arivero
  • 777
  • 1
  • 9
  • 30
  • By the way. even without aggregation, the option to interpret a foreign key as a reference to other object should be a welcome plus. – arivero Jan 19 '15 at 06:32
  • 1
    Result of `array_agg` is of type `Array` in JDBC, so using Anorm you can parse such column as either `Array[T]` or `List[T]`, provided each value of the array is of a supported type that can be parsed as `T`. Slick should have something similar with its native query. – cchantep Jan 19 '15 at 09:07
  • What I was thinking is that Slick could add some syntax do to this. They keept telling that .group-by must always be terminated by a map plus and aggregator (say, avg) in order to avoid the production of Lists. But in postgresql it is natural to produce lists, so an "unaggregated group-by query" in Slick should/could compile to array_agg – arivero Jan 19 '15 at 16:06
  • 1
    It's a kind of case where I prefer to handle DB specificity with a custom result parser (Anorm). – cchantep Jan 19 '15 at 23:21

3 Answers3

2

I am also not sure if I understand you question correct, could you elaborate?

In slick you currently can not use postgres "array_agg" or "string_agg" as a method on type Query. If you want to use this specific function then you need to use custom sql. But: I added an issue some time ago (https://github.com/slick/slick/issues/923, you should follow this discussion) and we have a prototype from cvogt ready for this.

I needed to use "string_agg" in the past and added a patch for it (see https://github.com/mobiworx/slick/commit/486c39a7ed90c9ccac356dfdb0e5dc5c24e32d63), so maybe this is helpful to you. Look at "AggregateTest" to learn more about it.

Another possibility is to encapsulate the usage of "array_agg" in a database view and just use this view with slick. This way you do not need "array_agg" directly in slick.

tfh
  • 620
  • 1
  • 4
  • 14
2

You can use slick-pg.

It supports array_agg and other aggregate functions.

Daniel Shin
  • 5,086
  • 2
  • 30
  • 53
0

Your question is intriguing, care to elaborate a little on how it might ideally look? When you group by you often have an additional column, such as count(*) over and above the standard columns from your case class, so what would the type of your List be?

Most of my (anorm) methods either return a singleton (perhaps Option) or a List of that class's type. For each case class, I have an sqlFields variable (e.g. m.id, m.name, m.forManufacturer) and a single parser variable that I reference as either .as(modelParser.singleOpt) or .as(modelParser *). For foreign keys, a lazy val at the case class level (or def if it needs to be) is pretty useful. E.g. if I had Model and Manufacturer entities, with a foreign key forManufacturer on Model, then I might define a lazy val manufacturer : Manufacturer = ... in the case class of the model, so that at any time I can refer to model.manufacturer. I can define joins as their own methods, either in this way, or as methods in the companion object.

Not 100% sure I am answering your question, but thought this was a bit long for a comment.

Edit: If your driver supported parsing of postgresql arrays, you could map them directly to a class like ProductSuppliers(id:Int, suppliers:List[Int]) (or even List[Supplier]?) In anorm that's about as idiomatic as one could get, I think? For databases that don't support it, it seems to me similar to an order by version, i.e. select p1, s1 from t1 order by p1, which you could groupBy p1 and similarly map to ProductSuppliers.

wwkudu
  • 2,778
  • 3
  • 28
  • 41
  • Really I do not know how sql-standard is the array_agg function, are you familiar with it? The additional column is converted to a list. Now, in slick and probably in anorm, I'd just expect a way to produce such lists and no more. In a ORM framework, if the type of the list was a foreign key to other table, I would expect the list to be converted in a list of objects of the other table. – arivero Jan 19 '15 at 16:02