Chris Stewart Chris Stewart - 2 months ago 7
Scala Question

Derive column value from another row's column value Slick

I'm trying to derive a newly inserted column's value from a previously inserted column's value. For instance, if I had this database, where every row (except the first row) must reference the

ID
of another row. I would like to increment the
Height
column by one from the value of
Height
in the row that
Previous ID
references

----------
ID | Previous ID | Height

0 | null | 123

1 | 0 | 124

2 | 1 | 125

3 | 1 | 125





Note how
height
is incremented by one from the previous value of
height
with the primary key
ID
. Is there any easy way to do this with Slick? A table could look something like this

case class ExampleCaseClass(id: Option[Long], previousId: Long)

class ExampleTable(tag: Tag) extends Table[ExampleCaseClass](tag,"example") {

def id = column[Long]("id",O.PrimaryKey, O.AutoInc)

def previousId = column[Long]("previous_id")

//this is the column that needs to be derived based on the height of 'previousId'
def height = column[Long]("height")

}


Can this be done simply?

Answer

You can use a plain sql query to do this, if you don't want to expose the height field:

def insertExample(previousId: Int): DBIO[Int] = {
  sqlu"insert into example (previous_id, height) select $previousId, height + 1 from example where id = $previousId"
}

Another way of implement this is adding a database trigger. By this way, you can use a plain insert, and the database will do the auto increment:

CREATE TRIGGER auto_height BEFORE INSERT ON example
FOR EACH ROW
SET NEW.height = 1 + (SELECT height FROM example WHERE id = NEW.previous_id);