Derive column value from another row's column value Slick

Question

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 1

There are two parts to this:

1. How do you insert a row using values that depend on some other row?
2. How do you package that up in such a way that the logic is "transparent" to users of your table?

There are two approaches for (1):

• (1a) Do the query and the insert in separate SQL statements, with two round-trips to the database. (Fine performance-wise in almost all cases.)

• (1b) Do the query and insert in one SQL statement using a INSERT INTO ... SELECT statement. (Marginally faster in certain uncommon situations).

Aside from performance, the two require different syntactic approaches. Both approaches in Slick are discussed in the two answers for:

Scala+Slick 3: Inserting the result of one query into another table

I actually think (1b) is more concise anyway. I mention (1a) to give you another option to explore. (1b) is accomplished using forceInsertQuery as described in:

http://slick.lightbend.com/doc/3.1.1/queries.html#inserting

val example = TableQuery[ExampleTable]
def insertAuto(previousId: Int) = {
  val query = example.filter(_.id == previousId).map(r => (previousId, r.height))
  DBIO.seq(example.forceInsertQuery(query))
}

(Note that when using INSERT INTO .. SELECT you can mix user-supplied literal values with queried values simply by embedding the literals in the SELECT clause.)

There might be an issue where we need to supply the id even though it's auto-incremented because the docs seem to suggest forceInsertQuery requires explicit values even for autoinc columns.

Now, how you want to package up that def to make it convenient for your users to invoke, I'm not actually sure. That should be a simpler thing to accomplish. You could consider making this an extension method on TableQuery[ExampleTable]:

implicit class RichExampleTable(example: TableQuery[ExampleTable]) {
  // def extension methods here
}

But this does require your users to invoke the insertAuto method rather than use "direct" Slick statements. I don't think there's any way around that.

Answer 2

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);