12

I'm trying to run a SELECT *-style query in Slick against a view. Ideally, I'd end up with a function that you can pass a set of column names as a Seq[String] and it'd execute the query SELECT col1, col2, ... FROM view. If that's not feasible then it'd be acceptable to just return all columns (SELECT * FROM view).

I know I could do this by defining a Table class for this view as well as an accompanying case class, but some of these views have hundreds of columns (which would require nested tuples, given that there's a 22 element limit on tuples), and there are many views I'd have to do this for, so that's a lot of code to write/generate/maintain. I'd like to avoid that if at all possible.

Is this doable in Slick? Either by defining a Table class without having to define columns in static code (initializing some class with a dynamic column string list would be acceptable), or by just generating a SQL query ourselves using sql"""...""" and somehow getting .as[...] to handle returning a set of columns of arbitrary length?

I don't need to do anything special with the actual column types: on the database side we've got a mixture of types, but if they're all treated as text columns on the Slick side that'd be fine with me.

Alex
  • 439
  • 4
  • 8
  • 2
    Interesting question, just as a note, the column limit is now removed in newer versions of slick. – Ende Neu Sep 20 '14 at 06:53
  • 2
    To follow up to @EndeNeu's comment, see http://slick.typesafe.com/doc/2.1.0/userdefined.html#using-custom-record-types-in-queries. – Jacek Laskowski Sep 20 '14 at 20:50
  • @ecoe Can't help unfortunately since I no longer work with slick. Sorry. – Jacek Laskowski Apr 06 '18 at 20:56
  • In `Slick 3.2.3` you can [select an arbitrary list of columns *of the same type*](https://stackoverflow.com/a/49824052/1080804) or [specify finite columns with `Option` types](https://stackoverflow.com/a/34429921/1080804) for compile-time checking. But if you need more complex queries, this can get tricky as indicated in links above. – ecoe Mar 13 '19 at 17:43

1 Answers1

3

Slick is not a good fit for your requirements.

The moment you want to access arbitrary columns (for example by using a List[String] of column names), you're losing all the type safety benefits that Slick provides because it cannot validate the schema.

This means you'll have to leave Slick's table classes behind and go for a completely dynamic solution using generated SQL (using String queries).

While Slick does allow you to write SQL manually (take a look at the StaticQuery.query functions), other libraries are better suited for the task. I recommend taking a look at ScalikeJDBC.

Chris
  • 2,057
  • 2
  • 16
  • 20
  • 1
    That's the conclusion we arrived at, too. We ended up switching to jOOQ and have been very pleased. – Alex Nov 03 '14 at 15:13
  • How's jOOQ moving along with Scala integrations? (`Option`'s and such?) – Chris Nov 03 '14 at 18:31
  • We're still fairly new to jOOQ so I can't answer this authoritatively, but I've found that Scala integration is decent but not great. Overall jOOQ is a very impressive ORM and its (limited) Scala integration is not hindering us. – Alex Nov 03 '14 at 23:38