Left join of columns with different datatypes in Slick

Question

I have an situation where one column is Bigint in one table and another one is varchar. I am using Scala and Slick for the application. The problem is I need to do a Left Join on these two columns, but getting compilation errors due to the type mismatch. I can change the DB column from BigInt to varchar, but then I will have to make changes in many places. Is there a way in which I can avoid changing the tables and the related entities ?

score 1 · Answer 1 · edited May 02 '18 at 22:29

1

You can cast the integer to string e.g.

LEFT JOIN mytable on myint::text = mytext

You can also use CAST(myint as varchar)

Have a look here.

edited May 02 '18 at 22:29

kdauria

6,300
4
34
53

answered Jan 27 '15 at 10:53

mlinth

2,968
6
30
30

Yes, this is done on the PG Query right? I need to do this in the Slick code. – Yadu Krishnan Jan 27 '15 at 11:03
What I put is indeed postgresql code. Can't help you with the Slick stuff, I'm afraid. – mlinth Jan 27 '15 at 12:11
Thats fine. Thanks. I know how to do in SQL, but need to do that in Slick :( – Yadu Krishnan Jan 27 '15 at 12:18

score 1 · Accepted Answer · answered Jan 27 '15 at 12:34

You can cast a value of type Column using its method asColumnOf with a proper type parameter. This way the generated sql will also contain the cast in the join condition.

Something like:

for {
    (a, b) <- aTable.leftJoin(bTable).on(_.someColumn === _.anotherColumn.asColumnOf[Long])
} yield (a, b)

Left join of columns with different datatypes in Slick

2 Answers2