0

Let's say I have the following tables in my database:

CREATE TABLE dealers(
 id INT PRIMARY KEY, 
 name VARCHAR(255)
);    

CREATE TABLE makers(
 id INT PRIMARY KEY, 
 name VARCHAR(255)
);

CREATE TABLE cars(
 id INT PRIMARY KEY, 
 make INT FOREIGN KEY makers(id), 
 model VARCHAR(255), 
 year INT
);

CREATE TABLE cars_in_dealers(
 car_id INT FOREIGN KEY cars(id), 
 dealer_id INT FOREIGN KEY dealers(id), 
 UNIQUE KEY (car_id, dealer_id)
);

Given such a schema, I want to use Slick to load dealers in Scala:

case class Dealer(id: Int, name: String, models: Set[Car])
case class Car(id: Int, make: Maker, model: String, year: Int)
case class Maker(id: Int, name: String)

How about something a bit more complicated:

What if I wanted to keep track of count of models in each dealership:

case class Dealer(id: Int, name: String, models: Map[Car, Int])

and this was my mapping table instead:

CREATE TABLE cars_in_dealers(
 car_id INT FOREIGN KEY cars(id), 
 dealer_id INT FOREIGN KEY dealers(id), 
 count INT,
 UNIQUE KEY (car_id, dealer_id)
);

I am familiar with Ruby's ActiveRecord and Java's Hibernate framework where these things are easy to do but I am having a hard time doing it in Slick since Slick does not map nested models into foreign keyed tables. I am using Slick's codegen which only generates the following classes:

case class DealersRow(id: Int, name: String)
case class MakersRow(id: Int, name: String
case class CarsRow(id: Int, make: Int, model: String, year: Int)
case class CarsInDealersRow(carId: Int, dealerId: Int)
pathikrit
  • 32,469
  • 37
  • 142
  • 221
  • 2
    You shouldn't (and probably can't), slick makes a model of your schema and your schema has foreign keys, you can map that into what you want after the query execution, this has been asked a lot around, [here](http://stackoverflow.com/questions/25957634/slick-2-mapping-many-to-one-relationship-with-mappedcolumn/25958586#25958586), [here](http://stackoverflow.com/questions/19427200/slick-use-foreignkey-constraint-and-access-referenced-object-directly-as-column) and [here](http://stackoverflow.com/questions/20503847/type-projection-of-foreign-keys-in-scala-slick) just to name some. – Ende Neu Oct 20 '14 at 12:31
  • Slick is not and should not be used as an ORM. Don't try to map foreign keys to sequences except in aggregate roots (and in that case, don't try to load them lazily). It just doesn't work. It's too complicated. –  Oct 20 '14 at 13:04
  • What do you recommend in Scala is a good library for ORMs? About 50% of my code in Slick is writing all these joins and tupled conversions to nested models. I want something out of the box where my Object oriented models map neatly to a relational database out of the box.. – pathikrit Oct 29 '14 at 00:22

1 Answers1

3

Do it using queries! From the documentation:

Slick new comers often ask how they can do something like this in Slick:

case class Address( … )
case class Person( …, address: Address )

The problem is that this hard-codes that to exist a Person requires an Address. It can not be loaded without it. This does’t fit to Slick’s philosophy of giving you fine-grained control over what you load exactly. With Slick it is advised to map one table to a tuple or case class without them having object references to related objects. Instead you can write a function that joins two tables and returns them as a tuple or association case class instance, providing an association externally, not strongly tied one of the classes.

val tupledJoin: Query[(People,Addresses),(Person,Address), Seq]
      = people join addresses on (_.addressId === _.id)

case class PersonWithAddress(person: Person, address: Address)
val caseClassJoinResults = tupledJoin.run map PersonWithAddress.tupled
cvogt
  • 11,260
  • 30
  • 46