CODEWITHSUNDEEP
mysqlscalaslick
Joshua Conner
Joshua Conner

Reputation: 912

Slick Plain SQL implicit GetResult for table with has-many JOIN

I'm using Slick Plain SQL and I've got a table schema something like this:

CREATE TABLE users (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(191) NOT NULL
);

CREATE TABLE images (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  url VARCHAR(191) NOT NULL,
  user_id INT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id)
  ON DELETE CASCADE ON UPDATE CASCADE
);

and a case class that looks like this:

case class User(name: String, imageUrls: Array[String])

Is it possible to write my query and implicit GetResult so that I can do something like this:

val u = Q.query[String, User]("""
  SELECT
    users.name, images.url
  FROM
    users
    INNER JOIN images ON images.user_id = users.id
  WHERE
    users.name = ?
""").first(name)

Upvotes: 1

Views: 986

Answers (1)

cmbaxter
cmbaxter

Reputation: 35453

The one-to-many relationship is the tricky thing here. Not sure there is anything out of the box to allow a GetResult to be able to collapse the records down so that the image urls get aggregated on a single User record by user id. But you could just do this processing yourself with a couple of tweaks to your example:

case class User(name: String, imageUrls: List[String])

val records = Q.query[String, (String,String)]("""
  SELECT
    users.name, images.url
  FROM
    users
    INNER JOIN images ON images.user_id = users.id
  WHERE
    users.name = ?
""").list(name)  

val users = records.groupBy(_._1).map(tup => User(tup._1, tup._2.map(_._2)))

When done, you will have an Iterable[User] as the users val.

Upvotes: 5

Related Questions