How to add AND to a SLICK join

Question

I have a problem writing a query in SLICK Here is my query for the 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? This is truly a lack of link information in documents.

UPDATE 1 I have tried all combinations, even ones 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, the compiler gives me the error wrong number of parameters; expected = 2 wrong number of parameters; expected = 2

So, I replaced sv.parameter_id with _.parameter_id, and now it claims to Join. He said that:

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

I am using the latest version of SLICK and it is deprecated. I manually imported

import scala.slick.lifted.Join , and now it looks like a regular innerJoin, because it uses WHERE instead of put and after ON.

SQL purchase request:

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 nice with the extra condition for 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 it never happens.

 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 of WHERE I need AND . What should I use to mention this condition, or there is no functionality for this?

Answer 1

EDIT
The OP is looking for a chain on conditions in the union of two 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) }