How to add AND to the join SLICK

Question

I have the problem writing query in SLICK Here is my request to MySql database:

SELECT * FROM readings AS r
JOIN parameters AS p
LEFT JOIN sensorvalues AS sv ON sv.parameter_id=p.id AND sv.reading_id=r.id

How can I write it using SLICK ? It is really lack of info on joins in docs.

UPDATE 1 I tried all the combinations even one like this

val q = for{
  Join(p,sv) <- Parameters leftJoin SensorValues on (_.id is sv.parameter_id)
  r <- Readings if sv.reading_id is r.id
} yield(r,p,sv)

In this case compiler gives me an error wrong number of parameters; expected = 2

So I replaced sv.parameter_id to _.parameter_id and now it argues about Join It said that:

constructor cannot be instantiated to expected type; found : models.Join required: (models.Parameters.type, models.SensorValues.type)

I'm using last version of SLICK and it's deprecated. I manually imported

import scala.slick.lifted.Join and now it looks like it's regular innerJoin because it uses WHERE instead of putting and after ON.

SQL generated buy query:

select x2.id, x2.platform_id, x2.date, x3.x4, x3.x5, x3.x6, x7.x8, x7.x9, x7.x10, x7.x11 from (select x12.id as x4, x12.name as x5, x12.units as x6 from parameters x12) x3 left outer join (select x13.id as x8, x13.reading_id as x9, x13.parameter_id as x10, x13.value as x11 from sensorValues x13) x7 on x3.x4 = x7.x10, readings x2 where true and (x7.x9 = x2.id)

Even this one would be good with added condition to the ON section

 val readings = for {
        all <-Readings join Parameters leftJoin SensorValues on (_._2.id is _.parameter_id) if(all._1._1.id === all._2.reading_id)
      } yield (all._1._1,all._1._2,all._2)

But this never hapens.

SELECT
    x2.x3,
    x2.x4,
    x2.x5,
    x2.x6,
    x2.x7,
    x2.x8,
    x9.x10,
    x9.x11,
    x9.x12,
    x9.x13
FROM
    (
        SELECT
            x14.x15 AS x3,
            x14.x16 AS x4,
            x14.x17 AS x5,
            x18.x19 AS x6,
            x18.x20 AS x7,
            x18.x21 AS x8
        FROM
            (
                SELECT
                    x22.`id` AS x15,
                    x22.`platform_id` AS x16,
                    x22.`date` AS x17
                FROM
                    `readings` x22
            )x14
        INNER JOIN(
            SELECT
                x23.`id` AS x19,
                x23.`name` AS x20,
                x23.`units` AS x21
            FROM
                `parameters` x23
        )x18
    )x2
LEFT OUTER JOIN(
    SELECT
        x24.`id` AS x10,
        x24.`reading_id` AS x11,
        x24.`parameter_id` AS x12,
        x24.`value` AS x13
    FROM
        `sensorValues` x24
)x9 ON x2.x6 = x9.x12
WHERE
    x2.x3 = x9.x11

instead WHERE I need AND. What Should I use to mention this condition or there is no functionality in slick for that?

virtualeyes · Answer 1 · 2012-11-17T08:57:02.030

5

EDIT
The OP is looking for chained on conditions in a join of 2 table objects.

This should compile:

val q = for{
  r <- Readings
  Join(p,s) <- Params leftJoin Sensors on (
    (a,b)=> (a.id is b.parameter_id) && (b.reading_id is r.id)
  )
} yield(r,p,s)

ORIGINAL
Try something like this:

val q = for{
  Join(p,s) <- Params leftJoin Sensors on (_.id is s.parameter_id)
  r <- Readings if s.reading_id is r.id
} yield(r,p,s)

val result = 
  q.list.map{ case(r,p,s)=>
    SomeCaseClass(r,p,s)
  }

edited Nov 17 '12 at 08:57

answered Nov 15 '12 at 23:31

virtualeyes

11,147
6
56
91

Did not work... If I put s.parameter_id in the on it sais `wrong number of parameters; expected = 2`. See update of the question – Yaroslav Nov 16 '12 at 10:42
Parameters is a reserved word in ScalaQuery, assume same for Slick, thus "Params" – virtualeyes Nov 16 '12 at 14:48
you can chain and conditions with "if(tbl.a is someVal) && (tbl.b is otherVal)" – virtualeyes Nov 16 '12 at 14:51
and you can pass in values to bind to columns with "for { a ~ b ~ c <- Parameters[Int,String,String] .... if(tbl.a is a) && ... }" – virtualeyes Nov 16 '12 at 14:54
`Parameters` is fine too I could write `models.Parameters`. Anyway, in my case it is the one from the models. I know that I can chain the conditions, but that means that it will create WHERE section != leftJoin it just becomes inner join. But I need to have `x9 ON x2.x6 = x9.x12 AND x2.x3 = x9.x11` instead of `x9 ON x2.x6 = x9.x12 WHERE x2.x3 = x9.x11` – Yaroslav Nov 17 '12 at 02:04
ah ok, I see, you're looking for chained "on" conditions -- updated my answer – virtualeyes Nov 17 '12 at 08:46
We almost there, I got this `on (x3.x4 = x7.x10) and (x2.id = x7.x9)` but it does not know about r.id column. That is because it does not generate inner Join for Readings and Params. It does this:`SELECT x2.id, x2.platform_id, x2.date, FROM readings x2, (SELECT x12.id AS x4, x12.name AS x5, x12.units AS x6 FROM parameters x12)x3` What should I change in scala code to get `FROM readings x2 inner join` instead `FROM readings x2,` ? Thanks for your help. – Yaroslav Nov 17 '12 at 16:38
not sure if this has changed in Slick, but in ScalaQuery the Join of more than 2 tables is not supported. – virtualeyes Nov 17 '12 at 16:44
hmm, in SLICK it's possible to do multiple joins. Like I wrote in update part of my post with all <- you see it generates inner join and left join. But I have no idea how to add another condition into ON section. – Yaroslav Nov 17 '12 at 16:55
ah, cool, did not know that, well, in that case, have you tried "(a,b,c)=> (a.id is b.parameter_id) && ..." – virtualeyes Nov 17 '12 at 17:04
in case of `all <- Readings join Parameters leftJoin SensorValues` `ON` only takes one condition =( – Yaroslav Nov 17 '12 at 18:03

How to add AND to the join SLICK

1 Answers1

Linked